How to Update Linked Excel Objects in PowerPoint Using VBA

If you are working with PowerPoint presentations that contain linked Excel objects, updating them can sometimes prove to be a tricky task. However, with the use of VBA (Visual Basic for Applications), this process can be made much simpler and more efficient. In this article, we will walk you through the steps to update linked Excel objects in a PowerPoint presentation using VBA.

Step-by-Step Guide

Step 1: Open the PowerPoint Presentation

First, open the PowerPoint presentation that contains the linked Excel objects that you wish to update.

Step 2: Access the VBA Editor

To access the VBA editor, press ALT F11. This will open the VBA editor window where you can write and run VBA code.

Step 3: Insert a New Module

Right-click on any of the objects in the Project Explorer window, then select Insert > Module. This will create a new module where you can write your VBA code.

Step 4: Write the VBA Code

Below is a sample VBA code snippet that will update all linked Excel objects in the active PowerPoint presentation.

Sub UpdateLinkedExcelObjects()        Dim slide As slide    Dim shape As shape    'Loop through each slide in the active presentation    For Each slide In         'Loop through each shape in the slide        For Each shape In             'Check if the shape is a linked OLE object            If shape.Type  msoLinkedOLEObject Then                'Update the linked object                            End If        Next shape    Next slide    MsgBox "All linked Excel objects have been updated."End Sub

In this code:

The outer loop iterates through each slide in the active presentation. The inner loop checks each shape on the slide. The code checks if the shape is a linked OLE object, which is how Excel objects are typically linked. It calls the Refresh method on the OLEFormat property of the shape to refresh it.

Step 5: Running the Code

After entering the code, you can run it by pressing F5 while in the VBA editor or by creating a button in PowerPoint and assigning this macro to it.

Note

Make sure that the linked Excel files are accessible as the update will fail if the file is moved or deleted.

Alternative Approach

Alex Reed provided an alternative code snippet that differs slightly from the previous method. This code also runs without needing to open or close the linked workbooks in order to refresh the PowerPoint slide. Here’s the code:

Sub UpdateLinks()    Dim PPTSlide As slide    Dim PPTShape As shape    Dim SourceFile As String    Dim Position As Integer    'Declare Excel Variables    Dim xlApp As Object    Dim xlWrkBook As Object    'Create a new instance of Excel    Set xlApp  New     xlApp.DisplayAlerts  False    'Loop through the slides in the presentation    For Each PPTSlide In         'Loop through each shape in the slide        For Each PPTShape In             'If the shape is a linked object, continue            If PPTShape.Type  msoLinkedOLEObject Then                'Get the linked source name                SourceFile                  'Parse the Source file name                Position  InStr(SourceFile, " - LINK")                SourceFile  Left(SourceFile, Position - 1)                'Open the associated Excel Workbook                Set xlWrkBook  (SourceFile, False, True)                'Update the link                                'Close the workbook and erase it from memory                 SaveChanges:False                Set xlWrkBook  Nothing            End If        Next    NextEnd Sub

In this code:

It declares variables for PowerPoint and Excel objects. It opens a new instance of Excel and sets DisplayAlerts to False to prevent any alert messages from appearing. It loops through each slide and shape in the presentation. For each linked shape, it gets the linked source name, parses it, and opens the associated Excel workbook. It refreshes the link and then closes the workbook, releasing it from memory.

Note

This method should be used with caution, as opening a workbook manually may cause it to run any attached Workbook_Open event code, potentially altering the source file. The previous method avoids this risk by not directly interacting with the workbook.

Conclusion

Using VBA to update linked Excel objects in PowerPoint can save you a significant amount of time and effort. The code snippets provided in this article can be tailored to your specific needs. Whether you choose the simpler method or the more comprehensive one, the key is to ensure that the linked files are accessible to avoid any errors during the update process.