Copy and Paste Special - Copy Excel Data to PowerPoint

[nmoiseff]

Hello,

I need some help copying and pasting data from Excel to PowerPoint via VBA. I have a range of data that I want to copy from Excel and Paste this range to PowerPoint. The data range is essentially a table of data with formatting.

The problem that I am running into is that I want the data pasted into PowerPoint to be the same format that it was copied. I need to be able to make modifications to the data once it is in PowerPoint. I can get exactly what I want when I do this manually (select data range in Excel, copy it, select the powerpoint slide, then select Paste Special -> HTML). I cannot seem to do this with VBA though. So in short Im trying to Paste Special -> HTML in PowerPoint but it isnt working.

This is the main code that I have so far;

Set PPSlide = PPApp.ActivePresentation.Slides.Add(1, ppLayoutBlank)
Worksheets(SheetName).Range(RangeName1).Copy
PPSlide.Shapes.PasteSpecial (ppPasteHTML)

But I always get a runtime error when it gets to the ppPasteHTML part. I am running Office 2010 but I would also like to be able to run this macro on older versions of Office.

Any help is greatly appreciated.

Nick

 

[Trevor G]

Hi Nick,

If you want to run this on older versions first you could set the PowerPoint.Application to use Object.
If you have older versions, then in PowerPoint 2003 you can record macros to do the paste special paste as object.

Here is a link that may help as it also has a download option with a sample workbook.

http://chandoo.org/wp/2011/08/03/create-powerpoint-presentations-using-excel-vba/

 

[nmoiseff]

You posted code to copy and paste a chart area. I am still unable to copy and paste special html. I get an error in my VBA code when doing this. No luck using Office 2003 to record the macro either.

 

[Trevor G]

You could try this (NOTE it is 2010 specific - it won't work in earlier versions)

ActiveWindow.View.GotoSlide 3 ' obviously change as required
CommandBars.ExecuteMso ("PasteSourceFormatting")

 

[nmoiseff]

Trevor,

I appreciate the help.

The code is still not working. It is throwing an error on both of the lines you suggested. This is my current code;

Set PPSlide = PPApp.ActivePresentation.Slides.Add(1, ppLayoutBlank)
Worksheets(SheetName).Range(RangeName1).Copy
ActiveWindow.View.GotoSlide 1
CommandBars.ExecuteMso ("PasteSourceFormatting")

Am I implementing this incorrectly?