topic Re: Export multiple objects to a single powerpoint slide in QlikView

Export multiple objects to a single powerpoint slide

Wed, 23 May 2012 21:14:55 GMT

Can anyone provide sample code for a macro to export multiple objects into a single powerpoint slide.

Looks like there are no previous discussions regarding this topic.

All the discussions only tell about pasting a single object per slide.

I would really appreciate it, if someone sheds light on this topic.

Thanks,

Bharath

Re: Export multiple objects to a single powerpoint slide

erichshiino — Thu, 24 May 2012 02:00:32 GMT

Hi,

I adapted DV's code from this post:

http://community.qlik.com/message/196413#196413

Sub ExportPPT

ActiveDocument.ActiveSheet.FitZoomToWindow

Set objPPT = CreateObject("PowerPoint.Application")

objPPT.Visible = True

Set objPresentation = objPPT.Presentations.Add

For i = 0 To ActiveDocument.NoOfSheets - 1

' Set MySheet = ActiveDocument.GetSheet(i)

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

' ActiveDocument.ActiveSheet.CopyBitmapToClipboard

ActiveDocument.GetSheetObject("CH03").CopyBitmapToClipboard

PPSlide.Shapes.Paste

ActiveDocument.GetSheetObject("CH04").CopyBitmapToClipboard

PPSlide.Shapes.Paste

Set PPSlide = Nothing

Set PPPres = Nothing

End sub

This will export charts CH03 and CH04

Hope this helps,

Erich

Re: Export multiple objects to a single powerpoint slide

Thu, 24 May 2012 18:44:07 GMT

Thanks for the prompt reply Erich.

The script you posted allows me to copy objects only from my Active sheet on to a slide.

But, I want to copy objects from other sheets as well.

To do this I have tried to manually set the active sheets but in vain.

Sub ExportPPT

Set objPPT = CreateObject("PowerPoint.Application")
objPPT.Visible = True
Set objPresentation = objPPT.Presentations.Add(True)

Set Sheet1= ActiveDocument.Sheets("SH04")
Set PPSlide = objPresentation.Slides.Add(objPresentation.Slides.Count + 1,11)

PPSlide.Shapes(1).TextFrame.TextRange.Text = "Productivity Tracking"
Sheet1.GetSheetObject("CH34").CopyBitmapToClipboard
PPSlide.Shapes.Paste
Sheet1.GetSheetObject("CH05").CopyBitmapToClipboard
PPSlide.Shapes.Paste
Sheet1.GetSheetObject("CH35").CopyBitmapToClipboard
PPSlide.Shapes.Paste

Set Sheet2= ActiveDocument.Sheets("SH10")
Set PPSlide = objPresentation.Slides.Add(objPresentation.Slides.Count + 1,11)
PPSlide.Shapes(1).TextFrame.TextRange.Text = "Hiring"
Sheet2.GetSheetObject("CH37").CopyBitmapToClipboard
PPSlide.Shapes.Paste
Sheet2.GetSheetObject("CH38").CopyBitmapToClipboard
PPSlide.Shapes.Paste

Set PPSlide = Nothing
Set PPPres = Nothing

End sub

This is the error I receive.

Object doesn't support this property or method: 'Sheet1.GetSheetObject'

How can I actually select what objects I want from different sheets?

I would also love to know, how I can control the positions of the objects on the slide?

Thanks,

BharathBK

Re: Export multiple objects to a single powerpoint slide

erichshiino — Thu, 24 May 2012 19:00:34 GMT

Are the objects in those sheets minimized? When I tried before, it didn't work.

You can try to activate the sheet before copying the object:

ActiveDocument.ActivateSheetByID "SH01"

It may also be necessary to restore the object (if it is minimized) with something like this:

ActiveDocument.GetSheetObject("CH03").Activate

I did a quick search, trying to control how to position the object when it is pasted, but I couldn't find a way. It would depend on the syntax provided by powerpoint itself...

Hope this helps,

Erich

Re: Export multiple objects to a single powerpoint slide

sona_sa — Fri, 20 Jul 2012 10:30:33 GMT

