topic Re: Unable to export objects from various sheets to a powerpoint application in QlikView

Unable to export objects from various sheets to a powerpoint application

snehamahaveer — Wed, 25 Nov 2020 16:16:04 GMT

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!!!

Re: Unable to export objects from various sheets to a powerpoint application

Clever_Anjos — Wed, 06 Apr 2016 15:02:36 GMT

You have to make the sheet as active before CopyBitmapToClipboard

Re: Unable to export objects from various sheets to a powerpoint application

marcus_sommer — Wed, 06 Apr 2016 15:03:48 GMT

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

Re: Unable to export objects from various sheets to a powerpoint application

snehamahaveer — Wed, 06 Apr 2016 15:51:32 GMT

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?

Re: Unable to export objects from various sheets to a powerpoint application

snehamahaveer — Wed, 06 Apr 2016 15:52:21 GMT

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.?

Re: Unable to export objects from various sheets to a powerpoint application

Tue, 12 Apr 2016 08:53:21 GMT

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

Re: Unable to export objects from various sheets to a powerpoint application

snehamahaveer — Mon, 18 Apr 2016 15:00:24 GMT

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.

Re: Unable to export objects from various sheets to a powerpoint application

Fri, 22 Apr 2016 13:01:31 GMT

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

Re: Unable to export objects from various sheets to a powerpoint application

snehamahaveer — Thu, 28 Apr 2016 07:47:10 GMT

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