Updating all links in a PowerPoint to an Excel document

kiwicanta7

New Member
Joined
May 3, 2021
Messages
15
Office Version
  1. 2016
Platform
  1. Windows
All,

I have several different things that are being pulled from my Excel document into a PowerPoint and Word document using links. While I am able to make it work great on my own computer, I need it to function when being sent to others. While Word seems to find the new place fine after saving it in a new location, PowerPoint does not appear to be able to do it even if using Save As and even if the links show the new location. Once that is closed down and pulled back up the links are back to the original location again and cease to function. While I could show them how to manually re-link it to wherever they put their files, there are a lot of files that they have to do this individually to and thus why I'm trying to figure out an Excel VBA option for this. I found the following code below and updated it to those locations, however I get a "Compile Error: User-defined type not defined" message.

It initially selects the Dim pptApp As PowerPoint.Application line, but once I hit "OK" it highlights in yellow the Sub EditPowerPointLinks() at the top. I'm very new to VBA so any help would be greatly appreciated! Code below for reference:

Sub EditPowerPointLinks()

'Set the link to the Object Library:
'Tools -> References -> Microsoft PowerPoint x.xx Object Library

Dim oldFilePath As String
Dim newFilePath As String
Dim sourceFileName As String
Dim pptApp As PowerPoint.Application
Dim pptPresentation As Object
Dim pptSlide As Object
Dim pptShape As Object

'The file name and path of the file to update
sourceFileName = "D:\FEENIX\FEENIX Planning\2) PowerPoint Mission Planning.pptx"

'The old file path as a string (the text to be replaced)
oldFilePath = "D:\FEENIX\Mission Planning - START HERE.xlsm"

'The new file path as a string (the text to replace with)
newFilePath = "D:\FEENIX\FEENIX Planning\1) Excel Mission Planning - START HERE.xlsm"

'Set the variable to the PowerPoint Application
Set pptApp = New PowerPoint.Application

'Make the PowerPoint application visible
pptApp.Visible = True

'Set the variable to the PowerPoint Presentation
Set pptPresentation = pptApp.Presentations.Open(sourceFileName)

'Loop through each slide in the presentation
For Each pptSlide In pptPresentation.Slides

'Loop through each shape in each slide
For Each pptShape In pptSlide.Shapes

'Find out if the shape is a linked object or a linked picture
If pptShape.Type = msoLinkedPicture Or pptShape.Type _
= msoLinkedOLEObject Or pptShape.Type = msoLinkedChart Then

'Use Replace to change the oldFilePath to the newFilePath
pptShape.LinkFormat.SourceFullName = Replace(LCase _
(pptShape.LinkFormat.SourceFullName), LCase(oldFilePath), newFilePath)

End If
Next
Next

pptPresentation.UpdateLinks

'Save, close and quit the application
pptPresentation.Save
pptPresentation.Close
pptApp.Quit


'Release the memory
Set pptApp = Nothing
Set pptPresentation = Nothing
Set pptSlide = Nothing
Set pptShape = Nothing

End Sub
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
It doesn't have to be this code, this was just the only one I've been able to find that appears to do what I'm looking for. If there are others that are better/shorter/whatever I'm open to those for sure.
 
Upvote 0
You may have already found the answer elsewhere, but I don't think that the shape type msoLinkedChart is supported in Powerpoint.
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top