none
Binding / PasteSpecial Problem RRS feed

  • Question

  • I’m working on an Excel VBA solution that will copy charts from Excel and paste them as images into a PowerPoint file. I need to use late binding due to a specific requirement. The problem is that when I use early binding via the References dialog, the chart pastes into PowerPoint as an image using the source design. However, when I changed to late binding, the chart is pasted as an image but using the destination design. I can’t change the destination design because that would completely alter the color scheme of the presentation. If I manually paste the image (by right clicking and selecting Paste Options -> Image), I get the desired result (source design is retained).

     

    BTW, I’ve tried using the CopyPicture method, but the gridlines are excessively thick when I paste it into Powerpoint.

     

    I’d appreciate if someone could offer a solution to this problem because I need to retain the source colors. I can’t understand why the binding type effects the pasted image color scheme.

     

    Thanks

     

    My code is:

     

    Sub CopyToPP(ByVal sSheet As String, _
                    ByVal sChart As String, _
                    ByVal dTop As Double, _
                    ByVal dLeft As Double, _
                    ByVal dWidth As Double, _
                    ByVal dHeight As Double, _
                    ByVal iSlide As Integer)
     
      '---CopyToPP method parameters------------------------------------------------
            '      sSheet - String. Name of sheet that contains the chart to copy
            '      sChart - String. Name of the chart to copy
            '      dTop - Double. y coorfinate of chart at destination
            '      dLeft - Double. x coordinate of chart at destination
            '      dWidth - Double. width of chart at destination
            '      dHeight - Double. height of chart at destination
            '      iSlide - Integer. index of destination slide
      '-----------------------------------------------------------------------------
       
        Dim pShape As Object
    
        Dim ppApp as object
    
        Set ppApp = CreateObject("Powerpoint.Application") '<--Late Binding
        'Set ppApp = New PowerPoint.Application '<--Early Binding
        
    
          
        Sheets(sSheet).Activate
        Sheets(sSheet).ChartObjects(sChart).Select
        ActiveChart.ChartArea.Copy
       
        ppApp.ActivePresentation.Slides(iSlide).Select
        Set pShape = ppApp.ActivePresentation.Slides(iSlide).Shapes.PasteSpecial(ppPasteJPG)
               
        With pShape
            .LockAspectRatio = msoFalse
            .Top = dTop
            .Left = dLeft
            .Width = dWidth
            .Height = dHeight
        End With
    
    End Sub

    Monday, May 7, 2012 4:55 PM

Answers

All replies