Export all charts from all sheets to PowerPoint - VbScript

tschullo — Thu, 02 Jul 2015 13:44:49 GMT

I have been working on a script to accomplish this task.

It works except for some sort of timing issue when I switch sheets.

If I add a msgbox in between, it works flawlessly. Can someone please help?

I have looked at other macros out there and they fail for me the same way.

I am new to macros, running 11.20.11718.9 SR1

I am in the QV editor, haven't even tried to publish yet.

I have tried using SLEEP as well but to no avail.

Sub exportAllCharts
Set PPApp = CreateObject("Powerpoint.Application")
PPApp.Visible = True
Set PPPres = PPApp.Presentations.Add

for j = ActiveDocument.NoOfSheets - 1 to 0 Step -1
     set ss= ActiveDocument.GetSheet(j)
     shName=ss.GetProperties.Name
     'msgbox(shName & " - j:" & j ) ' NOTE: IF I UNCOMMENT THIS LINE IT WORKS
     ActiveDocument.Sheets(j).Activate
     ActiveDocument.GetApplication.WaitForIdle
     set s=ActiveDocument.ActiveSheet
     charts=s.GetGraphs
     for i=ubound(charts) to lbound(charts) step -1
          ActiveDocument.GetApplication.WaitForIdle
          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
     next
next
Set PPSlide = Nothing
Set PPPres = Nothing
Set PPApp = Nothing
MSGBOX "PowerPoint export is complete!", vbInformation,"PPT Export"
End Sub

Please help!

Re: Export all charts from all sheets to PowerPoint - VbScript

tschullo — Thu, 02 Jul 2015 16:25:18 GMT

Ok, so I added on On Error Resume Next before the first call to waitForIdle and now it works, go figure.

Also learned how to select first slide (or whichever one you want) after export:

first, save off the name of the slide in your loop using:

slideName=PPSlide.Name

Then, at the end and before you clear your PPT object vars, do this:

PPPres.Slides(slideName).Select

Re: Export all charts from all sheets to PowerPoint - VbScript

Anonymous — Tue, 04 Aug 2015 03:06:26 GMT

Hi Tony

good post on PPT export !!!

I require your help on the following post. Could you please help on it

re-size of 3rd dimension

topic Export all charts from all sheets to PowerPoint - VbScript in QlikView

Export all charts from all sheets to PowerPoint - VbScript

Re: Export all charts from all sheets to PowerPoint - VbScript

Re: Export all charts from all sheets to PowerPoint - VbScript