Question How to extract report from Avaya CMS 17R

Hi All,

I am using this code to pull the report from cms r17. I can access the cms but unable to pull the report. Basically unable to understand the this variable "reportPrompt(1 To 2, 1 To 3) As String".
Need your help please provide how can i make it usefull. I picked this code from CMS Avaya forum.

Public Sub Single_CMS_Report_Extract()
On Error Resume Next
' Add the files specified below to the References section:
' Tools -> References -> Browse to the CMS directory,
' e.g.: "C:\Program Files\Avaya\CMS Supervisor R14"
Dim cmsApplication As ACSUP.cvsApplication 'acsApp.exe
Dim cmsServer As ACSUPSRV.cvsServer 'acsSRV.exe
Dim cmsConnection As ACSCN.cvsConnection 'cvsconn.dll
Dim cmsCatalog As ACSCTLG.cvsCatalog 'cvsctlg.dll
Dim cmsReport As ACSREP.cvsReport 'acsRep.exe

Dim myLog As String, myPass As String, myServer As String
Dim reportPath As String, reportName As String, reportPrompt(1 To 2, 1 To 3) As String
Dim exportPath As String, exportName As String

' Assigns Variables
myLog = "Username"
myPass = "password"
myServer = "xx.xxx.xxx.xxx"
reportPath = "Historical\CMS custom"
reportName = "AESC Multi Vector"
reportPrompt(1, 1) = "ENTER VECTOR(s)"
reportPrompt(1, 2) = "date"
reportPrompt(1, 3) = "Time"
reportPrompt(2, 1) = "312;307;316;301;305"
reportPrompt(2, 2) = "5/24/2018"
reportPrompt(2, 3) = "07:00-23:59"
exportPath = "C:\Users\parveen.parveen1\Documents"
exportName = "test.TXT"

' Open the CMS Application, launches acsApp.exe
' If a CMS Supervisor console is already open,
' the existing acsApp.exe is used.
Set cmsApplication = CreateObject("ACSUP.cvsApplication")
Set cmsServer = CreateObject("ACSUPSRV.cvsServer")
Set cmsConnection = CreateObject("ACSCN.cvsConnection")
cmsConnection.bAutoRetry = True

' Connects to the server, launches acsSRV.exe & ACSTrans.exe (2x)
If cmsApplication.CreateServer(myLog, myPass, "", myServer, False, "ENU", cmsServer, cmsConnection) Then
If cmsConnection.login(myLog, myPass, myServer, "ENU", "", False) Then
End If
End If

' Gets collection of Reports available on cmsServer
Set cmsCatalog = cmsServer.Reports
If cmsServer.Connected = False Then cmsServer.Reports.ACD = 1

' Sets parameters for report, launches ACSRep.exe (2x)
cmsCatalog.CreateReport cmsCatalog.Reports.Item(reportPath & reportName), cmsReport
If cmsReport.SetProperty(reportPrompt(1, 1), reportPrompt(2, 1)) And cmsReport.SetProperty(reportPrompt(1, 2), reportPrompt(2, 2)) And cmsReport.SetProperty(reportPrompt(1, 3), reportPrompt(2, 3)) Then
End If

' Runs report and extracts results
cmsReport.ExportData exportPath & exportName, 9, 0, False, False, True

' Kills active report & server
If Not cmsServer.Interactive Then
cmsServer.ActiveTasks.Remove cmsReport.TaskID
cmsApplication.Servers.Remove cmsServer.ServerKey
End If

' Logs out
cmsReport.Quit
cmsConnection.Logout
cmsConnection.Disconnect
cmsServer.Connected = False

' Releases objects
Set cmsReport = Nothing
Set cmsCatalog = Nothing
Set cmsConnection = Nothing
Set cmsServer = Nothing
Set cmsApplication = Nothing

End Sub

Hi whoever wrote this made it confusing unnecessarily. The reportPrompt(x, x) is just an array to hold different values. Each position of the array hold the date, time, etc. for example.
Now to run the code each report has properties which needs to be passed on via VBA. For example many reports have Date and Time as properties. You can see the properties when you run the report manually they are the boxes you fill in.

So instead of using:
cmsReport.SetProperty(reportPrompt(1, 2), reportPrompt(2, 2))

which hold from your code:

reportPrompt(1, 2) = "date"
reportPrompt(2, 2) = "5/24/2018"

You can use:

cmsReport.SetProperty("date", "5/24/2018") to hard enter those values.

Or you can choose not to use the array and using meaningful variable names to achieve the same results. For example:

myDateProperty = "date"
myDate = "5/24/2018" '' if American format or "24/5/2018" if Australian. Then you use:

cmsReport.SetProperty(myDateProperty , myDate)

Hoe this clears it up.

Thanks for the reply. but still code is not working. I could not get any report in the folder which i have provided.

Apart from this i used one more from Fracta.net which is working fine for me but on other systems is not working throwing the error. "ActiveX components cannot create" Bold and Italic line throw this error.
Below is the code

