Macro's - Multiple Powerplay Reports 1

[AndyFreeman]

Hi there

I have got to teh stage now where my Scheduler running on my Powerplay server is becoming unmabageable ie 300+ tasks per evening. So with the use of Macro's and Batch files i was hoping to clean this list up somewhat.

Currently i am writing lots of Macros for each individual PPR then adding each Mocro into a .BAT file.

Is there a simpler way of doing this, maybe pointing either the Macro or the .BAT file at a folder and it running through all PPR's or MCX files in one go?

Thanks in advance for any help you can offer

Regards

Andy

 

[christenhusz]

Hi,

Make a macro that scans one or more given directories for ppr file, which are then opened one by one by powerplay and saved. This is just one entry in your scheduler.

I think i have seen simular scripts examples in this newsgroup.

Martijn

 

[AndyFreeman]

Hi,

if you happen to come across the example, please could you post the link in here, i have looked around various places and do not appear to be able to find it.

I am also very new to scripting language

Cheers

 

[drlex]

Andy,
Use an array and load it with the filepaths using a DO WHILE loop.

Here's part of my script that reads all paths to PP reports from a specified directory. The last part is a loop that opens each report and saves an Excel (xl) version of each.

HTH,
lex

'Define variables
Dim directory, count
' for file location and counter
Dim x
' array variable
Dim strfilelocation as string ' location of account reports
Dim strfiledest as string ' location of files created
Dim strfilename(100) as string
' filenames for reports and xl extracts
...
strfilelocation = "C:\Accountant Views\"
'path to PP reports
strfiledest = "C:\xl\Accountant Reports_ folder\"
'path for xl reports
directory=Dir (strfilelocation & "*.ppr")
'way to get file names of just PP reports
...
'rest of macro
..
'PART I Here's the bit that fills the array
'
Do While directory<>""
count=count+1
Strfilename(count)=directory
Strfilename(count)=Left(strfilename(count),Len_(strfilename(count))-4)
'remove .ppr from the end of the path
directory=Dir
Loop
Close #1 'close file
'
'PART II Open Reports & save an xl version
' of the same name to the specified location
'Loop for no. of reports
For x = 1 to count
Set objPPRep = CreateObject("CognosPowerPlay.Report")
strfile = strfilelocation+strfilename(x) & ".ppr"
objPPRep.Open strfile
objPPRep.SaveAs strfiledest+strfilename(x) & ".xls",4
objPPRep.Close
Set objPPRep = Nothing
Next x

soi la, soi carre

 

[AndyFreeman]

thanks for that.
none of it makes sense to me but will try and find sum1 in the web dept that might be able to figure it out

Andy

 

[drlex]

Andy,
OK. Please post back if you want more assistance.
I think I cobbled it together based on the contents of the Macro Help file entry for the 'Dir' function.

PS I note there's a couple of lines missing in the definitions -
Dim strfile as string
Dim objPPRep as Object
which I must have cut out in error.

soi la, soi carre

 

[AndyFreeman]

Below is the MAC file as it is now, when i try and eceute the file the line "Strfilename(count)=Left(strfilename(count),Len_(strfilename(count))-4)" goes red and no error is shown

Any Ideas??


Sub Main()
Dim objApp As Object
Dim objRep As Object

'Define variables
Dim directory, count
' for file location and counter
Dim x
' array variable
Dim strfilelocation as string ' location of account reports
Dim strfiledest as string ' location of files created
Dim strfilename(100) as string
Dim strfile as string
' filenames for reports and xl extracts
...
strfilelocation = "C:\PPR test\"
'path to PP reports
strfiledest = "C:\PPR test\HTML\"
'path for ppr reports
directory=Dir (strfilelocation & "*.ppr")
'way to get file names of just PP reports

Do While directory<>""
count=count+1
Strfilename(count)=directory
Strfilename(count)=Left(strfilename(count),Len_(strfilename(count))-4)
'remove .ppr from the end of the path
directory=Dir
Loop
Close #1 'close file

'PART II Open Reports & save an xl version
' of the same name to the specified location
'Loop for no. of reports
For x = 1 to count
Set objApp = CreateObject ("CognosPowerPlay.Application")
Set objRep = CreateObject("CognosPowerPlay.Report")
objApp.Visible False
strfile = strfilelocation+strfilename(x) & ".ppr"
objRep.Open strfile
objRep.Publish strfiledest+strfilename(x) & ".htm",4
objRep.Close
objApp.Quit
Set objApp = Nothing
Set objRep = Nothing
Next x


End Sub

 

[drlex]

the underscore is to show a non-breaking Carriage Return - that the line is merely split in two for readability.
Remove it and it should be ok

