?
Solved

Using VBA to copy Excel range to PowerPoint table

Posted on 2014-01-02
3
Medium Priority
?
5,653 Views
Last Modified: 2014-01-03
Hi Experts:

I've done lots of automation between Excel and PowerPoint, including copying Chart objects and copying Excel data into the data table behind a PowerPoint Chart.  

But I can't figure this one out.  Using VBA I want to copy a formatted Excel range into a PowerPoint TABLE.  I want to duplicate what I get when I manually copy the range, then manually paste into PPT, selecting the Keep Source Formatting option.  It's a heat map, so very important to preserve formatting.

Once it's in PowerPoint, I need to be able to further manipulate the table, changing font sizes, adding rows, etc. all of which I know how to do.  Where I'm stuck is getting it to come in as a table.

The following two options both copy the formatting of the source cells, but in one case it's just a picture and in another it's an OLE object I can't manipulate

PPSlide.Shapes.PasteSpecial DataType:=ppPasteEnhancedMetafile
PPSlide.Shapes.PasteSpecial DataType:=ppPasteOLEObject

Open in new window

In the past I've iterated through the range and copied the values into the cells of the PowerPoint table one at a time, but there has to be a better way.

Thanks for your suggestions.
0
Comment
Question by:Bryce Bassett
3 Comments
 
LVL 54

Accepted Solution

by:
Rgonzo1971 earned 2000 total points
ID: 39753168
Hi,

pls try

ActivePresentation.Application.CommandBars.ExecuteMso ("PasteSourceFormatting")

Open in new window

Regards
0
 
LVL 23

Expert Comment

by:JSRWilson
ID: 39753227
As Rgonzo says "PasteSourceFormatting" models pressing the Keep SAource Format button. It only runs from version 2010.

You will probably need to select the start cell of the table first.

Assuming otbl is a reference to the table and the start cell is 2,2

otbl.Cell(2, 2).Select
CommandBars.ExecuteMso ("PasteSourceFormatting")
0
 

Author Closing Comment

by:Bryce Bassett
ID: 39754528
I knew there was an easier way.  Thanks!

JSR, I didn't use your suggestion of selecting the table cell.  That does not appear to be necessary.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question