topic Re: Errors in export Macro to ppt in QlikView

Errors in export Macro to ppt

Tue, 07 Aug 2012 09:46:18 GMT

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

Re: Errors in export Macro to ppt

m_woolf — Tue, 07 Aug 2012 10:27:55 GMT

Try replacing ActiveDocument with objPresentation

Re: Errors in export Macro to ppt

erichshiino — Tue, 07 Aug 2012 17:08:28 GMT

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

ActiveDocument.GetApplication.WaitforIdle

Hope it helps,

Erich

Re: Errors in export Macro to ppt

Wed, 08 Aug 2012 00:25:36 GMT

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

Re: Errors in export Macro to ppt

Wed, 08 Aug 2012 00:39:36 GMT

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

Re: Errors in export Macro to ppt

erichshiino — Wed, 08 Aug 2012 04:14:09 GMT

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

Hope this helps,

Erich

Re: Errors in export Macro to ppt

Wed, 08 Aug 2012 04:47:25 GMT

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 ?

Re: Errors in export Macro to ppt

erichshiino — Wed, 08 Aug 2012 05:04:03 GMT

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

Re: Errors in export Macro to ppt

Wed, 08 Aug 2012 07:26:27 GMT

THANKS !!!

Re: Errors in export Macro to ppt

Tue, 14 Apr 2015 15:08:03 GMT

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.