soi la, soi carre

 

[AndyFreeman]

First of all thanks so much of your help on this, i would not have stood a chance without ur advice

Looks like am getting somwhere now, the macro is opening the report, and powerplay is opening a Publish to screen, it does not appear to like the filelocation and filename variable.???

 

[drlex]

Andy,
For me, the Publish command seems to only require a path to a folder to publish to, as it takes the filename from the report.

try just:
objPPRep.Publish "C:\PPR test\HTML"

or:
strfiledest = "C:\PPR test\HTML"
..
objPPRep.Publish strfiledest
instead.


soi la, soi carre

 

[AndyFreeman]

Got it working now. Massiv ehtank goes out to drlex for taking teh time to help me out on this one. This has proven invaluable for our business and has taken a lot of admin work from us. Previously we would have to add a recurring task within the Sheduler.
Am going to try and work on some logging of these Macros to text file now so we know where problems occured if any happen overnight. As we run 100+ reports per night


Sub Main()
Dim objApp As Object
Dim objRep As Object

'Define variables
Dim directory, count
' for file location and counter
Dim x
' array variable
Dim strfilelocation as string ' location of account reports
Dim strfiledest as string ' location of files created
Dim strfilename(100) as string
Dim strfile as string
' filenames for reports and xl extracts

strfilelocation = "C:\PPR test\"
'path to PP reports
strfiledest = "C:\PPR test\HTML"
'path for ppr reports
directory=Dir (strfilelocation & "*.ppr")
'way to get file names of just PP reports

Do While directory<>""
count=count+1
Strfilename(count)=directory
Strfilename(count)=Left(strfilename(count),Len(strfilename(count))-4)
'remove .ppr from the end of the path
directory=Dir
Loop
Close #1 'close file

'PART II Open Reports & save an xl version
' of the same name to the specified location
'Loop for no. of reports
For x = 1 to count
Set objApp = CreateObject ("CognosPowerPlay.Application")
Set objRep = CreateObject("CognosPowerPlay.Report")
objApp.Visible False
strfile = strfilelocation+strfilename(x) & ".ppr"
objRep.Open strfile
objRep.Publish strfiledest
objRep.Close
objApp.Quit
Set objApp = Nothing
Set objRep = Nothing
Next x


End Sub

 

[drlex]

Andy - thanks for the star; you're too kind.

If you want error logging, use the "On Error" function.
Rather than a text file, I set it to send me an email about the problem when it occurs. Since I'm not there at weekends, I get Exchange to forward it to me at home on a rule (sender and presence of 'weekend' in the title). I use SMTP due to the anti-virus system of Outlook preventing macro access - it needs to be running as a service on the Server/PC and be configured correctly.

To get you going:

Add to the macro near the start
Dim ObjEmail as Object
On Error goto ErrMes

And then a resume label, where you want to resume after an error.
Endmac:

and at the end:
Exit Sub
ErrMes:
'Error has occured - warning
strSubject = strfilename(x) + " report FAILURE"
If weekday(CVar(DATE))=1 then strSubject = strSubject + " at the weekend"
If weekday(CVar(DATE))=7 then strSubject = strSubject + " at the weekend"
strBody = "Please note that the " strfilename(x) + " report failed with error " & Err & " : " & Error$ & " which occurred at line: " & Erl
strTo = "andy.freeman@bigco.co.uk"
'
'open and send email
'CDO mail object
Set objEmail = CreateObject("CDONTS.NewMail")
'CDO mail setttings
with objEmail
.To = strTo
.From = "Doomsayer"
.Subject = strSubject
.Body = strBody
.MailFormat = 0
.Send
End with

'end the use of the object and free memory
set objEmail = nothing
'
Resume endmac

End Sub

soi la, soi carre

 

[AndyFreeman]

Is it possible to publish to PDF instead of HTML. I have tried using the Help files but am getting some right strange errors

Andy

 

[drlex]

It's possible to publish as PDF, but I've never managed to select individual layers of a report, despite what the help file claims.

Sections of Code
...
Dim objPPRep as object
Dim objPDF as object
...
Set objPPRep = CreateObject("CognosPowerPlay.Report")
objPPRep.Open "C:\reports\test.ppr"
objPPRep.visible( TRUE )
Set objPDF = objPPRep.PDFFile( "C:\PDF\test" , True )
With objPDF
.SaveEntireReport = True
.SetListOfLayersToSave objPPRep.Layers
.SetListOfRowsToSave objPPRep.Rows
End With
objPDF.Save
objPPRep.Close
Set objPDF = Nothing

soi la, soi carre