Referencing an old thread: http://www.ozgrid.com/forum/showthread.php?t=147605&page=1 but hoping to improve on the code.
I used the code and it works great as long as only one excel source file is linked to the Presentation. Was hoping code could be modified to do the following (assuming there are more than one linked files):
1. Scan the PPT file and list all external excel source files within the document.
2. Allow the user to pick one of the existing source files.
3. Apply the below code to change the selected old source file with the the newly selected source file.
Code
- Sub M1()
- Dim sld As Slide
- Dim sh As Shape
- Dim ExcelFileNew
- Dim exl As Object
- Set exl = CreateObject("Excel.Application")
- 'Open a dialog box to promt for the new source file.
- ExcelFileNew = exl.Application.GetOpenFilename(, , "Select Excel File")
- Call stripPath(ExcelFileNew, filenameNew)
- For Each sld In ActivePresentation.Slides
- For Each sh In sld.Shapes
- If sh.Type = msoLinkedOLEObject Then
- With sh.LinkFormat
- LinkOld = .SourceFullName
- Call stripReference(LinkOld, fullpathOld)
- Call stripPath(fullpathOld, filenameOld)
- LinkNew = Replace(LinkOld, filenameOld, filenameNew)
- .SourceFullName = LinkNew
- 'Call showStrings(ExcelFileNew, filenameNew, LinkOld, fullpathOld, filenameOld, LinkNew)
- End With
- End If
- Next sh
- Next sld
- End Sub
- '
- Sub stripPath(fullPath, filename)
- 'This will take c:\folder\workbook.xlsx* and provide workbook.xlsx*
- Dim filenamePosition As Long
- filenamePosition = InStrRev(fullPath, "\")
- filename = Mid(fullPath, filenamePosition + 1, Len(fullPath) - filenamePosition)
- End Sub
- '
- Sub stripReference(fullReference, filename)
- 'This will take *workbook.xls!Graphs![workbook.xls]Graphs Chart 1 and provide *workbook.xls
- Dim referencePosition As Long
- referencePosition = InStr(1, fullReference, "!")
- filename = Left(fullReference, referencePosition - 1)
- End Sub
- '
- Sub showStrings(ExcelFileNew, filenameNew, LinkOld, fullpathOld, filenameOld, LinkNew)
- 'This is just a debugging function to display the variables
- MsgBox ("ExcelFileNew: " & ExcelFileNew & vbNewLine _
- & "filenameNew: " & filenameNew & vbNewLine _
- & "LinkOld: " & LinkOld & vbNewLine _
- & "fullpathOld: " & fullpathOld & vbNewLine _
- & "filenameOld: " & filenameOld & vbNewLine _
- & "LinkNew: " & LinkNew)
- End Sub