22 Replies Latest reply: Mar 17, 2015 3:34 AM by Ashar Khalid RSS

    Export multiple objects to a single powerpoint slide

    Bharath Bhushan Kurly

      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
          Erich Shino

          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

           

           

              Next

           

          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
              Bharath Bhushan Kurly

              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
                  Erich Shino

                  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
                      Suresh Baabu

                      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
                        Thomas Duroyon

                        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
                      akumar.c

                      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
                        Amit Kakkad

                        Thanks Erich. Excellent example QVW.

                         

                        Regards,

                         

                        Amit

                        • Re: Export multiple objects to a single powerpoint slide
                          Amit Kakkad

                          Thanks Erich. Excellent example QVW.

                           

                          Regards,

                           

                          Amit

                          • Re: Export multiple objects to a single powerpoint slide
                            vijeta sharma

                            This macro exporting my chart objects as images into ppt

                            • Re: Export multiple objects to a single powerpoint slide
                              Ashar Khalid

                              Hi Eric,

                              Thanks for the helpful snippet.

                              Was wondering how can we copy/paste objects from different sheets ona  different slides.

                              I tried the following but it is copying the objects from sheet(SH05) only, though sheet (SH04) get activated but the code is not coying the objects from sheet (SH04). The clipboard have still object from sheet (SH05).

                              Again thanks in advance:

                              Sub ExportPPT
                              Set objPPT = CreateObject("PowerPoint.Application")
                              objPPT.Visible = True
                              Set objPresentation = objPPT.Presentations.Add
                              Set PPSlide = objPresentation.Slides.Add(1,11)

                              set up = ActiveDocument.GetApplication.GetUserPreferences
                              up.ChartUseCapNBorder = True
                              ActiveDocument.GetApplication.SetUserPreferences up

                               


                              with objPresentation.Slides(1).Shapes(1)
                              .TextFrame.TextRange = "Supervisory Controls"

                               


                              '.Left = 100
                              '.top = 100
                              '.width=75
                              '.height=25
                              end with
                              ActiveDocument.Sheets("SH05").Activate
                              ActiveDocument.GetSheetObject("CH23").CopyBitmapToClipboard

                               

                              with objPresentation.Slides(1).Shapes.PasteSpecial(PasteHTML)
                              .Left = 100
                              .top = 125
                              .width=400
                              .height=150
                              end with
                              ActiveDocument.GetSheetObject("CH25").CopyBitmapToClipboard
                              with objPresentation.Slides(1).Shapes.Paste
                              .Left = 100
                              .top = 300
                              .width=100
                              .height=125
                              end with
                              ActiveDocument.GetSheetObject("CH26").CopyBitmapToClipboard
                              with objPresentation.Slides(1).Shapes.Paste
                              .Left = 400
                              .top = 300
                              .width=100
                              .height=125
                              end with

                               

                              Set PPSlide = objPresentation.Slides.Add(2,11)
                              with objPresentation.Slides(2).Shapes(1)
                              .TextFrame.TextRange = "Resource Forecasting"
                              end with

                               


                              ActiveDocument.ActivateSheetByID "SH04"
                              'ActiveDocument.Sheets("SH04").Activate
                              ActiveDocument.GetSheetObject("CH22").CopyBitmapToClipboard
                              objPresentation.Slides(2).select
                              with objPresentation.Slides(2).Shapes.Paste
                              .Left = 100
                              .top = 125
                              .width=400
                              .height=150
                              end with

                               

                              ActiveDocument.GetSheetObject("CH28").CopyBitmapToClipboard

                               

                              with objPresentation.Slides(2).Shapes.Paste
                              .Left = 100
                              .top = 300
                              .width=100
                              .height=125
                              end with

                               

                              Set PPSlide = Nothing

                               

                              Set PPPres = Nothing

                               

                              End sub

                            • Re: Export multiple objects to a single powerpoint slide
                              alec1982

                              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
                                  LauraS056

                                  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
                                  Juan Prado

                                  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

                                  Next

                                   

                                  'Clean up

                                  Set PPSlide = Nothing

                                  Set PPPres = Nothing

                                  Set PPApp = Nothing

                                   

                                   

                                  End Sub 

                                    • Re: Export multiple objects to a single powerpoint slide
                                      LauraS056

                                      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
                                          Juan Prado

                                          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
                                              LauraS056

                                              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
                                                Charen R

                                                Hi,

                                                 

                                                Is there a possibility to export only selective charts from a sheet to PPT.

                                                And this has to be dynamic, like every time when i need copy only 2/3 charts from a sheet having more than 5 charts. Please help out. Thanks for the help,

                                                  • Re: Export multiple objects to a single powerpoint slide
                                                    Ashar Khalid

                                                    Hi,

                                                    Try addressing the objects by its names like CH23, CH24...

                                                     

                                                     

                                                    Sub ExportPPT
                                                    Set objPPT = CreateObject("PowerPoint.Application")
                                                    objPPT.Visible = True
                                                    Set objPresentation = objPPT.Presentations.Add
                                                    Set PPSlide = objPresentation.Slides.Add(1,11)

                                                    set up = ActiveDocument.GetApplication.GetUserPreferences
                                                    up.ChartUseCapNBorder = True
                                                    ActiveDocument.GetApplication.SetUserPreferences up

                                                     


                                                    with objPresentation.Slides(1).Shapes(1)
                                                    .TextFrame.TextRange = "Supervisory Controls"

                                                     


                                                    '.Left = 100
                                                    '.top = 100
                                                    '.width=75
                                                    '.height=25
                                                    end with
                                                    ActiveDocument.Sheets("SH05").Activate
                                                    ActiveDocument.GetSheetObject("CH23").CopyBitmapToClipboard

                                                     

                                                    with objPresentation.Slides(1).Shapes.PasteSpecial(PasteHTML)
                                                    .Left = 100
                                                    .top = 125
                                                    .width=400
                                                    .height=150
                                                    end with
                                                    ActiveDocument.GetSheetObject("CH25").CopyBitmapToClipboard
                                                    with objPresentation.Slides(1).Shapes.Paste
                                                    .Left = 100
                                                    .top = 300
                                                    .width=100
                                                    .height=125
                                                    end with
                                                    ActiveDocument.GetSheetObject("CH26").CopyBitmapToClipboard
                                                    with objPresentation.Slides(1).Shapes.Paste
                                                    .Left = 400
                                                    .top = 300
                                                    .width=100
                                                    .height=125
                                                    end with

                                                     

                                                    Set PPSlide = objPresentation.Slides.Add(2,11)
                                                    with objPresentation.Slides(2).Shapes(1)
                                                    .TextFrame.TextRange = "Resource Forecasting"
                                                    end with

                                                     


                                                    ActiveDocument.ActivateSheetByID "SH04"
                                                    'ActiveDocument.Sheets("SH04").Activate
                                                    ActiveDocument.GetSheetObject("CH22").CopyBitmapToClipboard
                                                    objPresentation.Slides(2).select
                                                    with objPresentation.Slides(2).Shapes.Paste
                                                    .Left = 100
                                                    .top = 125
                                                    .width=400
                                                    .height=150
                                                    end with

                                                     

                                                    ActiveDocument.GetSheetObject("CH28").CopyBitmapToClipboard

                                                     

                                                    with objPresentation.Slides(2).Shapes.Paste
                                                    .Left = 100
                                                    .top = 300
                                                    .width=100
                                                    .height=125
                                                    end with

                                                     

                                                    Set PPSlide = Nothing

                                                     

                                                    Set PPPres = Nothing

                                                     

                                                    End sub