Hello,
I am using Excel 2010 on Windows 7
I am trying to do some excel to powerpoint automation.
The excel workbook consists of a number (say 100) of spreadsheets which generate 100 charts (each chart corresponding to a range of 1 and 1 only speadsheet; each chart on a separate worksheet, basically what you would get by selecting the range and hitting F11).
I have one macro to paste these excel charts to a specific placeholder of my powerpoint preso (adapted from J.Peltier) and another one to grab the content of cell A1 of each worksheet and paste it to the placeholder "Title".
Now, the latter works fine and does not give me any trouble.
The first one sometimes works, sometimes gives me a run time 462 error.
I read the diagnostic to this error on
http://support.microsoft.com/default.aspx?kbid=178510
http://support.microsoft.com/kb/319832/en-us
"These issues may occur if the Automation uses early binding in Microsoft Visual Basic 5.0 or Microsoft Visual Basic 6.0. These issues occur when the code makes an unqualified method call or property call to an Office object"
It could make sense as I am actually using early binding.
Still I cannot understand why it works on the paste title macro (or maybe I've just been lucky so far).
Anyway, I tried to convert my macro to late binding.
I am no VBA expert (actually I am a novice), so I am not sure if I missed something.
As a result, however, I still get (sometimes) that run time 462 error.
In the following the code, where I highlighted the point(s) where I get it.
Any help would be much appreciated
(Or maybe it has to do with the object I paste being a chart?)
Cheers
DF
Code
Sub AllCharts2Preso()
' Adapted from J.Peltier
' Try to use Late Binding to the PowerPoint Object Model
Dim PPApp As Object 'PowerPoint.Application
Dim PPPres As Object 'PowerPoint.Presentation
Dim PPSlide As Object 'PowerPoint.Slide
Dim AddSlidesToEnd As Boolean
Dim nPlcHolder As Long
Dim chtTemp As Chart
AddSlidesToEnd = True
Application.ScreenUpdating = False
For Each chtTemp In ActiveWorkbook.Charts
'Look for existing instance of PowerPoint
Set PPApp = GetObject(, "PowerPoint.Application")
PPApp.Visible = True
' Use active presentation
Set PPPres = PPApp.ActivePresentation
'Set view to normal slide view
PPApp.ActiveWindow.ViewType = ppViewSlide
' Use active slide
'I was getting a runtime 462 error the next line
Set PPSlide = PPPres.Slides(PPApp.ActiveWindow.Selection.SlideRange.SlideIndex)
PPApp.Visible = True
If AddSlidesToEnd Then
'Appends slides to end of presentation and makes last slide active
PPPres.Slides.AddSlide PPPres.Slides.Count + 1, PPPres.SlideMaster.CustomLayouts(6)
PPApp.ActiveWindow.View.GotoSlide PPApp.ActivePresentation.Slides.Count
'I get a runtime 462 error the next line
Set PPSlide = PPApp.ActivePresentation.Slides(PPApp.ActivePresentation.Slides.Count)
Else
'Sets current slide to active slide
Set PPSlide = PPApp.ActiveWindow.View.Slide
End If
' Reference active slide
'I was getting a runtime 462 error the next line
Set PPSlide = PPApp.ActivePresentation.Slides(PPApp.ActiveWindow.Selection.SlideRange.SlideIndex)
' Copy chart
chtTemp.ChartArea.Copy
' Paste chart
With PPPres
nPlcHolder = 2 'The place holder where I want to paste
PPSlide.Shapes.Placeholders(nPlcHolder).Select msoTrue
PPApp.ActiveWindow.View.PasteSpecial link:=False
End With
'PPPres.Save
Next
'add slide with Section header
PPPres.Slides.AddSlide PPPres.Slides.Count + 1, PPPres.SlideMaster.CustomLayouts(3)
PPPres.Save
'Clean up
PPApp.Visible = True
AppActivate ("Microsoft PowerPoint")
Set PPSlide = Nothing
Set PPPres = Nothing
Set PPApp = Nothing
Application.ScreenUpdating = True
End Sub
I am using Excel 2010 on Windows 7
I am trying to do some excel to powerpoint automation.
The excel workbook consists of a number (say 100) of spreadsheets which generate 100 charts (each chart corresponding to a range of 1 and 1 only speadsheet; each chart on a separate worksheet, basically what you would get by selecting the range and hitting F11).
I have one macro to paste these excel charts to a specific placeholder of my powerpoint preso (adapted from J.Peltier) and another one to grab the content of cell A1 of each worksheet and paste it to the placeholder "Title".
Now, the latter works fine and does not give me any trouble.
The first one sometimes works, sometimes gives me a run time 462 error.
I read the diagnostic to this error on
http://support.microsoft.com/default.aspx?kbid=178510
http://support.microsoft.com/kb/319832/en-us
"These issues may occur if the Automation uses early binding in Microsoft Visual Basic 5.0 or Microsoft Visual Basic 6.0. These issues occur when the code makes an unqualified method call or property call to an Office object"
It could make sense as I am actually using early binding.
Still I cannot understand why it works on the paste title macro (or maybe I've just been lucky so far).
Anyway, I tried to convert my macro to late binding.
I am no VBA expert (actually I am a novice), so I am not sure if I missed something.
As a result, however, I still get (sometimes) that run time 462 error.
In the following the code, where I highlighted the point(s) where I get it.
Any help would be much appreciated
(Or maybe it has to do with the object I paste being a chart?)
Cheers
DF
Code
Sub AllCharts2Preso()
' Adapted from J.Peltier
' Try to use Late Binding to the PowerPoint Object Model
Dim PPApp As Object 'PowerPoint.Application
Dim PPPres As Object 'PowerPoint.Presentation
Dim PPSlide As Object 'PowerPoint.Slide
Dim AddSlidesToEnd As Boolean
Dim nPlcHolder As Long
Dim chtTemp As Chart
AddSlidesToEnd = True
Application.ScreenUpdating = False
For Each chtTemp In ActiveWorkbook.Charts
'Look for existing instance of PowerPoint
Set PPApp = GetObject(, "PowerPoint.Application")
PPApp.Visible = True
' Use active presentation
Set PPPres = PPApp.ActivePresentation
'Set view to normal slide view
PPApp.ActiveWindow.ViewType = ppViewSlide
' Use active slide
'I was getting a runtime 462 error the next line
Set PPSlide = PPPres.Slides(PPApp.ActiveWindow.Selection.SlideRange.SlideIndex)
PPApp.Visible = True
If AddSlidesToEnd Then
'Appends slides to end of presentation and makes last slide active
PPPres.Slides.AddSlide PPPres.Slides.Count + 1, PPPres.SlideMaster.CustomLayouts(6)
PPApp.ActiveWindow.View.GotoSlide PPApp.ActivePresentation.Slides.Count
'I get a runtime 462 error the next line
Set PPSlide = PPApp.ActivePresentation.Slides(PPApp.ActivePresentation.Slides.Count)
Else
'Sets current slide to active slide
Set PPSlide = PPApp.ActiveWindow.View.Slide
End If
' Reference active slide
'I was getting a runtime 462 error the next line
Set PPSlide = PPApp.ActivePresentation.Slides(PPApp.ActiveWindow.Selection.SlideRange.SlideIndex)
' Copy chart
chtTemp.ChartArea.Copy
' Paste chart
With PPPres
nPlcHolder = 2 'The place holder where I want to paste
PPSlide.Shapes.Placeholders(nPlcHolder).Select msoTrue
PPApp.ActiveWindow.View.PasteSpecial link:=False
End With
'PPPres.Save
Next
'add slide with Section header
PPPres.Slides.AddSlide PPPres.Slides.Count + 1, PPPres.SlideMaster.CustomLayouts(3)
PPPres.Save
'Clean up
PPApp.Visible = True
AppActivate ("Microsoft PowerPoint")
Set PPSlide = Nothing
Set PPPres = Nothing
Set PPApp = Nothing
Application.ScreenUpdating = True
End Sub
