Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Errors in export Macro to ppt

Hi,

Using many different conversation, I have created the following macro to allow export of table and graph in ppt :

Sub export_ppt
'An array with the objects you want to export to powerpoint
Dim myArray(2)'change (2) by number of items to export
myArray(0) = "CH31" 'add chart or table reference in reverse order of slide
myArray(1) = "TB10"

'Create ppt object
Set objPPT = CreateObject("PowerPoint.Application")
objPPT.Visible = True
Set objPresentation = objPPT.Presentations.Add

'Apply a template if you want one
'objPresentation.ApplyTemplate _
' ("C:\mytemplate.ppt")

'For each object in the array create a new slide and paste it.
For each item in myArray
Set PPSlide = objPresentation.Slides.Add(1,1)
ActiveDocument.GetSheetObject(item).CopyBitmapToClipboard
PPSlide.Shapes.Paste

with PPSlide.Shapes(PPSlide.Shapes.Count)
.left = 0
.top = 200
.width=720
end with

Next
PPPres.SaveAs "C:\export QV.ppt"
PPPres.Close
PPApp.Quit

'Clean up
Set PPSlide = Nothing
Set PPPres = Nothing
Set PPApp = Nothing
End Sub

It works well except the following :

1. I have the error :

Object required: 'ActiveDocument.GetSheetObject(...)'

related to :

Set PPSlide = objPresentation.Slides.Add(1,1)

ActiveDocument.GetSheetObject(item).CopyBitmapToClipboard

PPSlide.Shapes.Paste

2. It creates one blank slide at the front of the deck.

How to remove

3. Probably because of the 1st error, it does not save.

thanks

TD

1 Solution

Accepted Solutions
erich_shiino
Honored Contributor

Re: Errors in export Macro to ppt

Hi, You change need to call the methods of other objects:

From the SaveAs until the end, the code should be:


objPresentation.SaveAs "export QV.pptx"

objPresentation.Close

objPPT.Quit

'Clean up

Set PPSlide = Nothing


Set objPPT = Nothing

End sub

Regards,

Erich

9 Replies
mwoolf
Honored Contributor II

Re: Errors in export Macro to ppt

Try replacing ActiveDocument with objPresentation

erich_shiino
Honored Contributor

Re: Errors in export Macro to ppt

Hi,

Does it work if you change the array to

ActiveDocument.GetSheetObject(CH31).CopyBitmapToClipboard

PPSlide.Shapes.Paste

and then

ActiveDocument.GetSheetObject(TB10).CopyBitmapToClipboard

PPSlide.Shapes.Paste

?

Are CH31 or TB10 minimized? I had problems with this before, please, try to have them restored or maximized before you try your code.

Also, I had problems with this type of code when QV would take long to copy and paste the object. You can try to include a

ActiveDocument.GetApplication.sleep 1000

or

ActiveDocument.GetApplication.WaitforIdle

Hope it helps,

Erich

Not applicable

Re: Errors in export Macro to ppt

thanks but it is not working.... it actually stop the copy and pasting completely.

Not applicable

Re: Errors in export Macro to ppt

thanks. there was an error in your string (missing the " " for each item):

ActiveDocument.GetSheetObject("CH31").CopyBitmapToClipboard

When I copy those, it does not position the bitmap properly on the document and it copies both graph on the same slide.

So correction of this would work but would extend dramatically the code if 10 or 15 items must be pasted....

Items are not minimized

Thanks for the tip on the WaitforIdle.

Cheers


erich_shiino
Honored Contributor

Re: Errors in export Macro to ppt

Hi,

I tried again with your array and was able to use it.

I also checked and you need to change the slides.add( 1, 1 ) including a counter.

Your code should be like this:

Dim myArray(3)'change (2) by number of items to export

    myArray(0) = "CH01"

    myArray(1) = "CH02" 'add chart or table reference in reverse order of slide

    myArray(2) = "CH03"

   

    'Create ppt object

    Set objPPT = CreateObject("PowerPoint.Application")

    objPPT.Visible = True

    Set objPresentation = objPPT.Presentations.Add

   

    For i =0 to 2

   Set PPSlide = objPresentation.Slides.Add(i+1,1)
   msgbox myArray(i)
   ActiveDocument.GetSheetObject(myArray(i)).CopyBitmapToClipboard
   PPSlide.Shapes.Paste
  
   with PPSlide.Shapes(PPSlide.Shapes.Count)
   .left = 0
   .top = 200
   .width=720
   end with

   

    Next

Hope this helps,

Erich

Highlighted
Not applicable

Re: Errors in export Macro to ppt

Thanks for this. I have made a slight modification to your code and it works the want it !

I still have an issue with PPPres which does not seem to be recognized... So the document is not closed or saved

Any idea ?

TD

erich_shiino
Honored Contributor

Re: Errors in export Macro to ppt

Hi, You change need to call the methods of other objects:

From the SaveAs until the end, the code should be:


objPresentation.SaveAs "export QV.pptx"

objPresentation.Close

objPPT.Quit

'Clean up

Set PPSlide = Nothing


Set objPPT = Nothing

End sub

Regards,

Erich

Not applicable

Re: Errors in export Macro to ppt

THANKS !!!

Not applicable

Re: Errors in export Macro to ppt

Hello, I use the same script and export correctly from batch file, but when I add the bat in Publisher external task, fail when save the power point document.

Community Browser