Question VBA script to automate Avaya CMS designer reports

Can I use VBA to automate my Avaya CMS Supervisor 15 designer report?

Here is the code as seen when I open in notepad for mine:

'LANGUAGE=ENU
'SERVERNAME= xxx.xxx.101.100 removed for security
Public Sub Main()
'## cvs_cmd_begin
'## ID = 2001
'## Description = "Report: Historical: Designer: Global Skill Summay: Export Data"
'## Parameters.Add "Report: Historical: Designer: Global Skill Summay: Export Data","_Desc"
'## Parameters.Add "Reports","_Catalog"
'## Parameters.Add "2","_Action"
'## Parameters.Add "1","_Quit"
'## Parameters.Add "Historical\Designer\Global Skill Summay","_Report"
'## Parameters.Add "3","_ACD"
'## Parameters.Add "3225","_Top"
'## Parameters.Add "4020","_Left"
'## Parameters.Add "21720","_Width"
'## Parameters.Add "12960","_Height"
'## Parameters.Add "","_TimeZone"
'## Parameters.Add "The report Historical\Designer\Global Skill Summay was not found on ACD 3.","_ReportNotFound"
'## Parameters.Add "*","_BeginProperties"
'## Parameters.Add "1/1/2013-1/2/2013","Date(s)"
'## Parameters.Add "740;741","Skill(s)"
'## Parameters.Add "*","_EndProperties"
'## Parameters.Add "*","_BeginViews"
'## Parameters.Add "*","_EndViews"
'## Parameters.Add "C:\Users\bob\Documents\BoA\Report_EXE\automation\acddata.xls","_Output"
'## Parameters.Add "9","_FldSep"
'## Parameters.Add "0","_TextDelim"
'## Parameters.Add "True","_NullToZero"
'## Parameters.Add "False","_Labels"
'## Parameters.Add "True","_DurSecs"
On Error Resume Next
cvsSrv.Reports.ACD = 3
Set Info = cvsSrv.Reports.Reports("Historical\Designer\Global Skill Summay")
If Info Is Nothing Then
If cvsSrv.Interactive Then
MsgBox "The report Historical\Designer\Global Skill Summay was not found on ACD 3.", vbCritical Or vbOKOnly, "Avaya CMS Supervisor"
Else
Set Log = CreateObject("ACSERR.cvsLog")
Log.AutoLogWrite "The report Historical\Designer\Global Skill Summay was not found on ACD 3."
Set Log = Nothing
End If
Else

b = cvsSrv.Reports.CreateReport(Info,Rep)
If b Then
Rep.Window.Top = 3225
Rep.Window.Left = 4020
Rep.Window.Width = 21720
Rep.Window.Height = 12960
Rep.TimeZone = ""
Rep.SetProperty "Date(s)","1/1/2013-1/2/2013"
Rep.SetProperty "Skill(s)","740;741"
b = Rep.ExportData("C:\Users\bob\Documents\BoA\Report_EXE\automation\acddata.xls", 9, 0, True, False, True)

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

Try the below VBA script in Excel, it should export the report the sheet 1 cell A1. Make sure you change the yourUserName, yourPassword and SERVERNAME variable to your settings.


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(1).Cells.ClearContents

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

yourUserName = "replace" '''' put yours in between the " "
yourPassword = "replace"
SERVERNAME = "xxx.xxx.101.100" ''' 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\Global Skill Summay")
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("Skill(s)", "740;741")
Debug.Print Rep.SetProperty("Date(s)", "1/1/2013-1/2/2013")
b = Rep.ExportData("", 9, 0, False, True, True)
ThisWorkbook.Sheets(1).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

Hi Roller,

I have created a couple of automated cms script but I need to run it in "Asia/Tokyo" and "default" TimeZone respectively. I saw one of your posts and applied it on the script however the data generated was on "default" timezone even if it was set it to run in "Asia/Tokyo" TimeZone.

There was a huge discrepancy of the numbers pulled from the system which makes the it useless.

Can you please help me fix this issue? Shown below is the snapshot of the code.

'LANGUAGE=ENU
'SERVERNAME=***********(edited)
Public Sub Main()

'## cvs_cmd_begin
'## ID = 2001
'## Description = "Report: Historical: Designer: JnJ Summary Interval, ASA/ABA Time F: Export Data"
'## Parameters.Add "Report: Historical: Designer: JnJ Summary Interval, ASA/ABA Time F: Export Data","_Desc"
'## Parameters.Add "Reports","_Catalog"
'## Parameters.Add "2","_Action"
'## Parameters.Add "1","_Quit"
'## Parameters.Add "Historical\Designer\JnJ Summary Interval, ASA/ABA Time F","_Report"
'## Parameters.Add "1","_ACD"
'## Parameters.Add "1440","_Top"
'## Parameters.Add "5115","_Left"
'## Parameters.Add "14685","_Width"
'## Parameters.Add "9420","_Height"
'## Parameters.Add "Asia/Tokyo","_TimeZone"
'## Parameters.Add "The report Historical\Designer\JnJ Summary Interval, ASA/ABA Time F was not found on ACD 1.","_ReportNotFound"
'## Parameters.Add "*","_BeginProperties"
'## Parameters.Add "1686","Splits/Skills"
'## Parameters.Add "-2.08333","Dates"
'## Parameters.Add "00:00-23:30","Times"
'## Parameters.Add "*","_EndProperties"
'## Parameters.Add "*","_BeginViews"
'## Parameters.Add "*","_EndViews"
'## Parameters.Add "C:\Users\****(erased)****\Documents\CMS Data Files\JnJ Multi Skills-ASPAC.txt","_Output"
'## Parameters.Add "44","_FldSep"
'## Parameters.Add "0","_TextDelim"
'## Parameters.Add "True","_NullToZero"
'## Parameters.Add "True","_Labels"
'## Parameters.Add "True","_DurSecs"


On Error Resume Next

cvsSrv.Reports.ACD = 1
Set Info = cvsSrv.Reports.Reports("Historical\Designer\JnJ Summary Interval, ASA/ABA Time F")

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

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


Rep.Window.Top = 1440
Rep.Window.Left = 5115
Rep.Window.Width = 14685
Rep.Window.Height = 9420

Rep.TimeZone = "Asia/Tokyo"

Rep.SetProperty "Splits/Skills","1686"

Rep.SetProperty "Dates","-2"

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



b = Rep.ExportData("C:\Users\***(erased)****\Documents\CMS Data Files\JnJ Multi Skills-ASPAC.txt", 44, 0, True, True, True)



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