This post may contain affiliate links. Please read my disclosure for more info.
Advertisement:

Edit links in PowerPoint using VBA

Edit links in PowerPoint using excel VBA

Edit links in PowerPoint using VBA

In a previous post, we considered how to link Excel files to PowerPoint presentations.  This can be a significant time saver as the PowerPoint presentation can be updated automatically.  Once you have created the links, life is brilliant, until . . . you need to change which file the PowerPoint presentation is linked to.  You could use the “edit links” option.  But there is one big issue with PowerPoint, you have to edit the links one by one.  If you have 100 links to different Excel workbooks you have to update each of them.  If you have 100 links to the same Excel workbook you would expect to be able to update them all at the same time.  But that’s just not how it works unfortunately, you still have to update each link individually.

Have no fear – VBA to the rescue.  Below you will find VBA code for PowerPoint and for Excel.  Yes, that’s right, you can update the links to a PowerPoint document from within Excel.  Pretty cool eh?

Here are a few notes for using the code:

  • The Excel code has been written using Early Binding.  Don’t worry if you don’t know what that means.  In the Excel Visual Basic Editor select Tools -> References -> Microsoft PowerPoint x.xx Object Library.  This will ensure that can Excel and speak PowerPoint.
  • When changing file paths remember to end the file path with a slash “\”

Advertisement:

 

Edit the linked documents using PowerPoint VBA

Open the Visual Basic Editor in PowerPoint (ALT+F11).

Right-click on the VBAProject, select Insert -> Module.

PowerPoint VBA insert Module


Advertisement:

Copy the code below into the code window.

PowerPoint VBA copy code

Sub EditPowerPointLinks()

Dim oldFilePath As String
Dim newFilePath As String
Dim pptPresentation As Presentation
Dim pptSlide As Slide
Dim pptShape As Shape

'The old file path as a string (the text to be replaced)
oldFilePath = "String of\File Path\To Be Replaced\Excel File.xlsx"

'The new file path as a string (the text to replace with)
newFilePath = "String of\New File Path\Excel File 2.xlsx"

'Set the variable to the PowerPoint Presentation
Set pptPresentation = ActivePresentation

'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 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

'Update the links
pptPresentation.UpdateLinks


End Sub

You just need to set the values for the newFilePath and oldFilePath variables listed in the code above.

 

Edit the linked documents from Excel using VBA


Advertisement:

It is also possible to update the links in PowerPoint from within Excel.

Press ALT + F11 to open the Excel Visual Basic Editor.  Click Insert -> Module. Copy the code below into the new Module.

The code below uses Early Binding, so you will need to set the references to the PowerPoint object library.  Select Tools -> References, then select the Microsoft PowerPoint x.xx Object Library from the list.

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 = "C:\File Path\Of Source File\File Name.pptx"

'The old file path as a string (the text to be replaced)
oldFilePath = "String of\File Path\To Be Replaced\Excel File.xlsx"

'The new file path as a string (the text to replace with)
newFilePath = "String of\New File Path\Excel File2.xlsx"

'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 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

End Sub

For the code above it will be necessary to set the values for the sourceFileName, oldFilePath and newFilePath variables.

Save