- Thank you received: 9
Welcome to the fracta.net forum!
Share your coding ideas or ask questions.
Share your coding ideas or ask questions.
Question Export CMS Supervisor R17 to Excel
- SMOnline
- Topic Author
- Visitor
9 years 2 months ago #513
by SMOnline
Export CMS Supervisor R17 to Excel was created by SMOnline
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
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
Please Log in or Create an account to join the conversation.
9 years 2 months ago #514
by roller
Replied by roller on topic Export CMS Supervisor R17 to Excel
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
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
Please Log in or Create an account to join the conversation.
- SMOnline
- Topic Author
- Visitor
9 years 2 months ago #515
by SMOnline
Replied by SMOnline on topic Export CMS Supervisor R17 to Excel
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!
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!
Please Log in or Create an account to join the conversation.
9 years 2 months ago #516
by roller
Replied by roller on topic Export CMS Supervisor R17 to Excel
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.
Please Log in or Create an account to join the conversation.
- SMOnline
- Topic Author
- Visitor
9 years 2 months ago #517
by SMOnline
Replied by SMOnline on topic Export CMS Supervisor R17 to Excel
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
'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
Please Log in or Create an account to join the conversation.
- SMOnline
- Topic Author
- Visitor
9 years 2 months ago #519
by SMOnline
Replied by SMOnline on topic Export CMS Supervisor R17 to Excel
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!
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!
Please Log in or Create an account to join the conversation.
Time to create page: 0.429 seconds