VBA & Macro Problems

How to Run Excel Macros and BI Tools After SharePoint Migration

Muhammad TalhaMuhammad Talha Nov 3, 2026 868 views

Question details

The user needs to ensure Excel VBA macros and BI tools can access, save, and load files after migrating from a local server to SharePoint, without syncing massive amounts of data to local storage.

How to Run Excel Macros and BI Tools After SharePoint Migration
Product
Microsoft Excel / SharePoint
Device & OS
not provided
Scenario
A company is migrating files from a local server to SharePoint and needs to retain the functionality of desktop VBA macros and BI tools that rely on specific folder paths.
Observed behavior
Excel for the Web does not support VBA macros, requiring users to rely on desktop applications. However, standard synchronization of cloud directories consumes excessive local disk space.
Before you start

Ensure you have the latest version of the OneDrive desktop client installed and are signed in with your company SharePoint account.

Solution 1Recommended

Use OneDrive Sync Client with Files On-Demand

This method allows your desktop Excel application to run macros using local file paths without permanently downloading all SharePoint files to your local hard drive.

Excel for the web does not support running VBA macros. To execute VBA code and allow BI tools to save or load files, you must use the desktop version of Excel and sync the SharePoint folders to your computer.

To avoid consuming excessive local storage, you can utilize the 'Files On-Demand' feature. This keeps files online-only until they are specifically opened or requested by a macro or BI tool.

1
Sync the SharePoint Library

Navigate to your company's SharePoint document library in a web browser and click the 'Sync' button to connect it to your local OneDrive desktop client.

2
Enable Files On-Demand

Open your OneDrive settings from the system tray, navigate to the Sync and Backup tab (or Settings tab in older versions), and ensure that 'Save space and download files as you use them' (Files On-Demand) is enabled.

3
Update Macro File Paths

Open your Excel VBA editor and update your macro code to point to the new local OneDrive sync directory (e.g., C:\Users\[Username]\[Company Name]\[SharePoint Folder]) instead of the old local server path.

4
Free Up Local Space

After your macros or BI tools have finished processing, right-click the synced folder in File Explorer and select 'Free up space'. This removes the downloaded copies from your local drive while keeping them safely stored in SharePoint.

Exclude Unnecessary Folders: If you only need specific directories for your macros, you can exclude irrelevant folders entirely by going to OneDrive Settings > Account > Choose folders.
Free Microsoft Office alternative

Try WPS Office for Your Spreadsheet and Data Needs

If you are managing complex data workflows and looking for a highly compatible, cost-effective office suite, WPS Office provides excellent performance for processing large datasets.

  1. 1. Download and Install: Visit the official WPS website to download and install the free WPS Office suite on your device.
  2. 2. Open Your Spreadsheets: Launch WPS Spreadsheets and open your existing Excel workbooks. The familiar interface ensures you can get to work immediately.
  3. 3. Process Your Data: Utilize the built-in data processing features to manage your large files and business intelligence datasets locally.
Free and lightweight alternative to Microsoft OfficeHigh compatibility with Microsoft Excel formats including .xlsx and .xlsmFamiliar user interface for seamless migrationEfficient handling of large datasets and spreadsheets
QA img-9

Frequently Asked Questions

Can I run Excel VBA macros directly in SharePoint or Excel for the Web?

No, Excel for the Web does not support running VBA macros. You must open the file in the desktop version of Excel to execute any VBA code.

How do I update my macro file paths after moving to SharePoint?

You will need to manually change the file paths in your VBA code from the old local server mapped drives to the new OneDrive sync directory on your local machine.

Will running macros on online-only files download them to my computer?

Yes. When a macro or BI tool accesses an online-only file through the synced folder, OneDrive automatically downloads it to your local storage to perform the requested operation.

How can I reclaim disk space after my BI tools finish processing synced files?

Open File Explorer, locate your synced SharePoint folder, right-click it, and select 'Free up space'. This will delete the local cached copies but keep the files accessible in the cloud.