Errors in export Macro to ppt

[#1]

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

[m_woolf]

Try replacing ActiveDocument with objPresentation

[erichshiino]

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

[#4]

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

[#5]

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

[erichshiino]

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

[#7]

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

[erichshiino · Accepted solution]

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]

THANKS !!!

[#10]

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.