9 Replies Latest reply: Apr 14, 2015 11:08 AM by Guillermo Ballester RSS

    Errors in export Macro to ppt

      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

       

       

       

        • Re: Errors in export Macro to ppt
          m w

          Try replacing ActiveDocument with objPresentation

          • Re: Errors in export Macro to ppt
            Erich Shiino

            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

              • Re: Errors in export Macro to ppt

                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


                  • Re: Errors in export Macro to ppt
                    Erich Shiino

                    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