Use Excel VBA to Access Files Stored in SharePoint Online
Question details
The user needs to configure Excel macros to read from and write to Access and Excel files hosted on SharePoint Online instead of relying on local Windows file paths.
- Product
- Microsoft Excel, SharePoint Online
- Device & OS
- not provided
- Scenario
- Automating data read/write processes across Excel and Access files that have been migrated to a cloud-based SharePoint Online environment.
- Observed behavior
- Existing macros depend on local Windows paths and fail or require updating to properly handle SharePoint Online URLs for data operations.
Ensure you have the appropriate site permissions to access the SharePoint Online files and that you are logged into Excel with your organizational Microsoft account.
Access SharePoint Files via Direct URL Path
Obtain the direct web path of the SharePoint file to pull data directly into Excel without writing complex API authentication code.
This method is ideal for directly referencing a specific workbook or dataset stored in SharePoint. By tweaking the URL provided by the Excel desktop app, you can seamlessly connect to cloud files using standard data connections.
Navigate to SharePoint and open the target Excel file in the Excel desktop application.
Click on 'File' in the top-left corner, select 'Info', and click the 'Copy Path' button to copy the file's web URL.
Paste the copied URL into a text editor. Locate the '?web=1' suffix at the very end of the link and delete it, leaving just the raw file path.
In your working Excel file, go to 'Data' > 'Get Data' > 'From Web'. Paste the modified URL into the dialog box and authenticate using your organizational account when prompted.
Automate File Operations Using SharePoint REST APIs
Utilize SharePoint REST endpoints within VBA for advanced, dynamic read and write operations on cloud-stored files and folders.
Need a Lightweight, Macro-Enabled Alternative to Microsoft Office?
While highly specific cloud integrations like SharePoint REST APIs rely heavily on the Microsoft ecosystem, everyday data automation doesn't have to be complicated. WPS Office offers a powerful, lightweight alternative with robust macro support and excellent format compatibility.
Frequently Asked Questions
Why does my copied SharePoint file path end with '?web=1'?
The '?web=1' parameter tells your system to open the document in the Excel web application inside a browser. Removing it allows Excel VBA and Power Query to target the actual underlying file for data extraction.
Can I use synced local mapped drives instead of SharePoint URLs in VBA?
Yes, you can sync SharePoint libraries to a local folder using OneDrive and use standard local Windows file paths in your VBA code. However, this method may break if you share the macro with other users whose OneDrive sync paths (like C:\Users\[Username]\...) differ from yours.
How do I authenticate SharePoint REST API calls in Excel VBA?
Authenticating REST APIs in VBA usually involves registering an application in Microsoft Entra ID (Azure AD) to acquire an OAuth 2.0 access token. This token is then passed in the header of your VBA XMLHTTP requests as a 'Bearer' token.
Does WPS Office support VBA macros?
Yes, WPS Office provides excellent support for VBA macros. You can run, edit, and create VBA scripts seamlessly in WPS Spreadsheets, making it a great alternative for macro-driven workflows.
