Question Excel VBA macro to automate Avaya CMS Supervisor reports

When I first started my current role in a call centre I found myself exporting and pasting numerous reports from Avaya CMS into Excel in order to compile all sort of business reports for the call centre managers. I thought this was tedious and my hand was sore from doing repetitive things. So after some research I found out that we can use the Avaya objects from Excel to automate these reports.

I was able to automate most of my daily, weekly and monthly reports and what used to take me hours to complete I can now finish in seconds with one click. Over the last couple of years I was able to create all sort of automated dashboards to report on call statistics, service levels and staff KPIs. Of course Avaya has a custom reports capability but I was told I can't have access to this function as our company had an agreement with Avaya that they are the only people allowed to write custom reports otherwise the warranty on the software will be void.

In this code sample I will demonstrate how to automate the Avaya Call Management System software from Excel. I will give an example on how to obtain an Agent Trace report and paste it on the first sheet in an Excel application. This is a basic example but the logic can be followed for all other reports. It is also worth while looking inside the script that Avaya generates, open it with Notepad and have a look at the code it contains, it is written in Visual Basic. This will give you an idea on how to do reports other than the Agent Trace in my example below.

So let's get started, start the Visual Basic editor from Excel then copy the code below in a new module, give the user name, password, date and agent name valid values and run it. It all is correct after about 10 seconds you will see the report on sheet 1.


Public Sub CMSConn()
Dim cvsApp As Object
Dim cvsConn As Object
Dim cvsSrv As Object
Dim Rep As Object
Dim Info As Object, Log As Object, b As Object

Set cvsApp = CreateObject("ACSUP.cvsApplication")
Set cvsConn = CreateObject("ACSCN.cvsConnection")
Set cvsSrv = CreateObject("ACSUPSRV.cvsServer")
Set Rep = CreateObject("ACSREP.cvsReport")
serverAddress = "cms"
mydate = "23/04/2010"
UserName = "yourUserName"
passW = "yourPassword"
agentName = "agent Name"
If cvsApp.CreateServer(UserName, "", "", serverAddress, False, "ENU", cvsSrv, cvsConn) Then
If cvsConn.login(UserName, passW, serverAddress, "ENU") Then
On Error Resume Next
cvsSrv.Reports.ACD = 1
Set Info = cvsSrv.Reports.Reports("Historical\Agent\Trace by Location")
If Info Is Nothing Then
If cvsSrv.Interactive Then
MsgBox "The Report " & "Historical\Agent\Trace by Location" & " was not found on ACD 1", vbCritical Or vbOKOnly, "CentreVu Supervisor"
Else
Set Log = CreateObject("ACSERR.cvslog")
Log.AutoLogWrite "The Report " & "Historical\Agent\Trace by Location" & " was not found on ACD 1"
Set Log = Nothing
End If
Else
b = cvsSrv.Reports.CreateReport(Info, Rep)
If b Then
Debug.Print Rep.SetProperty("Agent", agentName)
Debug.Print Rep.SetProperty("Dates", mydate)
Debug.Print Rep.SetProperty("Times", "00:00-23:59")
b = Rep.ExportData("", 9, 0, False, True, True)
Set wk = ThisWorkbook
wk.Sheets(1).Cells.ClearContents
wk.Sheets(1).Cells(1, 1).PasteSpecial

'b = Rep.ExportData(fileP, 9, 0, False, True, True)


Rep.Quit
If Not cvsSrv.Interactive Then cvsSrv.ActiveTasks.Remove Rep.TaskID
Set Rep = Nothing
End If
End If

Set Info = Nothing
End If

End If

cvsConn.logout
cvsConn.Disconnect
cvsSrv.Connected = False
Set Log = Nothing
Set Rep = Nothing
Set cvsSrv = Nothing
Set cvsConn = Nothing
Set cvsApp = Nothing


End Sub

Purchase a completed Avaya CMS Excel macro that records agents trace reports for just $2 donation from the link below. This has the working code and is great for beginners or if you want to save time. It will create a new folder for each day and saves each agent trace in a new a Excel file. Pay securely with your PayPal account.

Avaya CMS automation Excel VBA macro - $3.50 : Zen Cart!, The Art of E-commerce (fracta.net)

Another method to do the same as above is to set references to the AVAYA CMS objects from VBA in teh preferences menu.

Start the Visual Basic editor from Excel and add the Avaya objects references. You will need the Application Component, Connection Component, Report Component and Server Component. See the screen shot below, click to view larger image.



