24 Replies Latest reply: Aug 13, 2016 2:04 PM by Grant Rudachyk

Macro to Copy & Paste table into Powerpoint using paste special

Grant Rudachyk Mar 25, 2012 1:24 PM

Hello,

I'm hoping someone has experience pasting tables into Powerpoint using paste special. I've had success using CopyToBitmapToClipboard using the following code:

ActiveDocument.GetSheetObject("CH418").CopyBitmapToClipboard

PPSlide.Shapes.Paste.Select

I would like to paste in an editable table instead of an image and I thought something like this code would work:

ActiveDocument.GetSheetObject("CH418").CopyTableToClipboard True

PPSlide.Shapes.PasteSpecial(PasteHTML).Select

However, I get the following error:

Shapes.PasteSpecial : Invalid request. Clipboard is empty or contains data which may not be pasted here.

I confirmed that the Clipboard was not empty by manually pasting from it into Powerpoint. I also tried PasteDefault instead of PasteHTML.

The PasteSpecial works with CopyBitmapToClipboard. The example below worked so I think some form of PasteSpecial should work for straight tables:

ActiveDocument.GetSheetObject("CH418").CopyBitmapToClipboard

PPSlide.Shapes.PasteSpecial(PasteEnhancedMetafile).Select

If anyone has any insights, it would be greatly appreciated. I am using version 10.

thanx

Grant

Macro to Copy & Paste table into Powerpoint using paste special

Arun Goel Mar 26, 2012 5:35 AM (in response to Grant Rudachyk)

Hi,
Try This:

Sub CT
ActiveDocument.GetSheetObject("CH01").CopyTableToClipboard true
End Sub

Regards,
Arun Goel
Like Show 0 Likes (0)

Actions
- Macro to Copy & Paste table into Powerpoint using paste special
  
  Grant Rudachyk Mar 26, 2012 6:29 PM (in response to Arun Goel)
  
  Arun,
  Thanx for the reply. I don't have a problem copying the Table. My problem is pasting it into the Powerpoint Slide. It may be that I need to add a Table first and then paste into each cell. I hope I don't have to do that.
  
  Grant
  
  Like Show 2 Likes (2)
  
  Actions
Re: Macro to Copy & Paste table into Powerpoint using paste special

Jeremiah Kurpat Jan 29, 2014 2:37 PM (in response to Grant Rudachyk)

Hi Grant,
I recently ran into this and stumbled upon pasting while keeping source formatting. However I think this will only work for PowerPoint 2010 or higher since the command was implemented in this version. I used this code for a macro to copy a table to PowerPoint:

sub ppt
set objPPT = CreateObject("PowerPoint.Application")
objPPT.Visible = True
Set objPresentation = objPPT.Presentations.Add
ActiveDocument.Sheets("SH01").Activate
Set obj = ActiveDocument.GetSheetObject("CH01")
Set PPSlide = objPresentation.Slides.Add(1,obj.GetObjectType)
obj.CopyTableToClipboard true
With PPSlide
objPPT.CommandBars.ExecuteMso ("PasteSourceFormatting")
End With
End Sub

Saw this post while I was searching, and thought that this may be useful to you if you haven't found it yet.

Hope this helps!
Like Show 1 Like (1)