Hi Erich,

Do you have any macro for

Export multiple Sheets as a image to a single powerpoint slides.

Suppose I have an QV Application and I want to send all the sheets as a image to multiple Slide in a Single PPT.

Please help me on this.

Regards,

AKumar

Re: Export multiple objects to a single powerpoint slide

sureshbaabu — Thu, 09 Aug 2012 11:50:35 GMT

Hello Erich,

I'm Not sure if you have fixed the issue by now.

You can use the follwing the positon the objects when you place them on PPT. Set values for ( .Left, .top, .width, .height)

ActiveDocument.GetSheetObject("P2T2").CopyBitmapToClipboard
with PPSlide.Shapes.Paste
.Left = 150
.top = 350
.width=50
.height=90
end with

Hope it helps you!!!

I need your help in fixing my issue with PPT. I have 2 objects, one on 1st sheet and 2nd on the next sheet. I was able to export the object on 1st sheet to the ppt and could not export the 2nd one, Beacuse it was not the Active sheet. I tried You method above but I could not get that. Could you please help me fixing this issue?

Re: Export multiple objects to a single powerpoint slide

Thu, 09 Aug 2012 12:27:23 GMT

Hi,

A 720 width will give you objects of the maximum width of the slide, and as it is grow the bitmap proportionally, you end up with a clean mage or table...

Cheers

Re: Export multiple objects to a single powerpoint slide

Thu, 25 Apr 2013 08:24:34 GMT

Thanks Erich. Excellent example QVW.

Regards,

Amit

Re: Export multiple objects to a single powerpoint slide

Thu, 25 Apr 2013 08:25:06 GMT

Thanks Erich. Excellent example QVW.

Regards,

Amit

Re: Export multiple objects to a single powerpoint slide

vijetas42 — Mon, 24 Mar 2014 06:41:47 GMT

This macro exporting my chart objects as images into ppt

Re: Export multiple objects to a single powerpoint slide

alec1982 — Mon, 18 Aug 2014 15:04:37 GMT

Thank you for the sample. One little issue is that all objects are coming on top of each other.. Can I assign positions? Also, Is there anyway I can include all these objects into a single image instead of multiple images?

Best,

Alec

Re: Export multiple objects to a single powerpoint slide

Wed, 20 Aug 2014 12:34:47 GMT

Hello alec,

To choose the position of the objects, you can use :

SET Shape = PPSlide.Shapes(PPSlide.Shapes.count) ' Select the last picture which was pasted into the slide

With Shape

.LockAspectRatio = msoTRUE 'Allow size modification

.PictureFormat.CropTop = 10 'crop

.PictureFormat.CropBottom = 10

'.PictureFormat.CropRight = 10

.left=01

.top= 77

.height= 180

.width=370

END With

I'm not sure you can export the objects in only one picture.

Re: Export multiple objects to a single powerpoint slide

jumiprado — Fri, 10 Oct 2014 17:55:49 GMT

hey guys! how are you.

I want to create a powerpoint from some differents qlikview objects. I could creat the macro wich create the ppt and paste the objects but i have the following questions:

How can i create the ppt with a specific name?

How can i put one object per slide ?

How can i put two specific objects in a specific slide?

here i paste the macro. THANKS A LOT!!!

Sub ppt

'An array with the objects you want to export to powerpoint

Dim myArray(4)

myArray(0) = "CH09"

myArray(1) = "TX22"

myArray(2) = "TX23"

myArray(3) = "TX31"

'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.potx")

'For each object in the array create a new slide and paste it.

For each item in myArray

' Set MySheet = ActiveDocument.GetSheet(i)

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

' ActiveDocument.ActiveSheet.CopyBitmapToClipboard

ActiveDocument.GetSheetObject("CH09").CopyBitmapToClipboard

ActiveDocument.GetSheetObject("TX22").CopyBitmapToClipboard

with PPSlide.Shapes.Paste

.Left = 90

.top = 50

.width=50

.height=90

end with

ActiveDocument.GetSheetObject("TX31").CopyBitmapToClipboard

