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

Edit links in PowerPoint using 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 “\”

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

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

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
Set pptShape = Nothing

End Sub

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

Save



Want to Learn VBA & Macros?

If you want to automate Excel and save time, my recommended resouces for learning VBA Macros are:

23 thoughts on “Edit links in PowerPoint using VBA

  1. Stacey says:

    I am trying to get this to work using Office 16. I am not sure what I am missing but I have tried both the excel method and the ppt method.
    Any suggestions?

    I have 12 spreadsheets with about 30 links each to the same spreadsheet.

    Thank you so much for an insight you can provide.

    ‘The file name and path of the file to update
    sourceFileName = “\\mikewin10\C$\Users\mdavis\Desktop\Budgets\Sales Meeting 2019\A-Jan 2019.pptm\”

    ‘The old file path as a string (the text to be replaced)
    oldFilePath = “\\mikewin10\C$\Users\mdavis\Desktop\Budgets\Sales Meeting 2018\2018 Source.xlsx\”

    ‘The new file path as a string (the text to replace with)
    newFilePath = “\\mikewin10\C$\Users\mdavis\Desktop\Budgets\Sales Meeting 2019\2019 Source.xlsx\”

    • Excel Off The Grid says:

      Hi Stacey,

      Thanks for your question.

      My guess is that you need to remove the \ from the end of the file path. Let me know if that works.

  2. Gadi Bizinyan says:

    Hi Mark, just wanted to tell you that the info you wrote here is priceless! Last week I had to deal with a project wherein a pair of files inter-connected (Excel feeding PowerPoint through linked ranges across 50+ slides) kept changing locations together as a pair. The problem how to keep this pair connected at all times despite path changes as well as the Excel file renaming. The solution was to update all the links of the PowerPoint presentation from within Excel and the technique in my macro was based on the code you introduced. Saved me a lot of time writing it myself from scratch. Brilliant piece of work. Thank you for sharing.

    One comment – The variable pptApp is also an abject and at the end of the code you ought to set it to Nothing together with all the other object variables.

  3. Allen says:

    Hi Mark, I can’t get either the powerpoint macro or the excel macro to run correctly. In both cases I can see the macro running, but no changes take place to the ppt. For example when I run the macro in Excel, I see it open the Powerpoint file, it spins for a while, then closes the Powerpoint. But when I re-open the Powerpoint it’s the exact same as it was. Any thoughts? Thanks!

    • Excel Off The Grid says:

      Hi Allen – I’m guessing that the issues are likely to be either:

      (1) the text string set for the oldFilePath is not the same as the text string in the PowerPoint file.

      (2) the after making the change, the newFilePath is not a valid file. PowerPoint will only update if the path is valid and exists.

      • Allen says:

        Hi Mark, I eventually figured it out. My PPT contained charts, which the code for PPT doesn’t account for. So I just added “Or pptShape.Type = msoLinkedChart” before the ‘Then’ in the section of the code that finds out if the shape is a linked object or a linked picture.

        Thanks for your help and quick reply, very kind of you and very much appreciated.

  4. Aurélie says:

    Hi, Thank you for this ! I am totally new to VBA so perhaps a stupid question but does it also work if a Powerpoint presentation is linked to 3 different excel files ? How should i adapt the code ? thx !! u

    • Excel Off The Grid says:

      The code works based on a Find & Replace of the file path and file name. So it depends specifically on the find text string you’re using as to whether it will match multiple linked files.

  5. Alberto says:

    Hi. I cant make it work. there is always an error
    LinkFormat.SourceFullName : Failed

    sourceFileName = “D:\200 OpRev\OpRevTool\Data Traffic Forecast\Technology OpRev-NSP-Template4.pptx”

    ‘The old file path as a string (the text to be replaced)
    oldFilePath = “D:\200 OpRev\OpRevTool\Data Traffic Forecast\OpRev Data2.xlsm”

    ‘The new file path as a string (the text to replace with)
    newFilePath = “D:\200 OpRev\OpRevTool\Data Traffic Forecast\OpRev Data3.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 Then

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

    End If
    Next

    its stopping on the LinkFormat.SourceFullName

    Kindly advise 🙁 Thanks in advance

    • Excel Off The Grid says:

      Hi Don

      Work through the example in this post. Set up some fake data and follow all the steps. It explains what you need to do.

      If it doesn’t work, then it may be because you’re not using Excel.

  6. Angeles says:

    Hi! Hope you can help me.
    I dont know anything about macros, so I tried to run it and it says (PowerPoint.Application ) User-defined type not defined.

    What could it be?

    Thanks in advance.

    • Excel Off The Grid says:

      Hi Angeles – It’s probably because you have not referenced the PowerPoint Object Library. This is essential when using the Early Binding method – the instructions for this are included at the top of the post.

  7. Baiano42 says:

    Thank you for the post! Looking at the excel VBA version, would it be possible to adapt it where you could have the sourceFileName, oldFilePath and newFilePath variables to source from cells within the excel spreadsheet. For example:

         sourceFileName = Range("E5").Value
         oldFilePath = Range("C5").Value
         newFilePath = Range("B5").Value
    

    and have a macro such as:

        Application.CutCopyMode = False
        Range("B5").Select
        Selection.Copy
        Range("C5").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Range("A5").Select
        Application.CutCopyMode = False
        Selection.Copy
        Range("B5").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Range("D5").Select
        Selection.Copy
        Range("E5").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    

    (This would update the last file location to cell C5, update the new location to B5, and update the current PPT location in E5.)

    Using those adjustments to your macro, I am receiving the following error:
    *Run-time error ‘-2147467259 (80004005)’:
    Method ‘SourceFullName’ of object ‘LinkFormat’ failed*

    and it is highlighting the following as the portion of code:

    pptShape.LinkFormat.SourceFullName = Replace(LCase _
                (pptShape.LinkFormat.SourceFullName), LCase(oldFilePath), newFilePath)
    

    Any suggestions on where I’m going wrong?

    • Excel Off The Grid says:

      The error is appearing in the line of code which changes the file path. The new file must exist, else it will create an error. This is the most likely cause of the error. Use the following to check if the file exists.

      MsgBox Replace(LCase(pptShape.LinkFormat.SourceFullName), LCase(oldFilePath), newFilePath

      Once you’re happy that it works correctly, you can remove that line of code.

      • Baiano42 says:

        Got it to work! The problem was that when I used the Cell values, it would add an [____]Report around the .xlsx file name. To resolve that, I did a Find/Replace to change it to the link that would work for PPT. Thanks a ton for your assistance, you helped me solve a problem I have been having for the past 3 weeks!

  8. Reinaldo Perez says:

    I’m looking for a way to list the links rather than edit or change them. I’ve got PowerPoint files with tons of Excel links that are missing the workbooks and I think it would be great if I could generate a list of the workbook names so I might be able to more easily hunt down the workbooks. Even better if the list could include initials of author, date modified and range. Can the above code be modified to do this instead of editing the links?

    • Excel Off The Grid says:

      Hi Reinaldo – Yes you can modify the code to list the links:

      (1) Create a variable at the start of the macro:

      Dim i as Long

      (2) Change this line of code:

      pptShape.LinkFormat.SourceFullName = Replace(LCase _
      (pptShape.LinkFormat.SourceFullName), LCase(oldFilePath), newFilePath)
      

      To this:

      ActiveSheet.Range("A1").Offset(i, 0) = pptShape.LinkFormat.SourceFullName
      i = i + 1
      

      (3) Remove the following lines:

      pptPresentation.UpdateLinks
      
      pptPresentation.Save
      

      There are a few other lines which are not required now, but they shouldn’t cause you any issues.

      In terms of getting the initials of the author and the date modified. I don’t believe this is stored in the PowerPoint file, so you won’t be able to get this.

      Just for information – you can find a list of all the linked files by clicking File -> Info -> Edit Links to Files (look at the bottom right of the screen).

Leave a Reply

Your email address will not be published. Required fields are marked *