Problems updating powerpoint links to excel

[nikulina]

Hi,

Hoping someone out there might be able to help!

I have been using a piece of VBA code in powerpoint for 10 years now which ecentially goes through all the objects in powerpoint, finds the linkedobjects and replaces the existing source string (shape.linkformat.sourcefullname) with a new source.

This allows me to use a template powerpoint file and use it with multiple excel files (all of which have the same graph's (and therefor graph names in))

However, since moving to office 2010 (from 2003), the below command now causes the original excel file to open (the new source file is always open). This causes the code to open and reOpen the original excel file over and over again for each OLE object:


"sh.LinkFormat.SourceFullName = strFilePath & "!" & StrRangeAddress"

I dont see why it now needs to open the oringal file just to change the link. One option is obviously to open the original file 1st, however the powerpoint file may at first include links to numerous excel files, so its impractical to open them all (they are also quite large files). Full code is as follows:


For Each sld In PS.Slides
For Each sh In sld.Shapes
If sh.Type = msoLinkedOLEObject Then
If sh.OLEFormat.ProgID Like "Excel.*" Then
strSourceName = ""
strSourceName = sh.LinkFormat.SourceFullName
If InStr(strSourceName, "!") > 0 Then
intOleLinkCount = intOleLinkCount + 1
StrRangeAddress = Mid(strSourceName, InStr(strSourceName, "!") + 1)
If InStr(StrRangeAddress, "]") > 0 Then
StrRangeAddress = Mid(StrRangeAddress, InStr(StrRangeAddress, "]") + 1)
End If
With sh.LinkFormat
.SourceFullName = strFilePath & "!" & StrRangeAddress
'If chkUpdate.Value = -1 Then .Update
End With
Else
strErrorString = strErrorString & "Error on slide " & sld.SlideNumber & Chr(10)
End If
End If
End If
Next
Next
End If