Question Excel VBA macro to automate Avaya CMS Supervisor reports

Blank folder means no trace reports were found. Make sure you use a valid Agent Group name, make sure there are Trace reports for the date you use - usually trace reports purge after few days so don't go back to many days. Check first manually if they exist.

Log in log out report can use the same logic just change the guts. A good way is to see what the code should be like is to export the script from Avaya and open it in Notepad you will see code similar to VBA.

I already have a sample macro for log in log out that also cleans up the output to the first log in and last log out per days for a given date range, see:

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

Hi Roller,

Thank you for your reply. It worked eventually. I didn't put in the correct server for the first time.

Actually I wanted to try to export designer reports and I found this thread:
fracta.net/fracta/index.php/forum/2-exce...cms-designer-reports

I copied your script and made the necessary changes but it's not working.
I realize that "Dim Rep As New Object" s in red color, giving error message compile error: expected identifier.

I attached the code and the script in the file, can you please kindly have a look?

Your assistance is very much appreciated.
Thank you!

Hi the file is not attached sucessfully... i'll paste the code and script then.

Dim cvsApp As Object
Dim cvsConn As Object
Dim cvsSrv As Object
Dim Rep As New Object
Dim Info As Object, b As Object
Dim logged As Boolean
Dim i As Integer



Public Sub CMSConn()
ThisWorkbook.Sheets(5).Cells.ClearContents

Set cvsApp = CreateObject("ACSUP.cvsApplication")
Set cvsConn = CreateObject("ACSCN.cvsConnection")
Set cvsSrv = CreateObject("ACSUPSRV.cvsServer")
Set Rep = CreateObject("ACSREP.cvsReport")

yourUserName = "hcyeap" '''' put yours in between the " "
yourPassword = "12345678"
SERVERNAME = "aaa-bbb-ccc" ''' change it to your IP I omited for security

If cvsApp.CreateServer(yourUserName, yourPassword, "", SERVERNAME, False, "ENU", cvsSrv, cvsConn) Then
If cvsConn.login(yourUserName, yourPassword, SERVERNAME, "ENU") Then
On Error Resume Next
cvsSrv.Reports.ACD = 3
Set Info = cvsSrv.Reports.Reports("Historical\Designer\CCE METRICS 2010 (New)")
If Info Is Nothing Then
MsgBox "It didn't work"
Else
On Error Resume Next
b = cvsSrv.Reports.CreateReport(Info, Rep)
If b Then
'Debug.Print Rep.TimeZone = "" ' you may or may need this
Debug.Print Rep.SetProperty("Splits/Skills", "146;22;33;46;84;85;217;241;101;104;181;147;66;117;264")
Debug.Print Rep.SetProperty("Dates", "1/4/2014-30/4/2014")
b = Rep.ExportData("", 9, 0, False, True, True)
ThisWorkbook.Sheets(5).Cells(1, 1).PasteSpecial
Rep.Quit
End If
End If
End If
End If
Set Info = Nothing
cvsConn.logout
cvsConn.Disconnect
cvsSrv.Connected = False
Set Rep = Nothing
Set cvsSrv = Nothing
Set cvsConn = Nothing
Set cvsApp = Nothing

End Sub

'LANGUAGE=ENU
'SERVERNAME=aaa-bbb-ccc
Public Sub Main()

'## cvs_cmd_begin
'## ID = 2001
'## Description = "Report: Historical: Designer: CCE METRICS 2010 (New): Export Data"
'## Parameters.Add "Report: Historical: Designer: CCE METRICS 2010 (New): Export Data","_Desc"
'## Parameters.Add "Reports","_Catalog"
'## Parameters.Add "2","_Action"
'## Parameters.Add "1","_Quit"
'## Parameters.Add "Historical\Designer\CCE METRICS 2010 (New)","_Report"
'## Parameters.Add "1","_ACD"
'## Parameters.Add "-30","_Top"
'## Parameters.Add "570","_Left"
'## Parameters.Add "17730","_Width"
'## Parameters.Add "12105","_Height"
'## Parameters.Add "The report Historical\Designer\CCE METRICS 2010 (New) was not found on ACD 1.","_ReportNotFound"
'## Parameters.Add "*","_BeginProperties"
'## Parameters.Add "146;22;33;46;84;85;217;241;101;104;181;147;66;117;264","Splits/Skills"
'## Parameters.Add "1/4/2014-30/4/2014","Dates"
'## Parameters.Add "1","ACD"
'## Parameters.Add "*","_EndProperties"
'## Parameters.Add "*","_BeginViews"
'## Parameters.Add "*","_EndViews"
'## Parameters.Add "G:\OPS\Auths - TL & Support\Scheduling\Report Automation\Daily Performance\Total.xls","_Output"
'## Parameters.Add "9","_FldSep"
'## Parameters.Add "0","_TextDelim"
'## Parameters.Add "True","_NullToZero"
'## Parameters.Add "True","_Labels"
'## Parameters.Add "False","_DurSecs"