Actions
- Re: Macro to Copy & Paste table into Powerpoint using paste special
  
  sachin godhania Feb 22, 2016 5:34 AM (in response to Jeremiah Kurpat)
  
  HI Jeremiah Kurpat,
  
  I have one prob here. I have looped all the chart and copied to ppt.
  If its chart paste as bitmap and if its straigt/pivot paste as table as the above code that u are using.
  However , the prob is ,it copies all the tables to first slide and not the specific slide.
  Please let me know if you had encountered the same issue.
  
  Thanks,
  Sachin
  
  Like Show 0 Likes (0)
  
  Actions
  - Re: Macro to Copy & Paste table into Powerpoint using paste special
    
    Sathish G Feb 22, 2016 5:54 AM (in response to sachin godhania)
    
    Hi Sachin,
    All the charts are copied into same page in PPT?
    
    That means you have to pass add sheets command in the loop or before copy command.
    
    -Sathish
    
    Like Show 1 Like (1)
    
    Actions
  - Re: Macro to Copy & Paste table into Powerpoint using paste special
    
    Alex Millan Feb 22, 2016 6:05 AM (in response to sachin godhania)
    
    Hi,
    
    With this instruction you can set the diapo where you want your objects to be pasted:
    
    Set PPSlide = objPresentation.Slides(2) 'Change 2 for the number of the diapo you prefer
    
    HTH
    
    Regards.
    
    Like Show 1 Like (1)
    
    Actions
    - Re: Macro to Copy & Paste table into Powerpoint using paste special
      
      sachin godhania Feb 22, 2016 9:34 PM (in response to Alex Millan)
      
      Hi Alex,
      
      Thanks for ur reply.
      I am using code (orginal) as below ,
      
      sub ppt
      
      set objPPT = CreateObject("PowerPoint.Application")
      
      objPPT.Visible = True
      
      Set objPresentation = objPPT.Presentations.Add
      
      ActiveDocument.Sheets("SH01").Activate
      
      Set obj = ActiveDocument.GetSheetObject("CH03")
      
      Set PPSlide = objPresentation.Slides.Add(1,obj.GetObjectType)
      
      obj.CopyTableToClipboard true
      
      With PPSlide
      
      objPPT.CommandBars.ExecuteMso ("PasteSourceFormatting")
      
      End With
      
      Set obj = ActiveDocument.GetSheetObject("CH04")
      
      Set PPSlide = objPresentation.Slides.Add(2,obj.GetObjectType)
      
      obj.CopyTableToClipboard true
      
      With PPSlide
      
      objPPT.CommandBars.ExecuteMso ("PasteSourceFormatting")
      
      End With
      
      End Sub
      
      I tried your code but in vain . It always goes in the second slide. I am sure I am missing something to add above.
      
      Please let me know.
      
      Regards,
      Sachin
      
      Like Show 0 Likes (0)
      
      Actions
      - Re: Macro to Copy & Paste table into Powerpoint using paste special
        
        Alex Millan Feb 23, 2016 3:31 AM (in response to sachin godhania)
        
        Yes sachin,
        it goes always to the second slide because the instruction marks the second slide.
        
        You just have to adjust the instruction to point to the desired diapo. As example:
        
        Set PPSlide = objPresentation.Slides(2) '-->To point to the second slide
        Set PPSlide = objPresentation.Slides(4) '-->To point to the forth slide
        
        HTH
        
        Regards
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: Macro to Copy & Paste table into Powerpoint using paste special
        
        sachin godhania Feb 23, 2016 3:37 AM (in response to Alex Millan)
        
        Hi Alex,
        
        Sorry for typo, i mean to say the table always copies to slide 1 irrespective to the parameter you provide in obj.slides(eg 2).
        
        The logic absolutely works fine with "CopyBitmapToClipboard" but not CopyTableToClipboard
        
        Regards,
        Sachin
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: Macro to Copy & Paste table into Powerpoint using paste special
        
        Jeremiah Kurpat Feb 24, 2016 3:05 PM (in response to sachin godhania)
        
        Hi Sachin!
        
        You should try this code:
        
        sub ppt
        
        set objPPT = CreateObject("PowerPoint.Application")
        objPPT.Visible = True
        Set objPresentation = objPPT.Presentations.Add
        
        For j=1 To 2
        Set PPSlide = objPresentation.Slides.Add(1,12)
        next
        
        ActiveDocument.Sheets("SH01").Activate
        
        Set obj = ActiveDocument.GetSheetObject("CH03")
        objPresentation.Slides(1).Select
        obj.CopyTableToClipboard true
        objPPT.CommandBars.ExecuteMso ("PasteSourceFormatting")
        
        Set obj = ActiveDocument.GetSheetObject("CH04")
        objPresentation.Slides(2).Select
        obj.CopyTableToClipboard true
        objPPT.CommandBars.ExecuteMso ("PasteSourceFormatting")
        
        End sub
        
        I found out you don't need the With PPSlide to do Paste Source Formatting since it will paste it in you active slide. So you want to choose the slide you want to paste it on with the statement:
        
        objPresentation.Slides(1).Select
        
        Hope this helps!!
        
        Like Show 0 Likes (0)
        
        Actions
