Question Excel VBA Help - Set Object for Actively Running Report

Hey all,

I've been messing around with VBA today and I can't seem to figure this out I've searched around the web and I can't find any information on it either, so I've come here to seek some wisdom

As it stands we have on average 3 real-time reports running in the background that we monitor. I have working scripts to create a new report and pull the data from it, but I was hoping that I could make the process even more efficient by being able to use a currently running report to export the data from.

Would Excel's VBA have the capabilities to find the currently open reports, identify a specific one (by report name, as example), and export the data without closing the report or having to create the report from scratch?

Any insight into this would be greatly appreciated. Thanks in advance

Hummm I been looking at this all morning and so far I can not find a way in VBA to get to a running report Object. All I have gotten so far is a way to list all available report items that are present on the ACD server but can't find a connection between those and the ones that are running. I have other work to do, Below is the code I was messing with and I was in particular studying the cvsRepProp Object in the Watch window to see if there is any thing useful in there to you, but no luck so far. See if you can take it any further my head hit a dead end right now.


Function CMSConn()
sUserID = "xxxxx"
sPassword = "xxxxxx"
sServerIP = "xxxxx"
Dim cvsApp As cvsApplication
Dim cvsSrv As cvsServer
Dim cvsConn As cvsConnection
'Dim r As cvsReport
Dim iServer As Integer
Dim bConnected As Boolean

bConnected = False
Set cvsApp = New cvsApplication
Set cvsSrv = New cvsServer
Set cvsConn = New cvsConnection

For iServer = 1 To cvsApp.Servers.Count
Set cvsSrv = cvsApp.Servers(iServer)
If cvsSrv.ServerKey Like "*\" & sServerIP & "\*\*\*" Then Exit For
Next iServer

Dim cvsRepInfo As Object
Dim cvsRepProp As Object
Dim cvsLog As Object
Dim b As Boolean



cvsSrv.Reports.ACD = 1
Set r = cvsSrv.Reports.Reports

For i = 1 To r.Count
'Set cvsRepInfo = cvsSrv.Reports.Reports(r.Item(i).Key)
'b = cvsSrv.Reports.CreateReport(cvsRepInfo, cvsRepProp)
'cvsRepProp
'cvsRepProp.Quit
Debug.Print r.Item(i).Key
Next

If Not cvsSrv.Interactive Then cvsSrv.ActiveTasks.Remove cvsRepProp.TaskID
Set cvsRepProp = Nothing
Set cvsRepInfo = Nothing
If Not cvsSrv.Interactive Then cvsApp.Servers.Remove cvsSrv.ServerKey
cvsConn.Logout
cvsConn.Disconnect
cvsSrv.Connected = False
Set cvsConn = Nothing
Set cvsSrv = Nothing
Set cvsApp = Nothing

End Function

Another simpler approach could be to save the CMS script from the running reports to export to a text file then import them into Excel.

You can use VBA or Windows Task Scheduler to trigger the scripts and VBA to import them. That sounds like less work if we can't find a link to the running report Object via VBA!

Whew... I fried my mind trying to figure it out, but it was so worth it The code ended up being waaaay simpler than anticipated. And as I was hoping, it pulls the data instantaneously from the existing report Here's the basic code used for it:

Public Sub RealTimeData()
On Error GoTo 0
Application.ScreenUpdating = 0

'Find the right server
Dim ServerIP As String
For i = 1 To 5
Set cvsSrv = cvsApp.Servers(i)
ServerIP = cvsSrv.Name
If ServerIP = "**********" Or ServerIP = "**********" Then Exit For
Next i

Set Rep = cvsSrv.ActiveTasks.Item(2) 'Set the number to the report you are wanting it to pull (1 is the first report opened on the CMS server it's attached to)
t = Rep.ExportData("", 9, 0, False, True, True)
Worksheets("Sheet1").Cells(1, 1).PasteSpecial




That's essentially all it takes! I'll need to think of a good way for it to detect which report is Item 1, 2, etc. and choose the correct one. The hard part is over though!! Thanks for sharing some insight into it all

Yes, yes that's it. I was looking in the wrong places. Well now the hard part is done you can get the name of the reports and how many running by usining something like this:

For i = 1 To cvsSrv.ActiveTasks.Count
Set rep = cvsSrv.ActiveTasks.Item(i)
MsgBox rep.Name
MsgBox rep.Category
MsgBox rep.Window.Caption
'rep.Quit
Next i