If you add the references to the VBA editor you should be able to use the below instead to create the objects, so when you type ACSUP., ACSCN., etc... You should see the auto code completer come up with possible options.

Dim cvsApp As New ACSUP.cvsApplication
Dim cvsConn As New ACSCN.cvsConnection
Dim cvsSrv As New ACSUPSRV.cvsServer
Dim Rep As New ACSREP.cvsReport
Dim Info As Object, Log As Object, b As Object

The rest of the coding will be the same...

Another method on how to automate CMS without logging into the server by using the server instance already running if you have Avaya CMS control panel application already running on your desktop.

For this code to work remember to set all the reference I mentioned in the VBA editor tools me, read my first post above and click on the picture to see which ones to select:


Here's the entire module code below, it will run the historical split skill summary interval report for skill number 1:


Dim cvsApp As New ACSUP.cvsApplication
'Dim cvsConn As New ACSCN.cvsConnection
Dim cvsSrv As New ACSUPSRV.cvsServer
Dim Rep As New ACSREP.cvsReport
Dim Info As Object, Log As Object, b As Object
Dim logged As Boolean


Public Sub CMSConn()
On Error GoTo e:
Application.ScreenUpdating = 0
sk = "1" ''''''' change this number to a valid skill number that matches you CMS installation
ThisWorkbook.Sheets(1).Cells.ClearContents

