Question Excel VBA macro to automate Avaya CMS Supervisor reports

I think you are almost there but put the For loop after

If cvsConn.login(user, password, server, "ENU") Then

and before

On Error Resume Next

make sure you get the End If in the right places around your loop. At the end after Rep.Quit add set Rep = Nothing to clean up.

Hi roller,

Thank you for pointing out.

before this, I tried to put the for loop at different line, and the code worked but very slow.

However, your suggestion improves the speed a lot!

Thank you very much!

Regards,
HC

Hi Roller,

I was able to run this report on macro, however since I'm extracting 5 skills I had to create 5 modules in macro. My question is, is there a way to continuously extract the skill in just one module and have it pasted in different sheets for the specified skill.


Below is the report in CMS

StarCross wrote: Thank you for taking time on this Roller. I really appreciate it!

Here you go.

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

'## cvs_cmd_begin
'## ID = 2001
'## Description = "Report: Historical: VDN: Report (Skill) Interval: Export Data"
'## Parameters.Add "Report: Historical: VDN: Report (Skill) Interval: Export Data","_Desc"
'## Parameters.Add "Reports","_Catalog"
'## Parameters.Add "2","_Action"
'## Parameters.Add "0","_Quit"
'## Parameters.Add "Historical\VDN\Report (Skill) Interval","_Report"
'## Parameters.Add "1","_ACD"
'## Parameters.Add "8850","_Top"
'## Parameters.Add "6585","_Left"
'## Parameters.Add "16065","_Width"
'## Parameters.Add "6165","_Height"
'## Parameters.Add "The report Historical\VDN\Report (Skill) Interval was not found on ACD 1.","_ReportNotFound"
'## Parameters.Add "*","_BeginProperties"
'## Parameters.Add "3069639","VDN"
'## Parameters.Add "0","Date"
'## Parameters.Add "00:00-10:45","Times"
'## Parameters.Add "*","_EndProperties"
'## Parameters.Add "*","_BeginViews"
'## Parameters.Add "*","_EndViews"
'## Parameters.Add "","_Output"
'## Parameters.Add "9","_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\VDN\Report (Skill) Interval")

If Info Is Nothing Then
If cvsSrv.Interactive Then
MsgBox "The report Historical\VDN\Report (Skill) Interval was not found on ACD 1.", vbCritical Or vbOKOnly, "Avaya CMS Supervisor"
Else
Set Log = CreateObject("ACSERR.cvsLog")
Log.AutoLogWrite "The report Historical\VDN\Report (Skill) 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 = 8850
Rep.Window.Left = 6585
Rep.Window.Width = 16065
Rep.Window.Height = 6165



Rep.SetProperty "VDN","3069639"

Rep.SetProperty "Date","0"

Rep.SetProperty "Times","00:00-10:45"




b = Rep.ExportData("", 9, 0, True, True, True)





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

Here is what I currently have in my macro. It extracts just one skill, I got 5 of these in different modules.

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 VDN()
On Error GoTo e:
Application.ScreenUpdating = 0
sk = "3069639" ''''''' change this number to a valid skill number that matches you CMS installation
ThisWorkbook.Sheets(4).Cells.ClearContents

Set cvsSrv = cvsApp.Servers(1) '(cvsApp.Servers.Count) '''''' we set a reference to the server here
Call doRep("Historical\VDN\Report (Skill) Interval", 3069639)
ThisWorkbook.Sheets(4).Cells(1, 1).PasteSpecial
logout
Application.ScreenUpdating = 1


End Sub


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

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 Historical\VDN\Report (Skill) Interval was not found on ACD 1.", vbCritical Or vbOKOnly, "Avaya CMS Supervisor"
Else
Set Log = CreateObject("ACSERR.cvslog")
Log.AutoLogWrite "The report Historical\VDN\Report (Skill) Interval 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("VDN", 3069639)
Debug.Print Rep.SetProperty("Date", 0)
Debug.Print Rep.SetProperty("Times", "00:00-21:45")
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

Hi Roller,

I am facing similar trouble. pasted the code in vba. I am using ms office 2010. gives me error on

Set cvsSrv = cvsApp.Servers(1) '(cvsApp.Servers.Count) '''''' we set a reference to the server here

any help is much appriciated

No idea, what is the error what does your code look like. I have no way of telling like this.

Hi Roller,

thanks for a quick response to my query. My miss. Below is the code that i am running.

--> I have all the references set in vba
--> I have CMS running in my desktop

Code 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.

---

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 = "300" ''''''' 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

I get an error in the line below
Set cvsSrv = cvsApp.Servers(1) '(cvsApp.Servers.Count) '''''' we set a reference to the server here

--> i am running Avaya CMS Supervisor R16
--> Windows 7 64-bit OS
--> Ms Office 2010