I’m trying to write some code that will update tables and charts from Excel linked into PowerPoint when the Excel file is moved. I’ve used sample code from the MS Knowledge Base that works correctly with the tables (just cells of data). However, it is not working with the charts. It truncates the chart data to just using the entire file name, and then shows the entire first worksheet on the slide. Here is the code:
————————————————
Sub ChangeExcelSource()
Dim i As Integer, k As Integer, linkpos As Integer
Dim FileNameAndPath As String, linkname As String
FileNameAndPath = “c:\temp\myfile.xls”
‘loop through slides
For i = 1 To ActivePresentation.Slides.Count With ActivePresentation.Slides(i)
For k = 1 To .Shapes.Count
With .Shapes(k)
‘ If the shape’s type is an OLE object then…
If .Type = msoLinkedOLEObject Then With .LinkFormat
‘ Find where in the source path string the character “!” occurs
linkpos = InStr(1, .SourceFullName, “!”, vbTextCompare)
‘ Assign linkname to worksheet reference
linkname = Right(.SourceFullName, Len(.SourceFullName) – linkpos)
.SourceFullName = FileNameAndPath & “!” & linkname
.AutoUpdate = ppUpdateOptionAutomatic
End With
End If
End With
Next k
End With
Next i
ActivePresentation.UpdateLinks
End Sub
————————————————
Can anyone help???
Thanks!