Set cvsSrv = cvsApp.Servers(1) '(cvsApp.Servers.Count) '''''' we set a reference to the server here
Call doRep("Historical\Split/Skill\Summary Interval", sk)
ThisWorkbook.Sheets(1).Cells(1, 3).PasteSpecial
logout
Application.ScreenUpdating = 1

e:
If Err.Number <> 91 Then
MsgBox "Make sure you are logged in CMS, if already logged in close CMS and Excel program and re-login." & vbCrLf & "If still fails report to Edgar"
End If

End Sub


Sub doRep(sReportName As String, ByVal sk As Integer)

On Error Resume Next
cvsSrv.Reports.ACD = 1
Set Info = cvsSrv.Reports.Reports(sReportName)
If Info Is Nothing Then
If cvsSrv.Interactive Then
MsgBox "The Report " & sReportName & " was not found on ACD 1", vbCritical Or vbOKOnly, "CentreVu Supervisor"
Else
Set Log = CreateObject("ACSERR.cvslog")
Log.AutoLogWrite "The Report " & sReportName & " was not found on ACD 1"
Set Log = Nothing
End If
Else
b = cvsSrv.Reports.CreateReport(Info, Rep)
If b Then
Debug.Print Rep.SetProperty("Split/Skill", sk)
Debug.Print Rep.SetProperty("Date", 0)
Debug.Print Rep.SetProperty("Times", "00:00-23:30")
b = Rep.ExportData("", 9, 0, False, True, True)
Rep.Quit
If Not cvsSrv.Interactive Then cvsSrv.ActiveTasks.Remove Rep.TaskID
Set Rep = Nothing
End If
End If

Set Info = Nothing



End Sub

Sub logout()
Set Log = Nothing
Set Rep = Nothing
Set cvsSrv = Nothing
Set cvsApp = Nothing

End Sub

Now I though some of you might want to automate mutliple reports from CMS Avaya and save the output to text files without using VBA Excel but rather using the script file that Avaya allows you to save.

When you are in a report if you click on the Report button in the menu and select script and save it you can then run the report by just double clicking on the scriptName.acsauto file. This will only run the one report. But did you know you can edit that file to make it run for multiple skills and saves each in a different file?

Here the code to get the summary interval reports for 15 skills and save them in a text file in one script. You can open the .acsauto file with notepad and edit it, this script will save on the C: drive in a directory called C:\CMS Scripts, make sure you have it. Make sure you change the skill numbers to ones that match your call centre:

'LANGUAGE=ENU
'SERVERNAME=cms
Public Sub Main()

'## cvs_cmd_begin
'## ID = 2001
'## Description = "Report: Historical: Split/Skill: Summary Interval: Export Data"
'## Parameters.Add "Report: Historical: Split/Skill: Summary Interval: Export Data","_Desc"
'## Parameters.Add "Reports","_Catalog"
'## Parameters.Add "2","_Action"
'## Parameters.Add "1","_Quit"
'## Parameters.Add "Historical\Split/Skill\Summary Interval","_Report"
'## Parameters.Add "1","_ACD"
'## Parameters.Add "4620","_Top"
'## Parameters.Add "3435","_Left"
'## Parameters.Add "14505","_Width"
'## Parameters.Add "7755","_Height"
'## Parameters.Add "The report Historical\Split/Skill\Summary Interval was not found on ACD 1.","_ReportNotFound"
'## Parameters.Add "*","_BeginProperties"
'## Parameters.Add "71","Split/Skill"
'## Parameters.Add "-1","Date"
'## Parameters.Add "00:00-23:30","Times"
'## Parameters.Add "*","_EndProperties"
'## Parameters.Add "*","_BeginViews"
'## Parameters.Add "*","_EndViews"
'## Parameters.Add "C:\CMS Scripts\skill.txt","_Output"
'## Parameters.Add "9","_FldSep"
'## Parameters.Add "0","_TextDelim"
'## Parameters.Add "False","_NullToZero"
'## Parameters.Add "True","_Labels"
'## Parameters.Add "True","_DurSecs"

On Error Resume Next

Dim sk(14, 1)

Sk(0, 0) = "71"
sk(0,1) = "C:\CMS Scripts\skill71.txt"
sk(1, 0) = "2"
sk(1,1) = "C:\CMS Scripts\skill2.txt"
sk(2, 0) = "21"
sk(2,1) = "C:\CMS Scripts\skill21.txt"
sk(3, 0) = "31"
sk(3,1) = "C:\CMS Scripts\skill31.txt"
sk(4, 0) = "15"
sk(4,1) = "C:\CMS Scripts\skill15.txt"
sk(5, 0) = "50"
sk(5,1) = "C:\CMS Scripts\skill50.txt"
sk(6, 0) = "42"
sk(6,1) = "C:\CMS Scripts\Hskill42.txt"
sk(7, 0) = "51"
sk(7,1) = "C:\CMS Scripts\skill51.txt"
sk(8, 0) = "52"
sk(8,1) = "C:\CMS Scripts\skill52.txt"
sk(9, 0) = "32"
sk(9,1) = "C:\CMS Scripts\skill32.txt"
sk(10, 0) = "1"
sk(10,1) = "C:\CMS Scripts\skill1.txt"
sk(11, 0) = "11"
sk(11,1) = "C:\CMS Scripts\skill11.txt"
sk(12, 0) = "54"
sk(12,1) = "C:\CMS Scripts\skill54.txt"
sk(13, 0) = "30"
sk(13,1) = "C:\CMS Scripts\skill30.txt"
sk(14, 0) = "17"
sk(14,1) = "C:\CMS Scripts\skill17.txt"


for i = 1 to 15

cvsSrv.Reports.ACD = 1
Set Info = cvsSrv.Reports.Reports("Historical\Split/Skill\Summary Interval")

If Info Is Nothing Then
If cvsSrv.Interactive Then
MsgBox "The report Historical\Split/Skill\Summary Interval was not found on ACD 1.", vbCritical Or vbOKOnly, "Avaya CMS Supervisor"
Else
Set Log = CreateObject("ACSERR.cvsLog")
Log.AutoLogWrite "The report Historical\Split/Skill\Summary Interval was not found on ACD 1."
Set Log = Nothing
End If
Else

b = cvsSrv.Reports.CreateReport(Info,Rep)
If b Then

Rep.Window.Top = 4620
Rep.Window.Left = 3435
Rep.Window.Width = 14505
Rep.Window.Height = 7755



Rep.SetProperty "Split/Skill",sk(i-1,0)

Rep.SetProperty "Date","-1"

Rep.SetProperty "Times","00:00-23:30"




b = Rep.ExportData(sk(i-1, 1), 9, 0, False, True, True)





Rep.Quit



If Not cvsSrv.Interactive Then cvsSrv.ActiveTasks.Remove Rep.TaskID
Set Rep = Nothing
End If

End If
Set Info = Nothing

next


'## cvs_cmd_end

End Sub


I used an array to loop thru each skill and repeat the code that saves the report, if you have any questions please write to me.

Have you (or has anyone) ever tried something similar with VBA in MS Access?

I'm making some progress with Access, but there are some slightly different behaviors I'm noticing.

One other question - do you know how you would refresh (or simulate a refresh) a historical report via VBA? Similar to .RefreshInterval on a realtime report. It has a restart in the menu which is kind of what I'm thinking, restart it, feed it the .SetProperty values again, etc. Or is it just as fast to close it and reopen?