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.
collinc4Asked:
Who is Participating?
 
r0bertdenir0Commented:
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!
0
 
r0bertdenir0Commented:
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.
0
 
collinc4Author Commented:
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?
0
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.

 
r0bertdenir0Commented:
Replace ppPasteEnhancedMetafile with the value 2
0
 
r0bertdenir0Commented:
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.
0
 
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?  

CC




0
 
r0bertdenir0Commented:
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
                oPane.Activate
                Exit For
            End If
        Next oPane
    End If
   
End Sub


0
 
collinc4Author Commented:
Thank-you Robert!!  This help saved me a ton of pain!   Collin
0
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.