topic Re: Macro to Copy & Paste table into Powerpoint using paste special in QlikView

Macro to Copy & Paste table into Powerpoint using paste special

Sun, 25 Mar 2012 17:24:46 GMT

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

Mon, 26 Mar 2012 09:35:29 GMT

Hi,

Try This:

Sub CT

ActiveDocument.GetSheetObject("CH01").CopyTableToClipboard true

End Sub

Regards,

Arun Goel

Macro to Copy & Paste table into Powerpoint using paste special

Mon, 26 Mar 2012 22:29:37 GMT

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

Re: Macro to Copy & Paste table into Powerpoint using paste special

jerem1234 — Wed, 29 Jan 2014 19:37:22 GMT

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!

Re: Macro to Copy & Paste table into Powerpoint using paste special

sathishkumar_go — Fri, 03 Oct 2014 12:27:31 GMT

Hi Grant,

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

If you know, Please share to me.

-Sathish

Re: Macro to Copy & Paste table into Powerpoint using paste special

sachingodhania — Mon, 22 Feb 2016 10:34:49 GMT

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

Re: Macro to Copy & Paste table into Powerpoint using paste special

sathishkumar_go — Mon, 22 Feb 2016 10:54:28 GMT

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

Re: Macro to Copy & Paste table into Powerpoint using paste special

alex_millan — Mon, 22 Feb 2016 11:05:20 GMT

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.

Re: Macro to Copy & Paste table into Powerpoint using paste special

sachingodhania — Tue, 23 Feb 2016 02:34:18 GMT

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

Re: Macro to Copy & Paste table into Powerpoint using paste special

sachingodhania — Tue, 23 Feb 2016 02:35:45 GMT

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

Re: Macro to Copy & Paste table into Powerpoint using paste special

alex_millan — Tue, 23 Feb 2016 08:31:12 GMT

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

Re: Macro to Copy & Paste table into Powerpoint using paste special

sachingodhania — Tue, 23 Feb 2016 08:37:06 GMT

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

Re: Macro to Copy & Paste table into Powerpoint using paste special

sathishkumar_go — Tue, 23 Feb 2016 10:21:55 GMT

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

Re: Macro to Copy & Paste table into Powerpoint using paste special

alex_millan — Tue, 23 Feb 2016 11:14:49 GMT

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

Re: Macro to Copy & Paste table into Powerpoint using paste special

sathishkumar_go — Tue, 23 Feb 2016 11:32:09 GMT

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

Re: Macro to Copy & Paste table into Powerpoint using paste special

alex_millan — Tue, 23 Feb 2016 12:12:41 GMT

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!

Re: Macro to Copy & Paste table into Powerpoint using paste special

sachingodhania — Tue, 23 Feb 2016 14:09:28 GMT

Hi satish,

Yes i am doing that. Not sure what is the issue.

Regards,

Sachin

Re: Macro to Copy & Paste table into Powerpoint using paste special

sachingodhania — Wed, 24 Feb 2016 06:16:37 GMT

Hi Alex,

Were you able to replicate the issue ? or its some thing wrong in my macro only.

Regards,

Sachin

Re: Macro to Copy & Paste table into Powerpoint using paste special

alex_millan — Wed, 24 Feb 2016 09:01:33 GMT

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

Re: Macro to Copy & Paste table into Powerpoint using paste special

alex_millan — Wed, 24 Feb 2016 09:07:50 GMT

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