Re: Macro to Copy & Paste table into Powerpoint using paste special

Sathish G Oct 3, 2014 8:27 AM (in response to Grant Rudachyk)

Hi Grant,

I am looking for the same. Did you got the answer?

If you know, Please share to me.

-Sathish
Like Show 0 Likes (0)

Actions
- Re: Macro to Copy & Paste table into Powerpoint using paste special
  
  sachin godhania Feb 22, 2016 9:35 PM (in response to Sathish G )
  
  Hi Satish,
  
  I am looping to all the objects and pasting them in the predefine slidenos. I am able to do it for image successfully but not for table.
  
  Regards,
  Sachin
  
  Like Show 0 Likes (0)
  
  Actions
  - Re: Macro to Copy & Paste table into Powerpoint using paste special
    
    Sathish G Feb 23, 2016 5:21 AM (in response to sachin godhania)
    
    Hi,
    
    While exporting the qlikview object into PPT, You have to maximize the qlikview object.
    I don't know, you already did the same or not.. just to inform you to check these option.
    
    -Sathish
    
    Like Show 0 Likes (0)
    
    Actions
    - Re: Macro to Copy & Paste table into Powerpoint using paste special
      
      Alex Millan Feb 23, 2016 6:14 AM (in response to Sathish G )
      
      Hi Sathish,
      
      From my experience, there's no need to maximize the object, at least if it's not minimized in the app. If minimized, I haven't tested it. Did you notice any problem while not minimized?
      
      Regards
      
      Like Show 0 Likes (0)
      
      Actions
      - Re: Macro to Copy & Paste table into Powerpoint using paste special
        
        Sathish G Feb 23, 2016 6:32 AM (in response to Alex Millan)
        
        Hi Alex,
        
        I have faced the issue for the minimized objects (But long time back). For minimized objects, we have to do maximize and export into ppt.
        
        If its not minimized then same time you are keeping the multiple qlikview objects?
        
        -Sathish
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: Macro to Copy & Paste table into Powerpoint using paste special
        
        Alex Millan Feb 23, 2016 7:12 AM (in response to Sathish G )
        
        Thanks Sathish,
        
        I've just tested,
        indeed there's an issue while exporting a minimized object by using the bitmap option [.CopyBitmapToClipboard()],
        however, the export works properly when using the table option [.CopyTableToClipboard(true)] on a minimized object.
        
        Thanks for sharing, I didn't know this weird behaviour!
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: Macro to Copy & Paste table into Powerpoint using paste special
        
        sachin godhania Feb 24, 2016 1:16 AM (in response to Alex Millan)
        
        Hi Alex,
        
        Were you able to replicate the issue ? or its some thing wrong in my macro only.
        
        Regards,
        Sachin
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: Macro to Copy & Paste table into Powerpoint using paste special
        
        Alex Millan Feb 24, 2016 4:01 AM (in response to sachin godhania)
        
        Hi sachin,
        
        try the following code:
        
             SUB ppt
        
             Const ppLayoutText = 2
        
             Set objPPT = CreateObject("PowerPoint.Application")
             objPPT.Visible = True
        
             Set objPresentation = objPPT.Presentations.Add
        
             Set PPSlide = objPresentation.Slides.Add(1, ppLayoutText)
             ActiveDocument.GetSheetObject("CH03").CopyBitmapToClipboard
             PPSlide.Shapes.Paste
             Set PPSlide = Nothing
        
             Set PPSlide = objPresentation.Slides.Add(2, ppLayoutText)
             ActiveDocument.GetSheetObject("CH04").CopyBitmapToClipboard
             PPSlide.Shapes.Paste
             Set PPSlide = Nothing
        
             End Sub
        
        This works fine on my test
        
        Regards
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: Macro to Copy & Paste table into Powerpoint using paste special
        
        Alex Millan Feb 24, 2016 4:07 AM (in response to Alex Millan)
        
        Hi againg,
        
        The following code saves the issue with the minimized objects, just in case you need it. If the object is minimized then restore it before Copy & Paste and then minimize it.
        
             SUB ppt
        
             Const ppLayoutText = 2
             Dim v
        
             v = 0
        
             Set objPPT = CreateObject("PowerPoint.Application")
             objPPT.Visible = True
        
             Set objPresentation = objPPT.Presentations.Add
        
             Set PPSlide = objPresentation.Slides.Add(1, ppLayoutText)
             Set TargetObject = Activedocument.GetSheetObject("CH03")
             If TargetObject.IsMinimized Then
               v = 1
               TargetObject.Restore
             End If
             TargetObject.CopyBitmapToClipboard
             PPSlide.Shapes.Paste
             Set PPSlide = Nothing
             If v = 1 Then
               TargetObject.Minimize
               v = 0
             End If
             Set TargetObject = Nothing
        
             Set PPSlide = objPresentation.Slides.Add(2, ppLayoutText)
             Set TargetObject = Activedocument.GetSheetObject("CH04")
             If TargetObject.IsMinimized Then
               v = 1
               TargetObject.Restore
             End If
             TargetObject.CopyBitmapToClipboard
             PPSlide.Shapes.Paste
             Set PPSlide = Nothing
             If v = 1 Then
               TargetObject.Minimize
               v = 0
             End If
             Set TargetObject = Nothing
        
             End Sub
        
        Regards
        
        Like Show 1 Like (1)
        
        Actions
        
        Re: Macro to Copy & Paste table into Powerpoint using paste special
        
        sachin godhania Feb 24, 2016 4:11 AM (in response to Alex Millan)
        
        Hi Alex,
        
        Thank you very much for trying this out.
        
        However, as I mentioned earlier the issue is only when I use CopyTabletoClipboard , since I need to paste it as a table to PPT.
        
        Regards,
        Sachin
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: Macro to Copy & Paste table into Powerpoint using paste special
        
        Alex Millan Feb 26, 2016 6:17 AM (in response to sachin godhania)
        
        Hi Sachin,
        
        From my experience, you can not paste the table directly to an empty slide.
        
        If you choose the CopyTableToClipboard method you only paste the data, so if you don't have your table previously created in the slide, you can't paste it there.
        
        Sure there is a workaround to create the table from the vbscript code but I've never test it since my requirements have always been to paste it as image.
        
        Hope someone may help you on this topic. I'll be following the thread for me too, just in case this need would show up at any time.
        
        Good luck and regards
        
        Like Show 0 Likes (0)
        
        Actions
    - Re: Macro to Copy & Paste table into Powerpoint using paste special
      
      sachin godhania Feb 23, 2016 9:09 AM (in response to Sathish G )
      
      Hi satish,
      Yes i am doing that. Not sure what is the issue.
      
      Regards,
      Sachin
      
      Like Show 0 Likes (0)
      
      Actions
      - Re: Macro to Copy & Paste table into Powerpoint using paste special
        
        Sathish G Feb 24, 2016 6:04 AM (in response to sachin godhania)
        
        Hi Sachin,
        
        Could you please share your application with sample data.
        
        -Sathish
        
        Like Show 0 Likes (0)
        
        Actions
Re: Macro to Copy & Paste table into Powerpoint using paste special

Grant Rudachyk Aug 13, 2016 2:04 PM (in response to Grant Rudachyk)
Thanx to all that replied. based on what you provided and some additional research I put together a tool that others might find useful or at least a starting point for others in making simple PowerPoints from QlikView. I created a PowerPoint macro.

The macro basically builds the QlikView VB Script to copy QV objects to the clipboard and paste them into a PowerPoint presentation.
Basic Instructions:
1) add the number of blank slides required for the QV PowerPoint presentation (after the current blank slide)
2) add rectangles to those slides representing the size and position where the QV objects are to be pasted
3) add the appropriate QV object ID (e.g. CH01) to each of the rectangles
4) run the macro
Note: functionality is extended by using prefixes

I've attached a PowerPoint presentation to describe the basic functions and a demo file.

QVCG v01 Demo File.pptm 58.9 K

QlikView VB Script Generator for PowerPoint 07-27-2016.pptx 479.6 K
Like Show 0 Likes (0)

Actions

Go to original post