Copy Excel Chart to PowerPoint using VBA

[KevinYC]

Hello Everyone,

I am a new vba user. I am trying to copy an Excel chart into a powerpoint slide using vba. I got an error (Invalid porcedure call or argument) at this line of code "ActiveSheet.ChartObjects(chart_name).Activate" highlited in red. The code opens the powerpoint doc successfully but stops at the line colored in red. The chart's title in excel is 'income' and that's how I reference it in the fuction. What am I doing wrong?

Any input would be greatly appreciated!!

Here is my code:

Sub makePowerPoint()
...Dim PPT As PowerPoint.Application
Set PPT = New PowerPoint.Application
PPT.Visible = True
PPT.Presentations.Open Filename:="C:\My Documents\MacroTest.ppt"
Copy_chart "Sheet1", "income", 1, 250, 200, 60, 15
End Sub



Public Function copy_chart(sheet, chart_name, slide, awidth, aheight, atop, aleft)
Sheets(sheet).Select
' Set a VBE reference to Microsoft PowerPoint Object Library
Dim PPApp As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation
Dim PPSlide As PowerPoint.slide
' Reference existing instance of PowerPoint
Set PPApp = GetObject(, "Powerpoint.Application")
' Reference active presentation
Set PPPres = PPApp.ActivePresentation
PPApp.ActiveWindow.ViewType = ppViewSlide
PPApp.ActiveWindow.View.GotoSlide (slide)
' Reference active slide
Set PPSlide = PPPres.Slides(PPApp.ActiveWindow.Selection.SlideRange.SlideIndex)
ActiveSheet.ChartObjects(chart_name).Activate
ActiveChart.ChartArea.Copy
PPSlide.Select
PPSlide.Shapes.PasteSpecial ppPastePNG
PPSlide.Select
PPSlide.Shapes(PPSlide.Shapes.Count).Select
Dim sr As PowerPoint.ShapeRange
Set sr = PPApp.ActiveWindow.Selection.ShapeRange
' Resize:
sr.Width = awidth
sr.Height = aheight
If sr.Width > 700 Then
sr.Width = 700
End If
If sr.Height > 420 Then
sr.Height = 420
End If
' Realign:
sr.Align msoAlignCenters, True
sr.Align msoAlignMiddles, True
sr.Top = atop
If aleft <> 0 Then
sr.Left = aleft
End If
Set PPSlide = Nothing
Set PPPres = Nothing
Set PPApp = Nothing
End Function

 

[Jon Peltier]

Comment out the On Error GoTo ErrorHandling line, so the debugger will highlight the line that gives you the error. Reinstate this line only after the program works.

 

[Horus23]

Comment out the On Error GoTo ErrorHandling line, so the debugger will highlight the line that gives you the error. Reinstate this line only after the program works.

I Commented 'On Error GoTo ErrorHandling' and post running the Macro now I'm at below error:

Error: 91 Object variable or With block variable not set

in below line

PP.ActivePresentation.Slides.Add PP.ActivePresentation.Slides.Count + 1, 11

 

[Jon Peltier]

Okay, I don't know what you did with the module-level variables, but now you have variables declared in CopytoPPT that were assigned to objects in ExporttoPPT. Since CopyToPPT no longer knows what PP refers to, it chokes.

I've been meaning to do this for several posts, because it's making it more difficult for you to handle and for me to follow. Let's put the code together.

Option Explicit

Sub ExporttoPPT()

    Dim PP As PowerPoint.Application
    Dim PPPres As PowerPoint.Presentation
    Dim PPSlide As PowerPoint.slide
    Dim NextShape As Long
    Dim myRangeName As String
    Dim ActFileName As Variant

    ''On Error GoTo ErrorHandling '' disable for now

    ActFileName = Application.GetOpenFilename("Microsoft PowerPoint-Files (*.pptx), *.pptx")
    Set PP = CreateObject("Powerpoint.Application")
    If ActFileName = False Then
        PP.Activate
        PP.Presentations.Add
        Set PPPres = PP.ActivePresentation
    Else
        PP.Activate
        Set PPPres = PP.Presentations.Open(ActFileName)
    End If
    PP.Visible = True

    myRangeName = "myDashboard01"

    Application.GoTo Reference:=myRangeName
    Selection.CopyPicture Appearance:=xlScreen, Format:=xlPicture
    Range("B1").Select
    PP.ActivePresentation.Slides.Add PP.ActivePresentation.Slides.Count + 1, 11
    Set PPSlide = PPPres.Slides(PP.ActivePresentation.Slides.Count)
    PPSlide.Shapes.Placeholders(ppPlaceholderTitle).TextFrame.TextRange.Font.Size = 36
    NextShape = PPSlide.Shapes.Count + 1
    PPSlide.Shapes.PasteSpecial 2
    PPSlide.Shapes(NextShape).Height = Application.InchesToPoints(5.56)
    PPSlide.Shapes(NextShape).Width = Application.InchesToPoints(11.9)

    Set PPSlide = Nothing
    Set PPPres = Nothing
    Set PP = Nothing
    Worksheets(1).Activate
    Exit Sub

ErrorHandling:

    Set PPSlide = Nothing
    Set PPPres = Nothing
    Set PP = Nothing
    MsgBox "Error No.: " & Err.Number & vbNewLine & vbNewLine & "Description: " & Err.Description, vbCritical, "Error"

End Sub

 

[Horus23]

Okay, I don't know what you did with the module-level variables, but now you have variables declared in CopytoPPT that were assigned to objects in ExporttoPPT. Since CopyToPPT no longer knows what PP refers to, it chokes.

I've been meaning to do this for several posts, because it's making it more difficult for you to handle and for me to follow. Let's put the code together.

Hi Jon, sorry for the trouble. But I really appreciate your effort and support on this. After putting down my last post I want back to my original code and made some tweaks and changes and it's working great. My only concern as if now is how to get the Title and Object's size right. I also referred to your website but still it seems I can't get that right.

Is there a way we fix this or does the user has to reshape it manually.

This might be the last fix and we would be good to go.

Thanks again for all your efforts.

 

[Horus23]

Let's put the code together.

Option Explicit

Sub ExporttoPPT()

I'm glad to say that this is working great Minus the title but I fixed that problem. Thanks Jon!