How to Repair Broken Excel Links in OneDrive and SharePoint
Question details
Users need to repair broken workbook links or incorrect relative paths in Excel after migrating interconnected files to OneDrive or SharePoint.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Moving interconnected Excel workbooks to cloud-synchronized folders like OneDrive or SharePoint.
- Observed behavior
- Linked values fail to update automatically, links change to broken relative paths, and VBA update scripts may throw errors upon the first connection attempt.
Ensure you have the appropriate access permissions for both the source and destination workbooks in SharePoint or OneDrive, and verify that the source workbooks are currently synced.
Use the Edit Links Tool to Update Values Manually
The most reliable method to re-establish the SharePoint or OneDrive connection and fix relative paths is to manually update the link values using Excel's built-in tool.
When workbooks are moved to a cloud environment, Excel sometimes fails to automatically establish the necessary background connection to update external values. Manually prompting the update forces Excel to reach out to the SharePoint server.
Open your destination workbook in Excel. Go to the 'Data' tab on the ribbon and click on 'Edit Links' in the Queries & Connections group.
In the Edit Links dialog box, identify and select the source workbook that contains the broken links or incorrect relative paths.
Click the 'Update Values' button on the right side. This action will force Excel to establish the SharePoint connection and refresh the cell data.
Implement a VBA Macro to Automate Link Updates
You can automate the link update process using a VBA macro, which is particularly useful for workbooks with multiple external data sources.
Manage External Workbook Links Seamlessly in WPS Spreadsheet
WPS Office provides an intuitive Data tab to manage, update, and repair external workbook links efficiently. You can easily fix broken paths when migrating interconnected files to the cloud.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open your interconnected spreadsheet containing the external links.
- 2. Navigate to the Data tab: Click on the 'Data' tab in the top ribbon and locate the 'Edit Links' button.
- 3. Update the link sources: Select the broken external links from the list and click 'Update Values' to seamlessly refresh the data from the cloud source file.
Frequently Asked Questions
Why do Excel links break or change to relative paths in SharePoint?
When files are moved to SharePoint or OneDrive, their file paths change from local drive letters (e.g., C:\) to web-based URLs (https://). Excel attempts to convert these to relative paths, which can break the connection if the exact folder structure isn't perfectly maintained.
Can I prevent OneDrive from changing absolute links to relative links?
Yes. In Excel, go to File > Options > Advanced. Scroll down to the 'Web Options' button under the General section, switch to the 'Files' tab, and uncheck 'Update links on save'. This helps preserve the absolute paths.
Why does my VBA macro fail to update links on the first try in OneDrive?
Excel often takes a moment to authenticate and establish a connection to the SharePoint or OneDrive server. If the VBA macro runs immediately upon opening the workbook, the connection may not be ready yet, causing the script to throw a runtime error.
