Question Export CMS Supervisor R17 to Excel

Hi All,

I know this topic already exists in a variety of forms both here on Fracta and elsewhere but I can't find a solution to my specific issue anywhere and it's driving me mad! I've set up an account just to post this so hope someone can help!

I've been trying to export a report from CMS Supervisor to Excel using VBA. (R17 using server version 16.2). I've tried various subtly different versions of the code below but in every instance I get to the line:
b = cvssrv.Reports.CreateReport(Info, rep)
and am then given the error:
Object variable or With block variable not set

Help!

Public Sub CMSConn()

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

Set cvsApp = CreateObject("ACSUP.cvsApplication")
Set cvsconn = CreateObject("ACSCN.cvsConnection")
Set cvssrv = CreateObject("ACSUPSRV.cvsServer")
Set rep = CreateObject("ACSREP.cvsReport")

serverAddress = "xxx"
UserName = "xxx"
passW = "xxx"

If cvsApp.CreateServer(UserName, "", "", serverAddress, False, "ENU", cvssrv, cvsconn) Then
If cvsconn.Login(UserName, passW, serverAddress, "ENU") Then

'On Error Resume Next
cvssrv.Reports.ACD = 1
Set Info = cvssrv.Reports.Reports("Historical\Designer\Consultant Statistics")
If Info Is Nothing Then
If cvssrv.Interactive Then
MsgBox "The Report " & "Historical\Designer\Consultant Statistics" & " was not found on ACD 1", vbCritical Or vbOKOnly, "CentreVu Supervisor"
Else
Set Log = CreateObject("ACSERR.cvslog")
Log.AutoLogWrite "The Report " & "Historical\Designer\Consultant Statistics" & " 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("Agent", "Flights")
Debug.Print rep.SetProperty("Dates", "26/02/2015")
b = rep.ExportData("", 9, 0, False, True, True)
Set wk = ThisWorkbook
wk.Sheets(1).Cells.ClearContents
wk.Sheets(1).Cells(1, 1).PasteSpecial

rep.Quit
If Not cvssrv.Interactive Then cvssrv.ActiveTasks.Remove rep.TaskID
Set rep = Nothing
End If
End If

Set Info = Nothing
End If

End If

cvsconn.Logout
cvsconn.Disconnect
cvssrv.Connected = False
Set Log = Nothing
Set rep = Nothing
Set cvssrv = Nothing
Set cvsconn = Nothing
Set cvsApp = Nothing

End Sub

you have the on error resume next commented out, you need that. take away the ' comment bit. It will always give an error but it will work if it resumes. never figured that one out myself, never bothered to find out why.

Also you are using late binding so start with object:

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

Hi Roller,

Thanks for the quick reply. I've followed your instructions regarding the 'On Error' line and the Dims.
Code now runs from beginning to end but I'm not getting any data downloaded into the spreadsheet.
I know there's something there to download as I've checked the report with the same parameters directly in CMS Supervisor.

Any other suggestions or advice before I tear the last of my hair out?!
Cheers!

Go to your report in Avaya and save it as a script. Then use Notepad to open that file. Copy and paste what you see in here to compare what you did.

OK, here's the Avaya script in full:

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

'## cvs_cmd_begin
'## ID = 2001
'## Description = "Report: Historical: Designer: Consultant Statistics: Print"
'## Parameters.Add "Report: Historical: Designer: Consultant Statistics: Print","_Desc"
'## Parameters.Add "Reports","_Catalog"
'## Parameters.Add "5","_Action"
'## Parameters.Add "1","_Quit"
'## Parameters.Add "Historical\Designer\Consultant Statistics","_Report"
'## Parameters.Add "1","_ACD"
'## Parameters.Add "-11145","_Top"
'## Parameters.Add "3015","_Left"
'## Parameters.Add "20400","_Width"
'## Parameters.Add "11295","_Height"
'## Parameters.Add "default","_TimeZone"
'## Parameters.Add "The report Historical\Designer\Consultant Statistics was not found on ACD 1.","_ReportNotFound"
'## Parameters.Add "*","_BeginProperties"
'## Parameters.Add "Flights","Agent Group"
'## Parameters.Add "26/02/2015","Dates"
'## Parameters.Add "*","_EndProperties"
'## Parameters.Add "*","_BeginViews"
'## Parameters.Add "*","_EndViews"

On Error Resume Next

cvsSrv.Reports.ACD = 1
Set Info = cvsSrv.Reports.Reports("Historical\Designer\Consultant Statistics")

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

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

Rep.Window.Top = -11145
Rep.Window.Left = 3015
Rep.Window.Width = 20400
Rep.Window.Height = 11295


Rep.TimeZone = "default"



Rep.SetProperty "Agent Group","Flights"

Rep.SetProperty "Dates","26/02/2015"




b = Rep.PrintReport





Rep.Quit



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

Good spot!
I did have that set to 'Agent Group' in some of my other attempts but missed it in this one.
Unfortunately, it hasn't changed the result - I still get nothing back in Excel.


Thanks for looking at this for me though. I really appreciate it.
If you have any more ideas, please let me know. I think I'm about ready to admit defeat!