Unable to export objects from various sheets to a powerpoint application

[snehamahaveer]

Dear All,

I have been working on this macro which exports objects from 10 different sheets to specified positions in a powerpoint application.

My macro works well when all charts are in the same sheet. When I copy objects from different sheets the export ends in an error. It is highly unlikely that the macro is wrong however as I am not an expert I require your help in solving this issue. I have tried the activate sheet by variable method ( which works perfectly when I export items to Excel but fails when exporting to powerpoint presentation).

My Macro is given below. If it relates to some specific user settings please let me know. I am desperate to get it working asap as my deadline is near.

sub ppt

pos = activedocument.evaluate("=getactivesheetid()")
set v = activedocument.Getvariable("vShowSheet")
v.SetContent 1,TRUE

Set objPPT = CreateObject("PowerPoint.Application") 
objPPT.Visible = True 
'Set objPresentation = objPPT.Presentations.open("C:\users\mahaveers\Desktop\strategic development\KAM Template.pptx")

'objPresentation.Sheets("SH12").Activate
Set PPSlide = objPresentation.Slides(3)
ActiveDocument.GetSheetObject("CH52").CopyBitmapToClipboard
PPSlide.Shapes.PasteSpecial(PasteHTML)
PPSlide.Shapes(PPSlide.Shapes.Count).Top = 75 'This sets the top location of the image 
PPSlide.Shapes(PPSlide.Shapes.Count).Left =50 'This sets the left location 
PPSlide.Shapes(PPSlide.Shapes.Count).Width = 100 
PPSlide.Shapes(PPSlide.Shapes.Count).Height = 150

Set PPSlide = objPresentation.Slides(4)
ActiveDocument.GetSheetObject("CH53").CopyBitmapToClipboard
PPSlide.Shapes.PasteSpecial(PasteHTML)
PPSlide.Shapes(PPSlide.Shapes.Count).Top = 225 'This sets the top location of the image 
PPSlide.Shapes(PPSlide.Shapes.Count).Left = 50 'This sets the left location 
PPSlide.Shapes(PPSlide.Shapes.Count).Width = 100 
PPSlide.Shapes(PPSlide.Shapes.Count).Height = 150

Set PPSlide = objPresentation.Slides(5)
ActiveDocument.GetSheetObject("CH63").CopyBitmapToClipboard
PPSlide.Shapes.PasteSpecial(PasteHTML)
PPSlide.Shapes(PPSlide.Shapes.Count).Top = 75 'This sets the top location of the image 
PPSlide.Shapes(PPSlide.Shapes.Count).Left =50 'This sets the left location 
PPSlide.Shapes(PPSlide.Shapes.Count).Width = 100 
PPSlide.Shapes(PPSlide.Shapes.Count).Height = 150

Set PPSlide = objPresentation.Slides(6)
ActiveDocument.GetSheetObject("CH64").CopyBitmapToClipboard
PPSlide.Shapes.PasteSpecial(PasteHTML)
PPSlide.Shapes(PPSlide.Shapes.Count).Top = 225 'This sets the top location of the image 
PPSlide.Shapes(PPSlide.Shapes.Count).Left = 50 'This sets the left location 
PPSlide.Shapes(PPSlide.Shapes.Count).Width = 100 
PPSlide.Shapes(PPSlide.Shapes.Count).Height = 150

Set PPSlide = Nothing
Set PPPres = Nothing
Set PPApp = Nothing

set v = activedocument.Getvariable("vShowAll")
v.SetContent 0,TRUE
activedocument.sheets(pos).activate

msgbox "PPT Extract Successful",64,"Complete"

End sub

All help much appreciated!!!

[Clever_Anjos]

You have to make the sheet as active before CopyBitmapToClipboard

[marcus_sommer]

It sounds that there are activate-statements like: objPresentation.Sheets("SH12").Activate missing if it's not worked by multiplen sheets. Quite often you need to activate each sheet each time if you want to access objects on them.

- Marcus

[snehamahaveer]

Hi Marcus,

I did use activate sheet syntax, but it still did not work.

objPresentation.Sheets("SH12").Activate

Using vShowSheet to activate all sheets did not work as well.

Is there any other way?

[snehamahaveer]

Hi Clever,

I did use activate sheet syntax, but it still did not work.

objPresentation.Sheets("SH12").Activate

Using vShowSheet to activate all sheets did not work as well.

What am I doing wrong.?

[#6]

Dear Sneha,

the following Macro will export all graphs (objects that start with CH) in your application to powerpoint:

Sub ExportPPT 
Set PPApp = CreateObject("Powerpoint.Application")

MsgBox("Export to Powerpoint is starting")

PPApp.Visible = True ' Create a presentation

Set PPPres = PPApp.Presentations.Add

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.Sheets(j).Activate

set s=ActiveDocument.ActiveSheet

charts=s.GetSheetObjects


SlideCount = PPPres.Slides.Count
Set PPSlide = PPPres.Slides.Add(SlideCount + 1, 11)
'Set PPSlide = PPPres.Slides.Add(ppres.slides.Count,11)


for i=lbound(charts) to ubound(charts)

if(UCASE(mid(charts(i).getobjectid,10,2)))="CH" then 


ActiveDocument.GetSheetObject(charts(i).getobjectid).CopyBitmapToClipboard

PPSlide.Shapes.Paste

PPSlide.Shapes(PPSlide.Shapes.Count).Top = 117 'This sets the top location of the image 
PPSlide.Shapes(PPSlide.Shapes.Count).Left = 91 'This sets the left location


end if

next

next ''for sheets next move

Set PPSlide = Nothing
Set PPPres = Nothing


MSGBOX "You report was successfully exported to PowerPoint.",64,"KRALLMANN AG"
end sub

You can also modify the above statements to include only certain graphs (then you would just change the if statement to

if(UCASE(mid(charts(i).getobjectid,10,4)))="CHxx" then 

Please let me know if you need further instructions.

Best regards,

Maximilian

[snehamahaveer]

Hi Maximilian,

Thanks for this. Interestingly your script seems to copy/paste all charts to a powerpoint presentation.

However, I now must customise it. I hope you will be able to help as I am a novice with scripting.

I want it to incorporate the following.

1. Open a template and populate with charts

2. Charts are to be placed in specific slides at specified position

I have difficulty activating sheets(other than the current tab where the export button lies) and copying charts from qlikview over to Powerpoint.

I have attached a test version of my script/qvw.

Thank you very much in advance.

[#8]

Hi Sneha,

please excuse my delayed response.

Which objects would you like to have on which slide?

Please take a look at the modified version of your test file below - I added 2 charts of each sheet to a different powerpoint slide.

Let me know if you need any further assistance!

Best,

Max

[snehamahaveer]

Thanks Maxmilian.

Could you please guide me in modifying this script to suit my export requirements?

1. It should open a template file

2. Extract images of charts from various sheets

3. Paste pictures on specific slides in the template

If you could help me with the logic for a couple of slides, I shall write the script for all images.

Many thanks in advance,

Sneha