VBA to copy range from Excel to Powerpoint as a spreadsheet, not a picture

Wild Bill

Board Regular
Joined
Feb 20, 2006
Messages
125
I am writing a macro that copies ranges from Excel into a Powerpoint presentation. Some of the ranges need to be copied as spreadsheet files because the size is not known in advance and they may have to be split over several pages.

I understand how to copy as a picture [Selection.CopyPicture Appearance:=xlScreen, Format:=xlPicture] but how do I copy as a spreadsheet? When I use Selection.Copy I get a picture.
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Wild Bill

Board Regular
Joined
Feb 20, 2006
Messages
125
Haven't yet found an answer to this question anywhere else.

Does anybody know of a good reference on MS Office that addresses the topic of writing VBA code in Excel that carries out operations in PowerPoint?

Another task I'll want to do after completing the pasting of Excel ranges into the PowerPoint file is to do a SaveAs for the PowerPoint file and insert a suggested file name. If anybody can point out how to do that I'd appreciate it.
 

Wild Bill

Board Regular
Joined
Feb 20, 2006
Messages
125
It appears that the use of Selection.CopyPicture results in a paste that is equivalent to the Windows Metafile option. The range I am copying to PowerPoint may have special characters in it so I need to copy as an Enchanced Metafile or in other cases I need to copy as an Excel Worksheet Object. Is there any way to exercise the same kind of control when using VBA to paste a spreadsheet range into PowerPoint as there is available when you manually use Paste Special?
 

Wild Bill

Board Regular
Joined
Feb 20, 2006
Messages
125
I've been searching elsewhere and still haven't found an answer to my problem. I get responses here at MrExcel on simple things, but this issue must be too obscure. Can anybody suggest a good reference that addresses the topic of writing Excel VBA code that crosses over into PowerPoint?
 

Wild Bill

Board Regular
Joined
Feb 20, 2006
Messages
125
I have found Jon Peltier's recommendations on his web site:

Bullen, Bovey, Green: Professional Excel Development
Walkenbach: Power Programming

They each have a chapter that addresses the topic.
 

Wild Bill

Board Regular
Joined
Feb 20, 2006
Messages
125
To paste an Excel range into PowerPoint as an Excel file use this:

PPSlide.Shapes.PasteSpecial(ppPasteOLEObject).Select

where PPSlide is a variable dimensioned as PowerPoint.Slide


To paste an Excel range into Powerpoint as an Enhanced Metafile:

PPSlide.Shapes.PasteSpecial(ppPasteEnhancedMetafile).Select


I keep answering my own questions, I must be getting better at this VBA stuff.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,085
Messages
5,509,162
Members
408,711
Latest member
EMexcel

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top