Paste multiple charts: converting from early binding to late binding to avoid run time 462 error

DF10

New Member
Joined
Apr 30, 2012
Messages
12
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
 

DF10

New Member
Joined
Apr 30, 2012
Messages
12
Oh, i am so ... distracted.
I did not remove the reference to Microsoft powerpoint so who knows what was going on there.
I did and I added a

Const ppViewSlide = 1

Now it seems it runs smoothly.
That said, it was running smoothly also on Friday. And then today it crashed.
So, if someone more knowledgeable than me can give me advice, still very much appreciated.
Cheers
DF
 

Forum statistics

Threads
1,081,530
Messages
5,359,347
Members
400,524
Latest member
Excelbat

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top