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

[Wild Bill]

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.

 

[Wild Bill]

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]

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]

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]

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]

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.