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?

Solution 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

Solution 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.

Comments

[iper]

I've tried PPSlide.Selection.PasteExcelTable - but this doesnt work. and also PPSlide.Shapes.PasteExcelTable . Any ideas? EDIT: I cant record macros in powerpoint, but when I try to do it in word and copy the table from excel the way u suggest I get this code: Selection.PasteAndFormat (wdTableOriginalFormatting) - is there something similar i can use in the powerpoint vba code?

[PowerUser]

Hmm, why can't you use the PasteExcelTable methods? What error do you get?

[iper]

Compile error: Method or data member not found Is the error i get when I try PPSlide.Selection.PasteExcelTable or PPSlide.Shapes.PasteExcelTable. So i guess the syntax isnt all good then?

[PowerUser]

(A little training tip: VBA has a feature called Intellisense which gives you a list of available methods/properties/etc. In VBA, if you type "PPSlide." you should see a drop-down list of the members of that class. If you don't see what you're looking for, then you're doing it wrong. Very helpful when you're designing something you've never done before)

[PowerUser]

See my Edit above. An alternative approach.

[iper]

Thanks for the alternative solution. But I can't get it to work. This is mainly because my excel document I try to link to is empty and have a macro to run on the on open event that creates the content dynamically from an external xml source, and the document is never saved. I wish there would be an easier approach to this problem, but it seems like I may have to try to tweak my macros to get it work this way, if no one else have any other solutions. Thanks for the help, really appreciate it!

[PowerUser]

Let me get this straight: You have an xml source document that feeds data to an Excel file that feeds data to a powerpoint? What if you just simulate the powerpoint in Excel? 1 less format to worry about.

[Dave DuPlantis]

Downvoted (perhaps harshly) given the simplicity of Simon's VBA solution; I found basically the same code at the MrExcel forums (mrexcel.com/forum/showthread.php?t=376733).

[PowerUser]

@Dave Duplantis, I agree that Simon's solution is much simpler that mine (haven't tried it yet, but it should work). However, you're downvoting me because a better solution was posted over 3 months after mine. And no one else said anything about it in that entire time. In your shoes, I would just upvote Simon's solution, make a comment, and leave it at that.

[Mowgli]

Can you explain this please? I am not able get this Macro work.