topic Re: Macro to send reports to PPT at once not working? in QlikView

Macro to send reports to PPT at once not working?

Thu, 06 Mar 2014 11:09:25 GMT

Hi,

I have found the below macro in site to send all reports from qvw to PPt all at a once. But it seems not working for me. can anyone let me know what is wrong in this?

sub ppt1

Set PPApp = CreateObject("Powerpoint.Application")
MsgBox("Hello")
PPApp.Visible = True ' Create a presentation
Set PPPres = PPApp.Presentations.Add
'''''''''' For All Sheets '''''''''
for j = 0 to ActiveDocument.NoOfSheets - 1

     ActiveDocument.Sheets(j).Activate
     set ss= ActiveDocument.GetSheet(j)
    shID =ucase(mid(ss.GetProperties.SheetID,10))
     shName=ss.GetProperties.Name

     ActiveDocument.GetApplication.WaitForIdle

     'ActiveDocument.GetApplication.Sleep 5000

     'msgbox shName & "--" & shID
''''''''''''''''''''''''''''''''''''''''''

     ActiveDocument.Sheets(shName).Activate

     ActiveDocument.Sheets(j).Activate
     set s=ActiveDocument.ActiveSheet
     charts=s.GetSheetObjects
           for i=lbound(charts) to ubound(charts)
                if(UCASE(mid(charts(i).getobjectid,10,2)))="CH" then
                     Set PPSlide = PPPres.Slides.Add(1, 1)
                     PPSlide.Shapes(1).Delete ' removes the title
                     PPSlide.Shapes(1).Delete ' removes the text box
                     ActiveDocument.GetSheetObject(charts(i).getobjectid).CopyBitmapToClipboard
                     PPSlide.Shapes.Paste
                     'ActiveDocument.GetApplication.Sleep 1000
                end if
     next
next ''for sheets next move

PPPres.SaveAs "C:\Qlikview\PresentationPPT.ppt"
PPPres.Close
PPApp.Quit
Set PPSlide = Nothing
Set PPPres = Nothing
Set PPApp = Nothing
end sub

Re: Macro to send reports to PPT at once not working?

marcus_sommer — Thu, 06 Mar 2014 12:23:23 GMT

What is wrong? Which error message you received? On which point breaks the execution? Did you see these Msgbox? How are the security settings? Have you installed PP?

- Marcus

Re: Macro to send reports to PPT at once not working?

Thu, 06 Mar 2014 12:26:42 GMT

Thanks. Yes I have installed PPT. It is showing error as below

ActiveX component can't create object: 'Powerpoint.Application'

Re: Macro to send reports to PPT at once not working?

marcus_sommer — Thu, 06 Mar 2014 12:29:55 GMT

There are two drop-down fields on the left side from macro-editor - the settings must be system access.

- Marcus

Re: Macro to send reports to PPT at once not working?

Thu, 06 Mar 2014 12:33:02 GMT

Thanks. You are right just now I rectify my mistake .

One more thing The PPT is opening but it is showing blank pages even though the file contains sheets. what Is this error?

Re: Macro to send reports to PPT at once not working?

marcus_sommer — Thu, 06 Mar 2014 12:43:30 GMT

The macro-code contains ucase() and mid() functions on the object-id's, perhaps you must these adjust for you. Also there are two activate-statements ... Create simple msgbox with the object-id or name within the loops to see if the execution how expected ...

- Marcus

Re: Macro to send reports to PPT at once not working?

Thu, 06 Mar 2014 13:22:54 GMT

Thanks. I tried replacing getobjectid with my chart Id. but still showing same. could you let me know elaborately what is Ucase and MId()

Re: Re: Macro to send reports to PPT at once not working?

marcus_sommer — Thu, 06 Mar 2014 13:40:55 GMT

For me it worked - see attachment with the qv test script. I think that your object-id's not fit to the code.

ucase() - in qv script = upper() - and mid() are string-functions, have here a look in Help.

- Marcus

Re: Macro to send reports to PPT at once not working?

Thu, 06 Mar 2014 13:49:40 GMT

for me it's not working and it is stopping at

ActiveDocument.Sheets(shName).Activate

Any ideas?

Re: Re: Macro to send reports to PPT at once not working?

marcus_sommer — Thu, 06 Mar 2014 13:59:55 GMT

I think you don't need these code-row, try an uncomment, perhaps i missed the initial set-statement:

Set shName=ss.GetProperties.Name

What is the result of: msgbox "SheetNumber " & j & " - " & shName

- Marcus

Re: Macro to send reports to PPT at once not working?

Thu, 06 Mar 2014 15:13:21 GMT

The result of msgbox is showing 'Hello'. again it is stopping in ActiveDocument.Sheets(shName).Activate

Re: Macro to send reports to PPT at once not working?

marcus_sommer — Thu, 06 Mar 2014 15:27:08 GMT

Have you tried to uncomment these row and/or to a different declaration from shName like:

Set shName=ss.GetProperties.Name

- Marcus

Re: Macro to send reports to PPT at once not working?

Thu, 06 Mar 2014 15:55:47 GMT

Sorry I am not getting, may be I am confusing can you please post the script?

Re: Macro to send reports to PPT at once not working?

marcus_sommer — Thu, 06 Mar 2014 15:55:56 GMT

Perhaps this is also helpful: Export all sheets to ppt

- Marcus

Re: Macro to send reports to PPT at once not working?

marcus_sommer — Thu, 06 Mar 2014 15:58:57 GMT

Within my post above is this example: test script from qv + 3 objects and your macro-code - it worked ...

- Marcus

Re: Macro to send reports to PPT at once not working?

Thu, 06 Mar 2014 16:33:42 GMT

Thanks and this is really helpful. But when I am executing this every 2 minutes to test it is stopping at ActiveDocument.GetApplication.WaitForIdle

And the PPT is opening with blank page

May I know what is this?

Re: Macro to send reports to PPT at once not working?

Fri, 07 Mar 2014 16:09:17 GMT

Any Idea of this as It is stucking at ActiveDocument.GetApplication.WaitForIdle

Re: Macro to send reports to PPT at once not working?

marcus_sommer — Sat, 08 Mar 2014 10:20:37 GMT

WaitForIdle meant to wait until any calculations is finished - for example by selections but it could be that it never will happen if you used some kind of time-functions or variables. I think in your case you don't need this code-row, make it simply a comment or removed it.

- Marcus