Moving Excel Chart to Powerpoint (getting runtime error 91)

[Guest]

I have the following code created (highly leveraged from good examples here,
i might add) in which I wish to move a series of charts (and eventually data
tables) from an Excel file, to a ppt file . I get a "Run-time error '91':
Object variable or With block variable not set" when I execute this code (at
the WITH) statement.

I am an extreme noob at this, so would appreciate any pointers as to where I
have failed go clone this code properly. Note: I want to be able to update
the PPT file, and then save it under a name I have chosen.Thanks!

Pat


Sub ExcelToPowerpoint()

Dim PPApp As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation
Dim PPSlide As PowerPoint.Slide


Set PPApp = CreateObject("Powerpoint.Application")
Set PPPres = PPApp.Presentations.Add
Set PPSlide = PPPres.Slides.Add(1, ppLayoutTitleOnly)

PPApp.Visible = True

' Make sure a chart is selected
If ActiveChart Is Nothing Then
MsgBox "Please select a chart and try again.", vbExclamation, "No Chart
Selected"
Else
' Reference existing instance of PowerPoint
Set PPApp = GetObject(, "Powerpoint.Application")
' Reference active presentation
Set PPPres = PPApp.ActivePresentation
PPApp.ActiveWindow.ViewType = ppViewSlide
' Reference active slide
Set PPSlide =
PPPres.Slides(PPApp.ActiveWindow.Selection.SlideRange.SlideIndex)

' Copy chart as a picture
ActiveChart.CopyPicture Appearance:=xlScreen, Size:=xlScreen,
Format:=xlPicture

' Paste chart
PPSlide.Shapes.Paste.Select

' Align pasted chart
PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True

' Clean up
Set PPSlide = Nothing
Set PPPres = Nothing
Set PPApp = Nothing
End If


With PPPres ' THIS IS WHERE I AM GETTING THE RUNTIME ERROR 91
.SaveAs "C:\Documents and Settings\pklocke\My
Documents\4-Sourcing\Spreadsheets\Pareto\test.ppt"
.Close
End With

PPApp.Quit

Set PPSlide = Nothing
Set PPPres = Nothing
Set PPApp = Nothing

End Sub

 

[Mark Ivey]

Pat,

You are setting all your necessary objects to nothing and then trying to
re-reference them later. Take a look below at a possible solution.

I have a mock addin that will do almost what you are trying to accomplish
here that I created from Jon Peltier's original code if you would like a
copy. Let me know...

'***********************************************************************
Sub ExcelToPowerpoint()

Dim PPApp As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation
Dim PPSlide As PowerPoint.Slide


Set PPApp = CreateObject("Powerpoint.Application")
Set PPPres = PPApp.Presentations.Add
Set PPSlide = PPPres.Slides.Add(1, ppLayoutTitleOnly)

PPApp.Visible = True

' Make sure a chart is selected
If ActiveChart Is Nothing Then
MsgBox "Please select a chart and try again.", vbExclamation, "No Chart
Selected"
Else
' Reference existing instance of PowerPoint
Set PPApp = GetObject(, "Powerpoint.Application")
' Reference active presentation
Set PPPres = PPApp.ActivePresentation
PPApp.ActiveWindow.ViewType = ppViewSlide
' Reference active slide
Set PPSlide =
PPPres.Slides(PPApp.ActiveWindow.Selection.SlideRange.SlideIndex)

' Copy chart as a picture
ActiveChart.CopyPicture Appearance:=xlScreen, Size:=xlScreen,
Format:=xlPicture

' Paste chart
PPSlide.Shapes.Paste.Select

' Align pasted chart
PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True

' Clean up
''Set PPSlide = Nothing 'Rim these out b/c you will need them again
below
''Set PPPres = Nothing 'Rim these out b/c you will need them again
below
''Set PPApp = Nothing 'Rim these out b/c you will need them again
below
End If


With PPPres ' THIS IS WHERE I AM GETTING THE RUNTIME ERROR 91
.SaveAs "C:\Documents and Settings\pklocke\My
Documents\4-Sourcing\Spreadsheets\Pareto\test.ppt"
.Close
End With

PPApp.Quit

Set PPSlide = Nothing
Set PPPres = Nothing
Set PPApp = Nothing

End Sub

 

[Guest]

Thank you, Mark! Ultimately, I did figure this out (d'ohhh....so stupid). I
would LOVE a copy of your mock add in. What I am doing is trying to automate
a beastly process we do weekly, that takes about 2 days, of "massaging" a
massive excel file, then creating about 5 slides from that data in
powerpoint. I will create other VBA scripts to do the massaging...but I have
never really worked with using automation to move charts/tables between apps
(like PPT). How would I get this from you?

Thanks again!!!!

Pat