Using VBA in excel to export data from Avaya CMS

Hi guys,

I'm totally stumped on this one. I'm trying to run a report from Avaya CMS using excel. I've been told it can be done but I can't find much on it in the Avaya forums apart from some code which purports to do what I want. The code is as follows:

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 2) As String
Dim exportPath As String, exportName As String

' Assigns Variables
myLog = "my CMS log in"
myPass = "my CMS password"
myServer = "server address"
reportPath = "Historical\Designer\"
reportName = "Agent Group Daily Summary Report"
reportPrompt(1, 1) = "Agent Group"
reportPrompt(1, 2) = "IPS Team View"
reportPrompt(2, 1) = "Dates"
reportPrompt(2, 2) = "-1"
exportPath = "C:\Documents and Settings\My 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)) 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

I've changed the variables so you don't have my log in details!

I keep on getting an error saying that excel is waiting for another application to perform an OLE action. Has anyone come across this before? Not sure whether this is a problem with the code, excel or CMS. If it's the latter then I apologise for posting to this forum.

Thanks, Chris