How to copy cell range as table from Excel to PowerPoint - VBA

Question

I can't find any way to do this. What I have now is that it copy the range as an image:

Dim XLApp As Excel.Application 
Dim PPSlide As Slide 

Set XLApp = GetObject(, "Excel.Application") 
XLApp.Range("A1:B17").Select 
XLApp.Selection.CopyPicture Appearance:=xlScreen, Format:=xlPicture
PPSlide.Shapes.Paste.Select

this works like a charm, but is it possible to get it to copy the range as a table instead of picture?

Answer 1

This can be done simply with

Dim XLApp As Excel.Application
Dim PPSlide As Slide

Set XLApp = GetObject(, "Excel.Application")
XLApp.Range("A1:B17").Copy
PPSlide.Shapes.PasteSpecial DataType:=ppPasteOLEObject, Link:=msoFalse

Answer 2

Well, if I was copying it manually, I would probably do a Paste Special and choose "Formatted Text (RTF)" as the type. I'm sure you can mimic that in VBA.

Edit

Aah, here we go. Do this in your powerpoint:

1. Go to Insert->Object
2. Choose your Excel file. Check the Link option.

A link to your XL file is now embedded in your PP file. When the data in your XL file changes, you can:

1. Update it manually by Right-Click->Update Link.
2. Update it automatically by VBA by using something like ActivePresentation.UpdateLinks

This is a very different approach than what you were doing first, but I believe it gets you closer to your goal. It has it own problems, though, but those can be worked out.