Question Excel VBA macro to automate Avaya CMS Supervisor reports-v2

Hi Roller,

as requested:

here is the VBA Code:

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, sk As Variant
Dim logged As Boolean

Public Sub CMSConn2()
On Error GoTo e:
Application.ScreenUpdating = 0
sk = "201;202;209;214;225;203;205;213;247;248;249;250" ''''''' 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\Designer\Command Prgm Intrvl Info multi-skill", sk)
ThisWorkbook.Sheets(1).Cells(5, 3).PasteSpecial ' declare the destination of data
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

here is the Script from CMS AUTO:


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

'## cvs_cmd_begin
'## ID = 2001
'## Description = "Report: Historical: Designer: Command Prgm Intrvl Info multi-skill: Export Data"
'## Parameters.Add "Report: Historical: Designer: Command Prgm Intrvl Info multi-skill: Export Data","_Desc"
'## Parameters.Add "Reports","_Catalog"
'## Parameters.Add "2","_Action"
'## Parameters.Add "0","_Quit"
'## Parameters.Add "Historical\Designer\Command Prgm Intrvl Info multi-skill","_Report"
'## Parameters.Add "1","_ACD"
'## Parameters.Add "3105","_Top"
'## Parameters.Add "45","_Left"
'## Parameters.Add "19110","_Width"
'## Parameters.Add "9165","_Height"
'## Parameters.Add "default","_TimeZone"
'## Parameters.Add "The report Historical\Designer\Command Prgm Intrvl Info multi-skill was not found on ACD 1.","_ReportNotFound"
'## Parameters.Add "*","_BeginProperties"
'## Parameters.Add "201;202;209;214;225;203;205;213;247;248;249;250","Splits/Skills"
'## Parameters.Add "0","Date"
'## Parameters.Add "0-23:59","Times"
'## Parameters.Add "*","_EndProperties"
'## Parameters.Add "*","_BeginViews"
'## Parameters.Add "*","_EndViews"
'## Parameters.Add "\\ti\ortigas\CPlanning\Workforce\COMMCEN\OPERATIONS CENTER\TI_CCC_101\OC- MISCELLANEOUS_FILES\ErwinP\CMS SCRIPTS\TSYS AUTO\Intraday\combined.txt","_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\Designer\Command Prgm Intrvl Info multi-skill")

If Info Is Nothing Then
If cvsSrv.Interactive Then
MsgBox "The report Historical\Designer\Command Prgm Intrvl Info multi-skill was not found on ACD 1.", vbCritical Or vbOKOnly, "Avaya CMS Supervisor"
Else
Set Log = CreateObject("ACSERR.cvsLog")
Log.AutoLogWrite "The report Historical\Designer\Command Prgm Intrvl Info multi-skill was not found on ACD 1."
Set Log = Nothing
End If
Else

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

Rep.Window.Top = 3105
Rep.Window.Left = 45
Rep.Window.Width = 19110
Rep.Window.Height = 9165


Rep.TimeZone = "default"



Rep.SetProperty "Splits/Skills","201;202;209;214;225;203;205;213;247;248;249;250"

Rep.SetProperty "Date","0"

Rep.SetProperty "Times","0-23:59"




b = Rep.ExportData("\\ti\ortigas\CPlanning\Workforce\COMMCEN\OPERATIONS CENTER\TI_CCC_101\OC- MISCELLANEOUS_FILES\ErwinP\CMS SCRIPTS\TSYS AUTO\Intraday\combined.txt", 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

note: There are times that 5-7 skills are entered in Command Prgm Intrvl Info multi-skill seperated by colon though there are times it is just 1 skill.

Where's the rest of the vba code, where is the callRep sub? Can you show me what you did there I think that's where the problem might be.

I did not do anything else except for the interchanging of parameter's type from Integer to Variant. both did not solve the issue.

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 " & 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("Splits/Skills:", sk)
Debug.Print Rep.SetProperty("Date:", "0")
Debug.Print Rep.SetProperty("Times:", "00:00-23:59")
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

By the way. I just noticed just now that not only skill is having the issue. Same goes to Date and Time.

I cant see anything wrong with the code and the fact that it is running once confirms it. The problem is what is going on after the first run and before the second run.

What I think is happening is that your code is running only once. The subsequent runs the Avaya code is not running, what you are seeing is Excel pasting the clipboard from the first run over and over. So it didn't run again with the first skill and date.

How are you running the report the second time, how are you repeating the doRep? are you calling the Logout sub in between? If so you might be loosing your Server Objects. Can you tell me more about how you run it the second time?

I don't see any problem with the code as well so that is why I am puzzled why these 3 codes below did not materialize.

Debug.Print Rep.SetProperty("Splits/Skills:", sk)
Debug.Print Rep.SetProperty("Date:", "0")
Debug.Print Rep.SetProperty("Times:", "00:00-23:59")

well first of all. We use the same CMS alot, like every hour. So right before I run the automation there are skills/date/time already loaded to Split/Skills, Date and Time. To keep the story short, they are already there in CMS history, so once I run the vba code, yes it runs, but it did not overwrite the old property that was entered manually. To answer your question yes, "logout" function is being called in between... Sequence of function "CMSConn2"( already have CMS logged in), "doRep"( since it is inside CMSConn2 function", once it reaches and runs the 3 Debug.Print code. it will show the CMS report however the old inputs were still there and was not replace ( where I believe it should be, right?). Am I doing the sequence wrong?