On Error Resume Next

cvsSrv.Reports.ACD = 1
Set Info = cvsSrv.Reports.Reports("Historical\Designer\CCE METRICS 2010 (New)")

If Info Is Nothing Then
If cvsSrv.Interactive Then
MsgBox "The report Historical\Designer\CCE METRICS 2010 (New) was not found on ACD 1.", vbCritical Or vbOKOnly, "Avaya CMS Supervisor"
Else
Set Log = CreateObject("ACSERR.cvsLog")
Log.AutoLogWrite "The report Historical\Designer\CCE METRICS 2010 (New) was not found on ACD 1."
Set Log = Nothing
End If
Else

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

Rep.Window.Top = -30
Rep.Window.Left = 570
Rep.Window.Width = 17730
Rep.Window.Height = 12105



Rep.SetProperty "Splits/Skills","146;22;33;46;84;85;217;241;101;104;181;147;66;117;264"

Rep.SetProperty "Dates","1/4/2014-30/4/2014"

Rep.SetProperty "ACD","1"




b = Rep.ExportData("G:\OPS\Auths - TL & Support\Scheduling\Report Automation\Daily Performance\Total.xls", 9, 0, True, True, False)





Rep.Quit



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

End If
Set Info = Nothing
'## cvs_cmd_end

End Sub

at a glance you are missing the Rep.SetProperty "ACD","1" in the VBA. This report looks like it has a third property to set along side the Dates and the Splits/Skills. add it and try again.

Hi roller,

Thank you for the reply.

I added Debug.Print Rep.SetProperty("ACD", "1") and I changed cvsSrv.Reports.ACD = 1 (previously was 3)

However, it still doesn't solve the issue of "Dim Rep As New Object". It's in red color giving error message of "compile error: Expected: Identifier"

below is the latest code:

Dim cvsApp As Object
Dim cvsConn As Object
Dim cvsSrv As Object
Dim Rep As New Object
Dim Info As Object, b As Object
Dim logged As Boolean
Dim i As Integer



Public Sub CMSConn()
ThisWorkbook.Sheets(5).Cells.ClearContents

Set cvsApp = CreateObject("ACSUP.cvsApplication")
Set cvsConn = CreateObject("ACSCN.cvsConnection")
Set cvsSrv = CreateObject("ACSUPSRV.cvsServer")
Set Rep = CreateObject("ACSREP.cvsReport")

yourUserName = "hcyeap" '''' put yours in between the " "
yourPassword = "12345678"
SERVERNAME = "aaa-bbb-ccc" ''' change it to your IP I omited for security

If cvsApp.CreateServer(yourUserName, yourPassword, "", SERVERNAME, False, "ENU", cvsSrv, cvsConn) Then
If cvsConn.login(yourUserName, yourPassword, SERVERNAME, "ENU") Then
On Error Resume Next
cvsSrv.Reports.ACD = 1 '/ Changed to 1
Set Info = cvsSrv.Reports.Reports("Historical\Designer\CCE METRICS 2010 (New)")
If Info Is Nothing Then
MsgBox "It didn't work"
Else
On Error Resume Next
b = cvsSrv.Reports.CreateReport(Info, Rep)
If b Then
'Debug.Print Rep.TimeZone = "" ' you may or may need this
Debug.Print Rep.SetProperty("Splits/Skills", "146;22;33;46;84;85;217;241;101;104;181;147;66;117;264")
Debug.Print Rep.SetProperty("Dates", "1/4/2014-30/4/2014")
Debug.Print Rep.SetProperty("ACD", "1") 'Added
b = Rep.ExportData("", 9, 0, False, True, True)
ThisWorkbook.Sheets(5).Cells(1, 1).PasteSpecial
Rep.Quit
End If
End If
End If
End If
Set Info = Nothing
cvsConn.logout
cvsConn.Disconnect
cvsSrv.Connected = False
Set Rep = Nothing
Set cvsSrv = Nothing
Set cvsConn = Nothing
Set cvsApp = Nothing

End Sub