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


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.
Who is Participating?
ppPasteEnhancedMetafile is just a constant defined in the PowerPoint type library.
If you open the VBA Object Browser in PowerPoint, you can search for ppPasteEnhancedMetafile & you'll get it's value - in this case 2!
If pasting manually works, why don't you try recording a macro of you doing a manual paste?
The when it pastes correctly, stop the macro & you should have the code of how for how PPT 2003 is doing it.
collinc4Author Commented:

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?
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Replace ppPasteEnhancedMetafile with the value 2
Also, in order to get a reference to PPApp, you would have to use either CreateObject or GetObject, since the New operator doesn't work for late binding.
collinc4Author Commented:
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?  


Give this a try it should switch to the slide window - or modify it to suit yr needs.

Sub ActivateSlidePane()
Dim ppApp As Application
Dim nLoop As Long
Dim oPane As Pane

    Set ppApp = Application
    If (ppApp.ActiveWindow.ViewType <> ppViewSlide) Then
        For Each oPane In ActiveWindow.Panes
            If (oPane.ViewType = ppViewSlide) Then
                Exit For
            End If
        Next oPane
    End If
End Sub

collinc4Author Commented:
Thank-you Robert!!  This help saved me a ton of pain!   Collin
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.