Data Backup Problems

How to Automatically Copy an Excel OneDrive File to a Network Drive

Kushani Nimanthika Oct 14, 2026 870 views

Question details

The user needs a method to automatically save a backup copy of a OneDrive-hosted Excel file to a corporate network folder, replacing the older backup copy each time.

How to Automatically Copy an Excel OneDrive File to a Network Drive

Product: Microsoft Excel
Device & OS: not provided
Scenario: Creating an automated backup of a shared or AutoSaved OneDrive workbook onto a local corporate network drive without triggering sync conflicts.
Observed behavior: Using a standard SaveCopyAs VBA macro often fails or causes unexpected behavior because it runs while OneDrive's AutoSave is actively syncing changes.

Before you start

Verify that your corporate network drive is consistently mapped and accessible, and be aware that VBA macros can interact unpredictably with OneDrive's continuous AutoSave feature.

Solution 1Recommended

Use the Workbook_BeforeClose VBA Event

Trigger the copy operation only when the workbook is being closed to minimize simultaneous save conflicts with OneDrive AutoSave.

Running a SaveCopyAs macro during active editing often clashes with OneDrive's background syncing, especially if multiple users are co-authoring the file. Moving the macro to the BeforeClose event ensures the copy is generated when the user finishes their session.

Open the VBA Editor

Open your Excel file in the desktop application and press Alt + F11 to launch the Microsoft Visual Basic for Applications editor.

Access ThisWorkbook

In the Project Explorer pane on the left, locate your workbook's name and double-click on 'ThisWorkbook'.

Insert the BeforeClose Code

Select 'Workbook' from the left dropdown and 'BeforeClose' from the right dropdown. Insert your ActiveWorkbook.SaveCopyAs code here, specifying your corporate network drive path.

Add Error Handling

Wrap your code in standard VBA error handling (e.g., On Error GoTo) to check if the network path is available before attempting the copy. Save the file as a Macro-Enabled Workbook (.xlsm).

Co-authoring Limitations: Keep in mind that VBA macros do not run in Excel for the Web. If a user closes the web version, the BeforeClose event will not trigger.

Solution 2

Rely on OneDrive Version History

Use built-in versioning instead of VBA macros for a safer, conflict-free way to restore previous file states.

Free Microsoft Office alternative

Use WPS Office for Conflict-Free File Management

If VBA conflicts with Microsoft OneDrive AutoSave are causing constant backup failures, try WPS Office. It provides a lightweight, highly compatible spreadsheet editor that gives you full control over when and where your files are saved, including seamless local network drive saving and robust VBA support.

1. Download WPS Office: Visit the official WPS Office website to download the free suite and install it on your device.
2. Open Your Spreadsheets: Launch WPS Spreadsheets and open your existing .xlsx or .xlsm files with perfect format retention.
3. Manage Local Backups Easily: Use WPS's flexible save options and VBA support to securely back up files to your corporate network drives.

100% compatible with Microsoft Excel formats (.xlsx, .xls, .xlsm)Reliable local saving and network drive support without forced AutoSave conflictsBuilt-in VBA macro support for advanced enterprise automationLightweight, fast, and completely free to use

microsoft office alternative - wps office

Frequently Asked Questions

Why does my VBA SaveCopyAs macro fail when using OneDrive?

OneDrive's AutoSave frequently syncs files in the background. If a VBA macro attempts to copy the file at the exact moment OneDrive is locking it to sync changes, Excel will throw a runtime error or create an incomplete copy.

Can I use Power Automate instead of VBA for network backups?

Yes, Microsoft Power Automate is often a more reliable solution for cloud-hosted files. You can set up an automated flow that triggers whenever the file is modified to copy it to another SharePoint site, OneDrive folder, or an on-premises data gateway connected to your network.

Does the Workbook_BeforeClose event work if users edit in the browser?

No, VBA macros are not supported in Excel for the Web. The Workbook_BeforeClose event will only execute for users who have opened the workbook using the desktop Excel application.

Migration & Recovery