Public Function Single_CMS_Report_Extract(sUserID As String, sPassword As String, sServerIP As String)
On Error Resume Next
' Add the files specified below to the References section:
' Tools -> References -> Browse to the CMS directory,
' e.g.: "C:\Program Files\Avaya\CMS Supervisor R14"
Dim cmsApplication As ACSUP.cvsApplication 'acsApp.exe
Dim cmsServer As ACSUPSRV.cvsServer 'acsSRV.exe
Dim cmsConnection As ACSCN.cvsConnection 'cvsconn.dll
Dim cmsCatalog As ACSCTLG.cvsCatalog 'cvsctlg.dll
Dim cmsReport As ACSREP.cvsReport 'acsRep.exe


Set cmsApplication = CreateObject("ACSUP.cvsApplication")
Set cmsServer = CreateObject("ACSUPSRV.cvsServer")
Set cmsConnection = CreateObject("ACSCN.cvsConnection")
'cmsConnection.bAutoRetry = True


'For iServer = 1 To cmsApplication.Servers.Count
' Set cmsServer = cmsApplication.Servers(iServer)
' If cmsServer.ServerKey Like "*\" & sServerIP & "\*\*\*" Then
' bConnected = True
' MsgBox "Avaya Connected! Click OK to proceed."
' Exit For
' End If
'Next iServer


If bConnected = False Then
If cmsApplication.CreateServer(sUserID, sPassword, "", sServerIP, False, "ENU", cmsServer, cmsConnection) Then
If cmsConnection.Login(sUserID, sPassword, sServerIP, "ENU") Then
End If
End If
End If


Dim cvsRepInfo As Object
Dim cvsRepProp As Object
Dim cvsLog As Object
Dim b As Boolean

'On Error Resume Next

cmsServer.Reports.ACDs = 1
Set cvsRepInfo = cmsServer.Reports.Reports("Historical\Designer\CNX Ryder Split/Skill Interval SvcLvl")

If cvsRepInfo Is Nothing Then
If cmsServer.Interactive Then
MsgBox "The report was not found on ACD.", vbCritical Or vbOKOnly, "Avaya CMS Supervisor"
Else
Set cvsLog = CreateObject("ACSERR.cvsLog")
cvsLog.AutoLogWrite "The report was not found on ACD."
Set cvsLog = Nothing
End If
Else
b = cmsServer.Reports.CreateReport(cvsRepInfo, cvsRepProp)
If b Then
Application.DisplayAlerts = False
cvsRepProp.Window.Top = 40
cvsRepProp.Window.Left = 40
cvsRepProp.Window.Width = 40
cvsRepProp.Window.Height = 40


cvsRepProp.SetProperty "Splits/Skills", ThisWorkbook.Sheets(1).Range("x7").Value
cvsRepProp.SetProperty "Date", ThisWorkbook.Sheets(1).Range("x8").Value
cvsRepProp.SetProperty "Times", ThisWorkbook.Sheets(1).Range("x9").Value

b = cvsRepProp.ExportData("", 9, 0, False, True, True)
'Closes report
If bConnected = True Then
cvsRepProp.Quit
Else
If Not cmsServer.Interactive Then cmsServer.ActiveTasks.Remove cvsRepProp.TaskID
End If

Set cvsRepProp = Nothing
End If
End If

Set cvsRepInfo = Nothing
If Not cmsServer.Interactive Then cmsApplication.Servers.Remove cmsServer.ServerKey

If bConnected = False Then
cmsConnection.Logout
cmsConnection.Disconnect
cmsServer.Connected = False
End If

Set cmsConnection = Nothing
Set cmsServer = Nothing
Set cmsApplication = Nothing
End Function

I have come across this before but can't remember why. Are the other system using 64 bit Excel instead of 32?

Also instead of navigating to the directory yo can simply pick the references from the VBA references window - do that to be safe and check if it says MISSING. If so unselect and reselect from down the list. Try this on the PC that is throwing the error.

Finally you could try getObject instead of createObject for the connection. Perhaps the connection object is already running and you don't need to create a new one but instead get a the reference to it.

In my office all the systems are using 64bit. but could not find the reason why this code works only on system.
There is not missing reference but i unselected all and seleced again. but still getting the same error.

Is there any other way to get the reports from CMS and how can go through all the property and methods of CMS?

Did you try getObject instead of createObject?
Set cmsConnection = getObject("ACSCN.cvsConnection")

When I say 64 bits I am talking about Excel not Windows. From memory (you can double check by searching for this topic on the internet) Excel VBA 64 bits can not handle 32 bits libraries and Avaya connection is 32 bits. You might have Excel 32 bits, check in the Help menu in Excel to see what you are running.

You can view all methods and properties from Excel VBA editor. After you tick everything related to Avaya, you know them because the path will be in the Avaya directory, open the Object Explorer from the VBA editor - it's under one of the menus. There you can see the all.

Also be careful if you have been running VBA script over and over and it is crashing you will end up of build up of orphaned connections and you can not create any more. You can see them if you open the Task Explorer CTRL + ALT + DEL. If you see many tasks related to Avaya and repeated many times then that could cause problems. Terminate them all one by one or simply restart your PC.

Other than that I can see how I can help you. You could try to do a remote desktop session with me so I have a look but I doubt your IT will allow that if you work for a corporation.