with PPSlide.Shapes.Paste

.Left = 150

.top = 350

.width=20

.height=20

end with

ActiveDocument.GetSheetObject("TX23").CopyBitmapToClipboard

with PPSlide.Shapes.Paste

.Left = 150

.top = 350

.width=50

.height=90

end with

'Clean up

Set PPSlide = Nothing

Set PPPres = Nothing

Set PPApp = Nothing

End Sub

Re: Export multiple objects to a single powerpoint slide

Mon, 13 Oct 2014 08:02:24 GMT

Hi Juan,

It's been a while I haven't used macros but I will try to help you with what I remember.

1) To give a name to your ppt, you can save it at the end:

objPresentation.SaveAs "PathWhereYouwantToSaveYourPPT\YourPPTName.pptx"

2) To put one object per slide, I'd say you first have to select the slide and then paste the object you want to paste in. (and do it as many times as you have objects)

For example :

Set PPTSlide = objPresentation.Slide(1) selects (or create if none exists) the first slide of your presentation.

3) Once you have selected the slide, you can paste your 2 objects.

Hope this helps

Have a good day

Laura

Re: Export multiple objects to a single powerpoint slide

jumiprado — Tue, 14 Oct 2014 15:27:13 GMT

Laura, thanks. I have problems with 2) and 3) because i create an array with the differents objects i want to export. i have this code:

Dim myArray(3)

myArray(0) = "CH09"

myArray(1) = "TX23"

myArray(2) = "TX31"

---------------------------------------------------

For each item in myArray

' Set MySheet = ActiveDocument.GetSheet(i)

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

' ActiveDocument.ActiveSheet.CopyBitmapToClipboard

ActiveDocument.GetSheetObject("CH09").CopyBitmapToClipboard

with PPSlide.Shapes.Paste

end with

*****************

But I would like to select the objects and then paste that on the ppt.

Thanks!

Re: Export multiple objects to a single powerpoint slide

Tue, 14 Oct 2014 15:38:12 GMT

Ok, are your QV objects on the same sheet ?

As far as I know, you cannot select all your objects first and then paste them because the command : PPSlide.Shapes.Paste only takes into account the last shape.

What about using a loop ?

Something like :

for i=1 to 3

Select object(i)

Select Slide(i)

Paste

end

Do you only have 3 objects to paste or is it just for the example?

Re: Export multiple objects to a single powerpoint slide

jumiprado — Tue, 14 Oct 2014 15:42:02 GMT

for now, yes.

Re: Export multiple objects to a single powerpoint slide

Tue, 14 Oct 2014 15:49:59 GMT

Is a loop possible for you ?

Do you need to paste 1 object per sheet ? Is there a way to calculate the numero of the sheet you want to apste each object on ?

Re: Export multiple objects to a single powerpoint slide

jumiprado — Tue, 14 Oct 2014 15:58:05 GMT

Me parece que podemos hablar en español jaja, necesito pegar un objeto en un slide especifico y en algunos casos pegar 2 objetos en otros slides en particular, la idea es crear un powerpoint con los objectos actualizados de manera automática.

Pensaba utilizar una sentencia IF en donde dependiendo el ID de Objeto, lo pegue en una Slide 5, por ejemplo. El problema es que no se como armar el codigo para que lo realice.

Saludos y gracias!!

LauraS056

Re: Export multiple objects to a single powerpoint slide

Tue, 14 Oct 2014 16:21:28 GMT

jaja, no soy española o sudamericana. Soy francesa. Entiendo un poco el español pero el ingles es mas facil para mi

If there is no really relationship between the object and the numero of the slide, why don't you want to simply write one block of code per object ?

Sth like :

'Object 1

Set PPSlide=objPresentation.Slides(1) 'select the slide

ActiveDocument.GetSheetObject(myArray(0)).CopyBitmapToClipboard 'Copy the object you want

PPSlide.Shapes.Paste 'Paste

Then you can configure the size and position..and do it for each object.

I am not sure a loop will be more efficient if it has to go through all the powerpoint to check some conditions (IF) to paste the object or not and so on...

What do you think ?