How to Move OneDrive Excel Files Without Breaking Links and Macros
Question details
The user needs to move interconnected Excel workbooks stored on OneDrive without breaking their data links and associated VBA macros.
- Product
- Microsoft Excel, OneDrive
- Device & OS
- not provided
- Scenario
- Moving or reorganizing an entire folder containing linked Excel workbooks and macro-enabled files within or out of OneDrive.
- Observed behavior
- While relative data links may survive if the parent folder structure is maintained, absolute file paths and fixed directory locations within VBA macros typically break and result in reference errors after the move.
Ensure your OneDrive client is completely synced to your local computer and that all interconnected Excel workbooks are fully closed before initiating the file transfer.
Move the Entire Folder and Manually Update Absolute Paths
By syncing locally and moving the complete parent folder, you can preserve relative links. Hard-coded absolute paths will still require manual updates.
Excel uses relative paths for linked workbooks as long as the workbooks remain in the same relative folder structure. However, macros and certain complex links may rely on absolute file paths, which will inevitably break when the root folder changes.
Open your local File Explorer and ensure the source OneDrive folder is fully downloaded and synced to your computer.
Select the complete parent folder containing all linked workbooks. Copy and paste the entire folder to the new destination to preserve the relative folder structure.
Open the moved Excel workbooks. Go to the 'Data' tab and click 'Edit Links' (or 'Workbook Links' in newer versions) to verify that the external data references are still active. If any show an error, click 'Change Source' to map them to the new folder.
Press Alt + F11 to open the VBA Editor. Search through your modules for any hard-coded, absolute file paths (e.g., 'C:\Users\...\OneDrive\') and manually edit them to reflect the new directory location.
Manage Linked Spreadsheets and Macros with WPS Office
WPS Spreadsheet fully supports complex external workbook links and VBA macros. If you have moved your files and need to quickly repair broken connections, you can easily update data links and edit macro paths directly within WPS Office.
- 1. Open the Linked Workbook: Launch WPS Spreadsheet and open the file you recently moved from OneDrive.
- 2. Update External Data Links: Navigate to the 'Data' tab on the top ribbon and click 'Edit Links'. Select any broken links from the list and use 'Change Source' to browse for the files in their new location.
- 3. Edit VBA Code: Go to the 'Developer' tab and click 'Visual Basic Editor' (or press Alt + F11). Find your macros and replace the old file paths with the new directory path.
Frequently Asked Questions
Why do my Excel links change to absolute URLs in OneDrive?
When saving files in OneDrive, Office sometimes converts local file paths to absolute SharePoint or OneDrive URLs (e.g., https://d.docs.live.net/...). This can cause links to break if the files are moved outside of the OneDrive environment or opened by someone without network access.
How can I automatically find all broken links in my Excel file?
You can find broken links by navigating to the 'Data' tab and clicking on 'Edit Links'. The dialog box will list all external sources. Click 'Check Status' to immediately see which links are broken and require updating.
Will relative links break if I rename the parent folder?
As long as both the source and destination workbooks remain inside the same parent folder and their structural relationship to one another doesn't change, renaming the parent folder generally will not break relative links.
Can I prevent macros from breaking when moving folders?
Yes, you can make your macros dynamic. Instead of using a fixed absolute path like 'C:\Folder\File.xlsx', use VBA relative path methods such as 'ThisWorkbook.Path & "\File.xlsx"'. This allows the macro to dynamically find the file as long as it stays in the same folder as the workbook running the code.
