VBA & Macro Problems

How to Run an Excel VBA Macro with SharePoint Online Files

Maira Mehtab Sep 27, 2026 870 views

Question details

The user needs to adapt an Excel workbook containing VBA macros that read other workbooks, changing hard-coded local paths to work with SharePoint Online.

Product: Microsoft Excel / SharePoint Online
Device & OS: not provided
Scenario: Migrating an Excel workbook with VBA macros from an on-premises server to SharePoint Online.
Observed behavior: The existing VBA macro fails to run because it relies on hard-coded local or on-premises file paths that cannot directly access SharePoint Online files.

Before you start

Ensure you have active internet connectivity and the necessary permissions to access the target SharePoint Online folder. It is also recommended to back up your original VBA code before modifying any file paths.

Solution 1Recommended

Use OneDrive Sync for SharePoint Files

Syncing the SharePoint library locally via OneDrive allows VBA to interact with the files using a standard local file path, bypassing complex URL authentication.

The easiest way to make legacy VBA code work with SharePoint Online is to map the cloud drive to your local machine. This allows you to treat SharePoint folders just like standard Windows folders in your VBA script.

Sync the SharePoint Library

Navigate to your SharePoint Online document library in a web browser and click the 'Sync' button at the top to connect it with your local OneDrive client.

Open the VBA Editor

Open your Excel workbook and press ALT + F11 to launch the Visual Basic for Applications (VBA) editor.

Locate the Path Variables

Find the lines in your macro where the hard-coded on-premises paths are defined.

Update to the Local Synced Path

Replace the old path with the new local synced path (e.g., C:\Users\YourName\Company Name\SharePoint Library).

Dynamic Path Tip: Use Environ("USERPROFILE") in your VBA code to dynamically construct the path, ensuring the macro works for multiple users without needing to hard-code specific usernames.

Solution 2

Update Code to Direct SharePoint Online URLs

Update the VBA code to use the direct HTTPS URL of the SharePoint file if you prefer not to rely on local file synchronization.

Solution 3

Consult Specialized VBA Forums for Complex Logic

If your macro involves advanced API calls or custom authentication layers, seeking help from specialized communities can provide tailored solutions.

Advanced Macro Support

Manage Macros and Workbooks Easily with WPS Office

WPS Office provides robust support for VBA macros, allowing you to seamlessly run customized scripts, edit code, and manage workbooks synced from cloud storage solutions like SharePoint and OneDrive.

1. Download and Install WPS Office: Get the latest version of WPS Office and install it on your device.
2. Open Your Macro-Enabled Workbook: Launch WPS Spreadsheet and open your .xlsm file containing the VBA code.
3. Access the Developer Tools: Navigate to the 'Developer' tab on the ribbon and click 'Macros' or 'Visual Basic' to review your script.
4. Run Your Updated Macro: After updating any hard-coded paths to your synced SharePoint folders, run the script natively within WPS Spreadsheet.

Full compatibility with Microsoft Excel (.xlsx, .xlsm, .xls) file formats.Built-in VBA editor for modifying and executing complex macros.Seamless integration with locally synced cloud folders.Lightweight, fast, and highly cost-effective Office solution.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my VBA macro fail when using a direct SharePoint HTTPS link?

Excel VBA sometimes struggles with direct SharePoint authentication or requires strict URL encoding. Replacing spaces with '%20' and removing the '?web=1' parameter from the end of the copied URL often resolves basic file-opening issues.

Can I make a synced SharePoint path work for multiple users in VBA?

Yes, you can use the Environ("USERPROFILE") function in your VBA code to dynamically construct the base directory. For example: Environ("USERPROFILE") & "\Company Name\SharePoint Library\file.xlsx". This ensures the macro adapts to whoever is currently logged into Windows.

Does WPS Office support running VBA macros?

Yes, WPS Office includes a comprehensive VBA environment. You can open macro-enabled files, edit code in the Visual Basic Editor, and execute macros just as you would in standard Microsoft Excel.

Excel & Data Problems