From Excel 2013 VBA Open PowerPoint 2013 and Update Links

[davidb88]

Hi. I have an excel workbook that is automatically updated with new data that then changes charts embedded into the workbook on a weekly basis. I also have a powerpoint deck that has those charts as linked charts and set to automatically update when the powerpoint is opened.

What I would like to do is to create a macro that from the excel file will open the powerpoint deck and update the links. I found that I am able to open the powerpoint with the following code, but because it is opening from a macro, I suspect, the links within the deck do not refresh.

Application.DisplayAlerts = False


Set PPT = New PowerPoint.Application
PPT.Visible = True
PPT.Presentations.Open Filename:="C:\Operations Weekly Deck - 02.08.2016.pptx"
    
Application.DisplayAlerts = True

End Sub

I then found another thread with a similar question that was answered with using the following code. However, when I try to implement it I receive a 429 error saying "ActiveX component can't create object". I have the active X reference library selected as well as the MS PowerPoint reference library selected. Can anyone suggest where I am going wrong with my current code or maybe a different suggestion completely?

Sub powerpoint_refresh()


Dim pApp As Object
Dim pPreso As Object
Dim pSlide As Object
Dim sPreso As String


sPreso = "C:\Operations Weekly Deck - 02.08.2016.pptx"
Set pApp = GetObject(, "PowerPoint.application")
If Err.Number <> 0 Then
Set pApp = CreateObject("PowerPoint.application")
pApp.Visible = True
End If


Set pPreso = pApp.Presentations(sPreso)
If Err.Number <> 0 Then
Set pPreso = paoo.Presentations.Open(Filename:=sPreso)
End If


pPreso.UpdateLinks
End Sub

Thanks!

 

[Domenic]

That's probably because you're trying to get an instance of PowerPoint when one doesn't exist. In other words, PowerPoint isn't already running. So, in this case, an error occurs. And, since you haven't used an "On Error" statement, the macro stops when the error occurs.

The same thing will happen later on in your code when you try to assign a PowerPoint presentation to a variable and it doesn't exist. Because you haven't used an "On Error" statement, an error will occur and the macro will stop.

Try something like this (untested)...

[COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR]

[COLOR=darkblue]Sub[/COLOR] powerpoint_refresh()

    [COLOR=darkblue]Dim[/COLOR] pApp [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Object[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] pPreso [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Object[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] pSlide [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Object[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] sPath [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] sPreso [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    
    sPath = "C:\"
    [COLOR=darkblue]If[/COLOR] Right(sPath, 1) <> "\" [COLOR=darkblue]Then[/COLOR]
        sPath = sPath & "\"
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    
    sPreso = "Operations Weekly Deck - 02.08.2016.pptx"
    
    [COLOR=darkblue]If[/COLOR] Len(Dir(sPath & sPreso, vbNormal)) = 0 [COLOR=darkblue]Then[/COLOR]
        MsgBox "The specified file does not exist.", vbExclamation
        [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    
    [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]Resume[/COLOR] [COLOR=darkblue]Next[/COLOR]
    [COLOR=darkblue]Set[/COLOR] pApp = GetObject(, "PowerPoint.Application")
    [COLOR=darkblue]If[/COLOR] Err.Number <> 0 [COLOR=darkblue]Then[/COLOR]
        [COLOR=darkblue]Set[/COLOR] pApp = CreateObject("PowerPoint.Application")
        pApp.Visible = [COLOR=darkblue]True[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]GoTo[/COLOR] 0
    
    [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]Resume[/COLOR] [COLOR=darkblue]Next[/COLOR]
    [COLOR=darkblue]Set[/COLOR] pPreso = pApp.Presentations(sPreso)
    [COLOR=darkblue]If[/COLOR] Err.Number <> 0 [COLOR=darkblue]Then[/COLOR]
        [COLOR=darkblue]Set[/COLOR] pPreso = pApp.Presentations.Open(Filename:=sPath & sPreso)
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]GoTo[/COLOR] 0
    
    pPreso.UpdateLinks

[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Hope this helps!

 

[davidb88]

Thank you! Your code worked perfectly. I didn't realize I'd need an error statement to keep the query going and prevent the macro from stopping, but your explanation makes perfect sense. Thanks again, I really appreciate it!

 

[Domenic]

You're very welcome. Glad I could help.

Cheers!