asked on

how do I copy a large excel table in xlpicture format to powerpoint 2003 as emf format using vba

Hi,

I am trying copy several columns (>50 but half are blank) of numbers from excel into powerpoint 2003 using VBA.. It's easy in powerpoint 2007, but I need it to work in both PPT 2003 and 2007. But when pasting into powerpoint 2003, I find the table is cut off, i.e., the rightmost several columns disappear.

I know I have the object ready to paste. I can even "paste special" manually selecting "Picture (Enhanced Metafile", and then entire table comes in. I have tried the folloing line of code: ActiveWindow.View.PasteSpecial DataType:=ppPasteEnhancedMetafile, and several variations of that. But it just gives me an errors or ignores it. Please help.

SOLUTION

r0bertdenir0

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

collinc4

ASKER

HI,

That's the problem -- the macro won't record that step. Recorded macro launched both from excel and from ppt are blank.

But I found the righ code, the key command is PPApp.ActiveWindow.View.PasteSpecial (ppPasteEnhancedMetafile).. But the problem now s that I need to "early bind" power point in order for that command to work.. Otherwise the compiler trips up on that command: Without early binding the compiler can't see the PPT and says that ppPasteEnhancedMetafile is an unknown variable. Unfortunately, I need late binding because I need to link to powerpoint for both Office 2003 and Office 2007 users. I know about getobject and createobject, but I think that pasting into power point is a method, not an object.

So now I need to know how to get the compiler to ignore the fact that ppPasteEnhancedMetafile will not defined until it's late bound. How can I do that?

SOLUTION

r0bertdenir0

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

r0bertdenir0

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

r0bertdenir0

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

collinc4

ASKER

Wow, thanks Robert! Please, though, one small thing before we close this out.

This approach really works as long as I have selected any large slide in the main powerpoint window pane before I launch the excel macro. Good news is that's the normal case.

HOWEVER, if I manually select a small slide (in the slide sorter view pane on the left) before I launch, it doesn't work. In fact the pastespecial gives me an error message as follows: -2147188160 View.PasteSpecial : Invalid request. The specified data type is unavailable. I know this is caused by being in wrong view pane when I launch.

I just don't get this. I would think manually selecting the big slide or the corresponding small slide would not affect this pastespecial command when the VBA. Even that would not be a big deal if I knew the command to select the big slide in the correct pane vs. the small slide. But I can't seem to differentiate in VBA. I.e. it seems to use big or corresponding little slide based on what was most recently selected.

Any way to clear this up? How can I select the big slide in the main window instead of the little slide?

CC

SOLUTION

r0bertdenir0

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

collinc4

ASKER

Thank-you Robert!! This help saved me a ton of pain! Collin