Avaya CMS data extraction in vba only works when single stepping

[madonnac]

When I run my code to retrieve data from a CMS report, if I single step through the code, the code works, but if I run it normally, it (eventually) fails, and MS Access stops responding.

' Sets parameters for report
cmsReport.SetProperty reportPrompt(1, 1), reportPrompt(1, 2)
cmsReport.SetProperty reportPrompt(2, 1), reportPrompt(2, 2)
cmsReport.SetProperty reportPrompt(3, 1), reportPrompt(3, 2)
' Runs report and extracts results
EmptyClipboard
b = cmsReport.ExportData("", 44, 0, True, True, True)
Do While ClipboardEmpty
    ' for some reason, avaya doesn't always copy to clipboard, so keep repeating it until it gives up the data!
    b = cmsReport.ExportData("", 44, 0, True, True, True)
Loop

the subroutines used for checking clipboard, and emptying it before I try to get the data into the clipboard:

Private Declare Function apiOpenClipboard Lib "user32" Alias "OpenClipboard" (ByVal hWnd As Long) As Long
Private Declare Function apiEmptyClipboard Lib "user32" Alias "EmptyClipboard" () As Long
Private Declare Function apiCloseClipboard Lib "user32" Alias "CloseClipboard" () As Long
Private Declare Function CountClipboardFormats Lib "user32" () As Long

Sub EmptyClipboard()
If apiOpenClipboard(0&) <> 0 Then
    Call apiEmptyClipboard
    Call apiCloseClipboard
End If
End Sub

Function ClipboardEmpty() As Boolean
ClipboardEmpty = (CountClipboardFormats() = 0)
End Function

I use late binding for the objects, and I have Avaya CMS 16.2 installed.
I have tried setting the timeouts, with no success (either line, or both, fails to solve the issue)

cmsConnection.lTimeOutSecs = 60
cmsConnection.bTimeOutEnable = False

 

[jfh9219]

Without seeing your whole script it is hard to offer suggestions. I think it works when you single step due to the time delay you inject in the process vs operating at computer speed. Ever look at your task manager when it stops running? Are there many acsXXX threads? As long as you clean up your environment for subsequent executions there shouldn't be a problem. I have a looping script that has been running continuously at this point for 54 days on my PC.

 

[madonnac]

There is nothing extra running. I have a server object, an application object, and I can initially see a report object.

For your enjoyment, the whole routine:

Dim cmsApplication As Object
Dim DaysData As Integer
Dim ACDNum As Long

Public Sub Single_CMS_Report_Extract(sServer As String, iACD As Integer, sSkills 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"
'acsApp.exe
'acsSRV.exe
'cvsconn.dll
'acsRep.exe

Dim cmsConnection As Object
Dim cmsServer As Object
Dim cmsReport As Object

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

Dim INFO As Object, b As Variant

' Assigns Variables
myLog = "UserName"
myPass = "12345"
myServer = sServer
reportPath = "Historical\Designer\"
reportName = "Data Scrape"
reportPrompt(1, 1) = "Splits/Skills"
reportPrompt(1, 2) = sSkills
reportPrompt(2, 1) = "Dates"
reportPrompt(2, 2) = "-1"
reportPrompt(3, 1) = "Times"
reportPrompt(3, 2) = "00:00-23:30"

' Open the CMS Application, launches acsApp.exe
' If a CMS Supervisor console is already open,
' the existing acsApp.exe is used.

Set cmsServer = CreateObject("ACSUPSRV.cvsServer")
Set cmsConnection = CreateObject("ACSCN.cvsConnection")
Set cmsReport = CreateObject("ACSREP.cvsReport")

' 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
        'cmsConnection.lTimeOutSecs = 60
        cmsConnection.bTimeOutEnable = False
        Set cmsReport = CreateObject("ACSREP.cvsReport")
        
        cmsServer.Reports.acd = iACD
        Me.txtSvr = myServer
        Me.txtACD = iACD
        DoEvents
        Set INFO = cmsServer.Reports.Reports(reportPath & reportName)
        
        If INFO Is Nothing Then
            MsgBox "Unable to find report"
        Else
            b = cmsServer.Reports.CreateReport(INFO, cmsReport)
            If b Then
                ' Sets parameters for report, launches ACSRep.exe (2x)
                cmsReport.SetProperty reportPrompt(1, 1), reportPrompt(1, 2)
                cmsReport.SetProperty reportPrompt(2, 1), reportPrompt(2, 2)
                cmsReport.SetProperty reportPrompt(3, 1), reportPrompt(3, 2)
                ' Runs report and extracts results
                EmptyClipboard
                b = cmsReport.ExportData("", 44, 0, True, True, True)
                Do While ClipboardEmpty
                    ' for some reason, avaya doesn't like export to file, so keep repeating it until it gives up the data!
                    b = cmsReport.ExportData("", 44, 0, True, True, True)
                Loop
                cmsReport.Quit
                If Not cmsServer.Interactive Then
                    cmsServer.ActiveTasks.Remove cmsReport.TaskID
                    cmsApplication.Servers.Remove cmsServer.ServerKey
                End If
            End If
        End If
        
        Set cmsReport = Nothing
        ' Logs out
        cmsConnection.Logout
        cmsConnection.Disconnect
        
    End If
    cmsServer.Connected = False
End If

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

End Sub

called using:

Single_CMS_Report_Extract "ServerIP",1,"1-3;5-9;12;15;77-84;1000-1044"