VBA & Macro Problems

How to Update Excel VBA Code for SharePoint Online File Paths

Maira Mehtab Sep 28, 2026 869 views

Question details

The user needs to update the file paths within an Excel VBA macro to reflect a new SharePoint Online environment after migrating away from a local network drive.

Product: Microsoft Excel
Device & OS: not provided
Scenario: A macro-enabled workbook was moved from a local network drive to SharePoint Online, breaking the file paths referenced in the code.
Observed behavior: The VBA code continues to use the old read-only network path, resulting in runtime error 75 or 1004 when the macro is executed.

Before you start

Ensure that your local OneDrive sync client is up to date and that you have the appropriate edit permissions for the SharePoint document library where the file resides.

Solution 1Recommended

Use Local Synchronized OneDrive Paths in VBA

The most reliable method to fix SharePoint path issues is to sync the SharePoint library to your local machine and update the VBA code to use environment variables to build the dynamic local path.

Standard VBA functions like 'Dir' or 'FileCopy' do not natively support HTTPS web paths. By syncing the SharePoint library via OneDrive, you can reference a standard local C: drive path.

Using environment variables ensures the macro works across different computers where the username in the local path might vary.

Sync the SharePoint Library

Navigate to the SharePoint Online document library in your web browser and click the 'Sync' button to map the library to your local Windows File Explorer.

Open the VBA Editor

Open the macro-enabled workbook in the Excel desktop application and press ALT + F11 to launch the Visual Basic for Applications (VBA) editor.

Locate the Old Path

Search your VBA modules for the old hardcoded network drive path that is causing the runtime error.

Update to the Dynamic Sync Path

Replace the old string with a dynamic local path using the user's environment variable. For example: Environ("UserProfile") & "\CompanyName\SharePointFolder\FileName.xlsx".

Save and Test

Save your code changes, close the VBA editor, and run the macro to verify that runtime error 75 or 1004 is resolved.

Dynamic Paths: Using the 'Environ("UserProfile")' function prevents the macro from breaking when shared with colleagues who have different Windows usernames.

Solution 2

Modify VBA Code to Use Direct SharePoint HTTPS URLs

If local syncing is not an option, you can update the VBA code to point directly to the SharePoint HTTPS URL, provided you use supported objects like MSXML2.XMLHTTP for web requests.

Free Microsoft Office alternative

Manage Macros and Spreadsheets Effortlessly with WPS Office

If you are encountering persistent macro errors or compatibility issues with your current setup, consider switching to WPS Office. It is a highly compatible, free, and lightweight alternative to Microsoft Office that provides robust VBA support, a familiar interface, and seamless migration for your macro-enabled workbooks.

1. Download WPS Office: Visit the official WPS website and download the free WPS Office suite.
2. Open Your Workbook: Launch WPS Spreadsheet and open your existing .xlsm macro-enabled file directly.
3. Enable Macros: Click 'Enable Macros' in the security warning prompt to seamlessly run your VBA scripts.

Fully compatible with Microsoft Excel file formats (.xlsx, .xls, .xlsm)Robust built-in VBA support for running and editing complex macrosLightweight application that uses minimal system resourcesFamiliar ribbon interface ensuring a seamless transition with zero learning curve

microsoft office alternative - wps office

Frequently Asked Questions

Why do I get runtime error 1004 when running a macro from SharePoint?

Runtime error 1004 generally occurs because the VBA code is referencing an outdated local network path that no longer exists, or the SharePoint file path is being treated as read-only and cannot be modified by the script.

Can standard VBA commands like 'Dir' or 'FileCopy' use SharePoint web URLs?

No, standard VBA file system commands do not natively support HTTPS web paths. To use these functions, you must sync the SharePoint document library locally using OneDrive and reference the synchronized local path.

How do I find the correct synchronized local path for my SharePoint folder?

Open Windows File Explorer, navigate to the synced SharePoint folder (usually located under your user profile directory named after your organization), and click the address bar to copy the exact local file path.

Excel & Data Problems