× Welcome to the fracta.net forum!

Share your coding ideas or ask questions.

How? CMS data automatic export to Excel

More
4 years 6 months ago #589 by Dizturbdone
I'm attempting to automate one of the reports I run out of CMS Supervisor, I need it to paste into a specific excel file and then save that file. I've tried following the code but it does not paste anything in my export file and I get and error saying excel has stopped working after everything closes and saves, any suggestions? I have this set to auto run when the excel file is open. I've removed usernames passwords and file paths.

Sub Auto_Open()
'
' Auto_Open Macro
'

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




'
Workbooks.Open Filename:= _
"Path\[export.xlsx"




Set cvsApp = CreateObject("ACSUP.cvsApplication")
Set cvsConn = CreateObject("ACSCN.cvsConnection")
Set cvsSrv = CreateObject("ACSUPSRV.cvsServer")
Set Rep = CreateObject("ACSREP.cvsReport")
serverAddress = "[my ip/i]"
Username = "my username"
passW = "my password"

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 = 2
Set Info = cvsSrv.Reports.Reports("IIntegrated\Designer\Bridge monitor SL - WALLBOARDV6")
If Info Is Nothing Then
If cvsSrv.Interactive Then
MsgBox "The Report " & "IIntegrated\Designer\Bridge monitor SL - WALLBOARDV6" & " was not found on ACD 2", vbCritical Or vbOKOnly, "CentreVu Supervisor"
Else
Set Log = CreateObject("ACSERR.cvslog")
Log.AutoLogWrite "The Report " & "IIntegrated\Designer\Bridge monitor SL - WALLBOARDV6" & " was not found on ACD 2"
Set Log = Nothing
End If
Else
b = cvsSrv.Reports.CreateReport(Info, Rep)
If b Then

Debug.Print Rep.SetProperty("Splits/Skills1", "0322;0323;0324;0321;0334;0318")
Debug.Print Rep.SetProperty("Splits/Skills2", "0333;0309;0305")
b = Rep.ExportData("", 44, 0, False, True, True)
Set wk = ThisWorkbook
wk.Sheets(1).Cells.ClearContents
wk.Sheets(1).Cells(1, 1).PasteSpecial
'b = Rep.ExportData(fileP, 44, 0, False, True, True)
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




Application.DisplayAlerts = False
ActiveWorkbook.Save
Application.Quit

End Sub

This is what my report spits out when I script it and open it in notepad, I have removed all file paths as they are network drives

'## cvs_cmd_begin
'## ID = 2001
'## Description = "Report: Integrated: Designer: Bridge monitor SL - WALLBOARDV6: Export Data"
'## Parameters.Add "Report: Integrated: Designer: Bridge monitor SL - WALLBOARDV6: Export Data","_Desc"
'## Parameters.Add "Reports","_Catalog"
'## Parameters.Add "2","_Action"
'## Parameters.Add "1","_Quit"
'## Parameters.Add "Integrated\Designer\Bridge monitor SL - WALLBOARDV6","_Report"
'## Parameters.Add "2","_ACD"
'## Parameters.Add "1335","_Top"
'## Parameters.Add "6015","_Left"
'## Parameters.Add "16770","_Width"
'## Parameters.Add "13530","_Height"
'## Parameters.Add "default","_TimeZone"
'## Parameters.Add "The report Integrated\Designer\Bridge monitor SL - WALLBOARDV6 was not found on ACD 2.","_ReportNotFound"
'## Parameters.Add "*","_BeginProperties"
'## Parameters.Add "0322;0323;0324;0321;0334;0318","Splits/Skills1"
'## Parameters.Add "0333;0309;0305","Splits/Skills2"
'## Parameters.Add "*","_EndProperties"
'## Parameters.Add "*","_BeginViews"
'## Parameters.Add "*","_EndViews"
'## Parameters.Add "Path\export.csv","_Output"
'## Parameters.Add "44","_FldSep"
'## Parameters.Add "0","_TextDelim"
'## Parameters.Add "False","_NullToZero"
'## Parameters.Add "True","_Labels"
'## Parameters.Add "True","_DurSecs"

On Error Resume Next

cvsSrv.Reports.ACD = 2
Set Info = cvsSrv.Reports.Reports("Integrated\Designer\Bridge monitor SL - WALLBOARDV6")

If Info Is Nothing Then
If cvsSrv.Interactive Then
MsgBox "The report Integrated\Designer\Bridge monitor SL - WALLBOARDV6 was not found on ACD 2.", vbCritical Or vbOKOnly, "Avaya CMS Supervisor"
Else
Set Log = CreateObject("ACSERR.cvsLog")
Log.AutoLogWrite "The report Integrated\Designer\Bridge monitor SL - WALLBOARDV6 was not found on ACD 2."
Set Log = Nothing
End If
Else

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

Rep.Window.Top = 1335
Rep.Window.Left = 6015
Rep.Window.Width = 16770
Rep.Window.Height = 13530


Rep.TimeZone = "default"



Rep.SetProperty "Splits/Skills1","0322;0323;0324;0321;0334;0318"

Rep.SetProperty "Splits/Skills2","0333;0309;0305"




b = Rep.ExportData("Path\export.csv", 44, 0, False, True, True)





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.

More
4 years 6 months ago #590 by roller
When nothing get pasted it means there a line of code with and error. There is in the code on error resume next so you won't know where the error is. First check in the immediate window to see if the lines Debug.Print Rep.SetProperty... are printing 2 x true in that debug print window. That at least tells you that everything is running fine to those lines of code. Let me know what you see.

Next the 44 in the export part i have never used. This is related to exporting the data to a file perhaps instead of the clipboard. Change that to 0 or 1 and see if it works.

Please Log in or Create an account to join the conversation.

More
4 years 6 months ago #591 by Dizturbdone
I took out the auto close ran through looked at immediate window it's printing back as False False.

You are correct on the 44 that was to print to file, I did another one to export to clipboard and instead of 44 it was a 9 so I have since changed it to that.

Please Log in or Create an account to join the conversation.

More
4 years 6 months ago #592 by Dizturbdone
I took out the on error resume line to follow it through to where it breaks, and am getting an error at

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

Object variable or With block variable not set is the error I'm getting

Please Log in or Create an account to join the conversation.

More
4 years 6 months ago #593 by roller
Your mistake is here:
Set Info = cvsSrv.Reports.Reports("IIntegrated\Designer\Bridge monitor SL - WALLBOARDV6")

Can you see it? IIntergrated should be Integrated

Please Log in or Create an account to join the conversation.

More
4 years 6 months ago #594 by Dizturbdone
I did find that, and now when I go to debug step by step I'm getting Microsoft is waiting for another application to complete an OLE action at this line

IF cvsConn.login(Username, passW, serverAddress, "ENU") Then

Please Log in or Create an account to join the conversation.

Time to create page: 0.579 seconds
Powered by Kunena Forum