Fix VBA Failing to Update Excel Links in PowerPoint on OneDrive or SharePoint
Question details
The user needs to programmatically update linked Excel ranges in a PowerPoint presentation using VBA when the source files are hosted on OneDrive or SharePoint.
- Product
- Microsoft PowerPoint and Excel
- Device & OS
- not provided
- Scenario
- Running a VBA macro to change the LinkFormat.SourceFullName property to a new cloud-based file path.
- Observed behavior
- The VBA script fails to update the link for files stored on OneDrive or SharePoint, even though the same links update correctly through the standard PowerPoint graphical interface.
Ensure you have a stable internet connection and are actively logged into your Microsoft account with proper permissions for the specific SharePoint or OneDrive directory.
Use Authenticated Cloud URLs Instead of Local Paths
Ensure Office is properly authenticated to SharePoint and use the exact, cleaned HTTP/HTTPS URL for the VBA link source.
PowerPoint VBA may silently reject cloud paths if the user is not fully authenticated during runtime or if the path includes incompatible web parameters. Standard local syncing paths may also fail for shared cloud documents.
Open PowerPoint, click on 'File', then 'Account'. Ensure you are signed in with the user profile that has direct access to the required OneDrive or SharePoint repository.
Open your source Excel file in the Excel desktop application. Go to 'File' > 'Info' and click 'Copy Path' to retrieve the absolute SharePoint or OneDrive URL.
Paste the copied URL into a text editor. Remove any trailing web parameters, such as '?web=1', so the path ends directly with the file extension (e.g., '.xlsx').
Replace the file path in your PowerPoint VBA script for 'LinkFormat.SourceFullName' with this cleaned URL and run the macro again.
Simplify Presentation Linking with WPS Office
If dealing with VBA cloud authentication and SharePoint linking becomes too complex, try WPS Office. It provides an intuitive, reliable way to link spreadsheets to presentations without the steep troubleshooting curves, all within a lightweight and free suite.
- 1. Download and Install: Get WPS Office from the official website and install the suite on your computer.
- 2. Open Your Files: Launch WPS Presentation and WPS Spreadsheet to open your existing Office documents seamlessly.
- 3. Link Data Easily: Copy your spreadsheet data, go to your presentation, and use 'Paste Special' > 'Paste Link' to maintain a dynamic connection.
Frequently Asked Questions
Why do manual updates work but VBA fails for SharePoint links?
Manual updates via the PowerPoint interface often leverage cached credentials and Microsoft's built-in file picker dialogs, whereas VBA interacts directly with the path string. If the path string lacks authentication or isn't formatted perfectly, VBA will silently reject it.
Can I use my local synced OneDrive path in PowerPoint VBA?
Yes, but it is not recommended for files shared with other users. A local synced path (like C:\Users\Name\OneDrive) is specific to your computer. Other users running the macro will encounter an error unless their local environment is identically structured.
How do I check if my SharePoint path is correct for VBA?
A correct SharePoint path for VBA must start with 'http://' or 'https://' and end exactly with the file extension (.xlsx or .xls). Ensure all spaces are either preserved correctly or encoded as '%20' depending on your macro's string handling.
