Using Excel VBA to run Avaya CMS reports

[mac_see]

I have imported the following references in the VBE window of M---cel 2000 (9.0.6926 SP-3):
Application component : acsApp.exe
Server component : acsSRV.exe
Connection component : cvsconn.dll
Report Component : acsRep.exe
Loggin and Error Handling Component : cvserr.dll
ACSLog - Error routing and logging : CVSLog.dll

I have a function "CMSGetReport" (with 9 parameters)
I have a sub "Pull_Report" which calls the "CMSGetReport" function and puts it in cell A1 of the workbook.

Question1: The above 6 references that I listed, do I really require them?
Question2: Do I require more references than these?
Question3: (Most important one). The function and the sub works fine. The only problem is that every time the function is executed, an instance of Server (acsSRV.exe) is added in the task manager. If I run this sub 10 times, there will be 10 instances of the Server. How do I eliminate this problem?

  Sub Pull_Report()
  Cells.ClearContents
  Range("A1").Select
  Call CMSGetReport("xx.xxx.xxx.xx", 2, "Historical\Designer\Summary Interval", "Splits/Skills", "Group", "Date", Date, "Times", "00:00-23:30")
  End Sub

  Public Function CMSGetReport( _
       sServerIP As String, _
       iACD As Integer, _
       sReportName As String, _
       sProperty1Name As String, _
       sProperty1Value As String, _
       sProperty2Name As String, _
       sProperty2Value As String, _
       Optional sProperty3Name As String, _
       Optional sProperty3Value As String) As Boolean
   
  Const Username As String = "1234"
  Const Password As String = "mypwd"
   
  Dim cvsApp As Object
  Dim cvsConn As Object
  Dim cvsSrv As Object
  Dim Rep As Object
   
  Dim Info As Object, Log As Object, b As Object
   
  Set cvsApp = CreateObject("ACSUP.cvsApplication")
  Set cvsConn = CreateObject("ACSCN.cvsConnection")
  Set cvsSrv = CreateObject("ACSUPSRV.cvsServer")
  Set Rep = CreateObject("ACSREP.cvsReport")
   
  If cvsApp.CreateServer(Username, "", "", sServerIP, False, "ENU", cvsSrv, cvsConn) Then
       If cvsConn.Login(Username, Password, sServerIP, "ENU") Then
       
           CMSGetReport = False
           
           On Error Resume Next
           cvsSrv.Reports.ACD = iACD
           Set Info = cvsSrv.Reports.Reports(sReportName)
           If Info Is Nothing Then
               If cvsSrv.Interactive Then
               MsgBox "The Report " & sReportName & " was not found on ACD" & iACD & ".", vbCritical Or vbOKOnly, "CentreVu Supervisor"
           Else
               Set Log = CreateObject("CVSERR.cvslog")
               Log.AutoLogWrite "The Report " & sReportName & " was not found on ACD" & iACD & "."
               Set Log = Nothing
           End If
       Else
           b = cvsSrv.Reports.CreateReport(Info, Rep)
           If b Then
               Debug.Print Rep.SetProperty(sProperty1Name, sProperty1Value)
               Debug.Print Rep.SetProperty(sProperty2Name, sProperty2Value)
               Debug.Print Rep.SetProperty(sProperty3Name, sProperty3Value)
               b = Rep.ExportData("", 9, 0, True, True, False)
               Rep.Quit
               CMSGetReport = True
               
               If Not cvsSrv.Interactive Then cvsSrv.ActiveTasks.Remove Rep.TaskID
                   Set Rep = Nothing
               End If
               
           End If
       
       Range("A1").Select
       ActiveSheet.Paste
       
       Set Log = Nothing
       Set Rep = Nothing
       Set cvsSrv = Nothing
       Set cvsConn = Nothing
       Set cvsApp = Nothing
       
       End If
  End If
          
  End Function

[that_techy_guy]

I have found out that by adding the following code you can disconnect the server (and avoid having multiple instances).

cvsConn.Disconnect
cvsSrv.Disconnect
cvsApp.Disconnect

I added this code just before you release your variables with the set = nothing lines.

Cheers,

That_tech_guy

[gkrishs]

Answer 3: move cvsApp, cvsConn and cvsSrv out of the sub and make them global variables. And below mentioned code should be out of the sub
Set cvsApp = CreateObject("ACSUP.cvsApplication")
Set cvsConn = CreateObject("ACSCN.cvsConnection")
Set cvsSrv = CreateObject("ACSUPSRV.cvsServer")
Then remove the following statments
Set Log = Nothing
Set Rep = Nothing
Set cvsSrv = Nothing
Set cvsConn = Nothing
Set cvsApp = Nothing

If you want to keep the code clean then create a seperate function (Sub) with the above statments which should be executed on close of the Excel workbook.

Answer 2: you don't need any extra references.
Answer 1: I have used VBScript and didn't have to use acsSRV.exe
You might want to give it a try.

[djkelabu]

anyone, HI i'm Newbie please advice

went i run code above i got an error "Invalid procedure call or argument"

what sholud i do?

[djkelabu]

HI All,

i'm newbie here, please advice

when i run code above, i got an error

"180539 4 2 -5 [06]acsRep cvsReport:Run:00010 -- '*Real-Time\Agent\Agent Report: VB error (5) Invalid procedure call or argument' "

can anyone help me