If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below. If you don't like Google AdSense in the posts, register or log in above.

Update Links In Powerpoint

October 19th, 2007, 07:59

I have excel links in powerpoint that are set to manual update. How can I get them to update using VBA? (i.e. the equivalent of going to Edit, Links, selecting all links and clicking Update Now.). I tried using

Sub linkupdate()
Dim osld As Slide
Dim oshp As Shape
For Each osld In ActivePresentation.Slides
For Each oshp In osld.Shapes
If oshp.Type = msoLinkedOLEObject Then
oshp.LinkFormat.Update
Next oshp
Next osld
End Sub

Sub linkupdate()
Dim osld As Slide
Dim oshp As Shape
For Each osld In ActivePresentation.Slides
For Each oshp In osld.Shapes
If oshp.Type = msoLinkedOLEObject Then
oshp.LinkFormat.Update
Next oshp
Next osld
End Sub

so I know this thread is really old, but I am trying to do this in powerpoint 2010 and this code isn't working, any thoughts?

Comment

I'm having the same issue and none of these solutions seem to work. I would just set the links to 'automatic', but I get an error message of "The source application is busy and can't respond immediately" when running the vba which combines refreshing data queries/pivot tables and updating the links in powerpoint. Is there a way to update the links via VBA if they're set to manual in PowerPoint? This is driving me insane.

Comment

I'm having the same issue and none of these solutions seem to work. I would just set the links to 'automatic', but I get an error message of "The source application is busy and can't respond immediately" when running the vba which combines refreshing data queries/pivot tables and updating the links in powerpoint. Is there a way to update the links via VBA if they're set to manual in PowerPoint? This is driving me insane.

OK, I *think* I may have solved my own problem, by changing the links to manual update before the queries run and the pivot tables update, and then right back to automatic when complete, then running UpdateLinks. I can't take credit for any of the code, it's a cross between a few different sources (MVPs, http://www.recursivecreativity.com/f...ed_Objects.txt, and maybe another place or two). Please excuse any code bloat/inefficiencies as I'm not a developer.

Code:

Sub Refresh1() Dim PPApp As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation
Dim PPSlide As PowerPoint.Slide
Dim PPShape As PowerPoint.Shape
Dim osld As Slide
Dim oShp As Shape
Application.DisplayAlerts = False
Set PPApp = GetObject(, "Powerpoint.Application")
Set PPPres = PPApp.ActivePresentation
PPApp.DisplayAlerts = ppAlertsNone
PPPres.SlideShowWindow.View.First
PPPres.SlideShowWindow.View.State = ppSlideShowPaused
For Each osld In PPPres.Slides
Call SetLinksToManual(osld)
Next
For Each wSht In ThisWorkbook.Worksheets
For Each qt In wSht.QueryTables
qt.BackgroundQuery = False
qt.Refresh
Next
For Each pt In ThisWorkbook.PivotCaches
pt.Refresh
Next pt
Next
For Each osld In PPPres.Slides
Call SetLinksToAutomatic(osld)
Next
PPPres.UpdateLinks
PPPres.SlideShowWindow.View.State = ppSlideShowRunning
PPApp.DisplayAlerts = ppAlertsAll
Application.DisplayAlerts = True
Application.OnTime Now + TimeValue("00:15:00"), "Refresh1"
End Sub
Sub SetLinksToAutomatic(oSlideOrMaster As Object)
Dim oShp As PowerPoint.Shape
For Each oShp In oSlideOrMaster.Shapes
If oShp.Type = msoLinkedOLEObject Then
'Set the link to automatic update mode
oShp.LinkFormat.AutoUpdate = ppUpdateOptionAutomatic
oShp.LinkFormat.Update
End If
Next oShp
End Sub
Sub SetLinksToManual(oSlideOrMaster As Object)
Dim oShp As PowerPoint.Shape
For Each oShp In oSlideOrMaster.Shapes
If oShp.Type = msoLinkedOLEObject Then
'Set the link to manual update mode
oShp.LinkFormat.AutoUpdate = ppUpdateOptionManual
End If
Next oShp
End Sub