Action to export CCPulse-stats to file or DB

Marpos · February 26, 2016, 4:17pm

Im interested on your script too.

How do you set it to keep running all the day ?, and also, how you set 15min of delay ?

Thanks !

Wombat · February 29, 2016, 9:51am

Glad you like it. Here’s the code for the delays;



'public sub to allow delayed timings to be used in other code
Public Declare Sub Delay Lib "kernel32" Alias "Sleep" (ByVal dwMilliseconds As Long)

So the sub itself is called “Delay” and the number is a wait time in Milliseconds. To pause for one second you enter a line as “Delay 1000”.

If you want this to run every 15 minutes there are two options;

use Task Scheduler
You can use windows task scheduler to set the Excel workbook to run at a particular time of day, and every 15 minutes after. Because the CCPulse reading macro needs to interact with the screen it has to run within a normal user session. For this reason you can’t have Task Scheduler call the Excel sheet directly as it runs in a hidden session. Instead use Task Scheduler to call a batch file and use that batch file to run the Excel workbook.

Batch file; open Notepad and type in ‘call’, then the full path to your excel book. Save this with the extension .bat and then point Task Scheduler to this file to run it.



call "c:\ProgramData\Reporting\CCPulse Reader.xlsm"

Hint: you’ll also need to set the macro up to run when the workbook is opened

use the delay code and call the macro from within itself
Instead of using Task Scheduler you could end the earlier macro with



Delay 900000

Call CCPulse_reader

A note of caution though… This will need to be tuned to subtract the time it takes for the main macro to run on your machine. If you don’t tune it the code will run, then it will wait 15 minutes, then it will run again you will quickly lose sync with the intervals.

Remember also that the main macro will overwrite the saved files so you will need to hive these off between the intervals or you’ll lose them.

Marpos · February 29, 2016, 4:35pm

Thanks man, ill give it a try.

I couldnt make CCPulse to regulary save stats so this will save me =)

portal230 · May 25, 2016, 7:19am

Wombat post:16:

I’ve been working on something similar whilst we work on getting SDK installed. I’ve ended up with the following Excel Macro that I use to save all the active CCPulse windows in .HTML format. After that you can import these into whatever format you prefer for your final report.
Sub CCPulse_Save_Windows()

'should add a check that only one CCPulse instance is running

'set a variable as CCP_window so we can run a loop and count repeats
    Dim CCP_window As Integer
    CCP_window = 1

'move focus to CCPulse
    AppActivate "CCPulse+"
    
'run loop to save all 8 windows
'do until used in case of a problem so we don't get an infinite loop
    
    Do Until CCP_window = 10

'on 9th loop we want to go back to excel so we add this here
        If CCP_window = 9 Then GoTo RESET
    
'save windows 1-8 in the loop
        SendKeys "%W", True
        Delay 400
        SendKeys CCP_window, True
        Delay 400
        SendKeys "%F", True
        Delay 400
        SendKeys "H", True
        Delay 400
        SendKeys "%S", True
        Delay 400
        SendKeys "%Y", True
        Delay 400
        
'add one to the counter so next loop triggers next window
        CCP_window = CCP_window + 1
    
    Loop
    
'exit command in case we reach loop 10 and exit loop
'clear variable
    CCP_window = vbNullInteger
    
    Exit Sub

'on 9th loop we call this section of code instead of saving a window
RESET:

'move focus back to Excel
    'AppActivate ("Microsoft Excel")
    Workbooks("CCPulse Mailer Test.xlsm").Activate
    Delay 250
    Range("A1").Select
    Delay 250
    
'reset NUMLOCK on as this is turned off by a bug with multiple sendkey commands
    SendKeys "{NUMLOCK}", True
    
'clear variable
    CCP_window = vbNullInteger
    
End Sub
Notes:

will work with up to 8 windows which is the maximum that Pulse will show in a single list

if you have less than 8 windows adjust all the numbers in the ‘Do…Loop’ part of the code to match

the code re-writes the files over existing files (that’s what the ‘SendKeys “%Y”, True’ is for) so you’ll need to manually save them first for the script to work

Hello. I try to test this script in my Microsoft Exclel 2010(CCPulse+ 8.0.0.36), but this solution is not work. No error in script. Macros security disabled. Do I understand correctly, contents windows of the CCPulse is saved in html file? In what the folder on disk located html file ?

Script
Public Declare Sub Delay Lib “kernel32” Alias “Sleep” (ByVal dwMillisecond As Long)
Sub CCPulse_Save_Windows()

'should add a check that only one CCPulse instance is running

'set a variable as CCP_window so we can run a loop and count repeats

Dim CCP_window As Integer
CCP_window = 1

'move focus to CCPulse
AppActivate “CCPulse+”

'run loop to save all 8 windows
'do until used in case of a problem so we don’t get an infinite loop

Do Until CCP_window = 10

'on 9th loop we want to go back to excel so we add this here
If CCP_window = 9 Then GoTo RESET

'save windows 1-8 in the loop
SendKeys “%W”, True
Delay 400
SendKeys CCP_window, True
Delay 400
SendKeys “%F”, True
Delay 400
SendKeys “H”, True
Delay 400
SendKeys “%S”, True
Delay 400
SendKeys “%Y”, True
Delay 400

'add one to the counter so next loop triggers next window
CCP_window = CCP_window + 1

Loop

'exit command in case we reach loop 10 and exit loop
'clear variable
CCP_window = vbNullInteger

Exit Sub

'on 9th loop we call this section of code instead of saving a window
RESET:

'move focus back to Excel
'AppActivate (“Microsoft Excel”)
Workbooks(“CCPulse Mailer Test.xlsm”).Activate
Delay 250
Range(“A1”).Select
Delay 250

'reset NUMLOCK on as this is turned off by a bug with multiple sendkey commands
SendKeys “{NUMLOCK}”, True

'clear variable
CCP_window = vbNullInteger

End Sub

Wombat · June 2, 2016, 10:46pm

Hi Portal

When you lauch CCPulse and set the views you need then…

Manually go through the file > save as HTML options in CCPulse to save the first window. After that all other windows saved by the macro will go to the same folder. You need to repeat this manual save each time CCPulse is restarted otherwise the files will save to the default location.

Wombat · June 2, 2016, 11:02pm

Also…

Whilst my macro works really well I need to make a step change improvement to handle more windows. Is there an action script that will save all open veiws? I can use either save to html or an odbc (oracle) command.

Basic requirements are that at ‘x’ interval CCPulse will save the contents of all open windows (more correctly views) as .html files. If .html files are tricky is there an ODBC option?

Teaser; if I get a solution that works I’ll share some extensions to my macro which extend capacity to ten views and how to import these files into excel