To open a SharePoint workbook with VBA, the most dependable approach is to sync its document library with OneDrive and pass the resulting local file path to Workbooks.Open. You can also try the workbook’s direct SharePoint file URL, but sign-in, link format, and organization policies can make that method less reliable.
How SharePoint files differ from local files
A workbook stored in SharePoint Online is held in a document library, not in an ordinary Windows folder. Excel for Microsoft 365 can connect to that library using your signed-in Microsoft 365 account, but VBA still needs a usable file location to give to the Workbooks.Open method. That location might be a synchronized local path or, in some circumstances, a direct HTTPS address.
This distinction explains why a link that opens in a browser may not work as a VBA filename. A sharing link can redirect through a sign-in or preview page, and it may include parameters that describe how the browser should display the file. It is not necessarily the same as the address of the workbook itself. Use a path that identifies the actual .xlsx or .xlsm file, not a generic SharePoint page or a link to a folder.
The examples below are for desktop Excel for Microsoft 365 on Windows. Excel for the web cannot run VBA macros. On a Mac, VBA can open a file from a OneDrive-synchronized folder if you use the actual Mac file path, but the location and URL behavior may differ.
Most reliable method: sync the SharePoint library
For a recurring macro, syncing the library with OneDrive is usually the easiest route to maintain. In SharePoint, open the document library containing the workbook and choose Sync. Follow the prompts to open or sign in to OneDrive. When synchronization is set up, the library appears in File Explorer under your organization’s OneDrive location. Depending on your organization and sync setup, the path may resemble C:\Users\Avery\OneDrive - Example Company\Project Documents\Reports\Sales.xlsx.
Use the exact path shown on the computer that runs the macro. Do not assume every user has the same Windows username, organization label, library name, or folder structure. If different staff members will run the code, consider asking them to select the file or store the location in a configurable cell or setting rather than hard-coding one person’s path.
- In File Explorer, browse to the synchronized library and find the workbook.
- Use the file’s full path in VBA. In File Explorer, you can select the address bar to inspect the folder path, then add the workbook’s filename.
- Make sure the account running Excel has permission to the SharePoint library and that OneDrive is signed in.
- If the file is online-only, ensure the computer has network access; choose the OneDrive option to keep the file available offline if the macro must work without a connection.
OneDrive Files On-Demand can show a file in File Explorer without keeping all its contents downloaded at all times. Opening an online-only file may cause OneDrive to retrieve it, but that depends on connectivity and sync state. For unattended automation or time-sensitive work, making the workbook available offline and confirming that synchronization has completed can reduce surprises.
VBA example for a synchronized workbook
This example checks that the local file is present, then opens it read-only and does not update external links. Replace the sample path with the full path on the computer that runs the macro. Read-only mode is useful when the macro only needs to inspect data; remove that argument only if the process genuinely needs to edit and save the source workbook.
Before running a macro, save a separate copy of any workbook it may change. VBA actions cannot be undone with Ctrl+Z, and an error in a macro can alter or overwrite data.
Sub OpenSyncedSharePointWorkbook()
Dim filePath As String
Dim wb As Workbook
' Replace this with the synchronized file's full local path.
filePath = "C:\Users\Avery\OneDrive - Example Company\Project Documents\Reports\Sales.xlsx"
If Len(Dir$(filePath)) = 0 Then
MsgBox "The workbook was not found. Check the path and sync status.", vbExclamation
Exit Sub
End If
On Error GoTo OpenFailed
Set wb = Workbooks.Open(Filename:=filePath, UpdateLinks:=0, ReadOnly:=True)
Exit Sub
OpenFailed:
MsgBox "Excel could not open the workbook: " & Err.Description, vbExclamation
End Sub
The code opens the workbook in the same Excel instance as the macro. The wb variable refers to the newly opened workbook, so later code can refer to it explicitly—for example, to read a worksheet or close the file. If the path is wrong, the file is not synced, or access is denied, the check or Excel’s open operation will fail rather than silently selecting a different file.
Opening a direct SharePoint file URL
A direct HTTPS file address can work in desktop Excel when the signed-in account can access the site and the address points to the workbook itself. A typical shape is https://tenant.sharepoint.com/sites/Team/Shared%20Documents/Reports/Sales.xlsx. The tenant, site, library, folders, and filename in that example are illustrative; use the real address for your organization. Spaces in URL paths are commonly represented as %20.
To find a candidate address, navigate to the file in SharePoint and use the option that opens it in the desktop app or view its file details. Avoid pasting a “Copy link” sharing URL without checking its form. If it contains a redirect or a query string such as ?web=1, it may be a browser-oriented link rather than a direct file path. Removing a query string is not a universal fix: only use the resulting address if it is genuinely the workbook’s direct URL and your organization permits access that way.
Here is the basic VBA pattern. Replace the sample with the direct URL, not a folder address or browser page. Excel may display a sign-in prompt, or it may fail if the URL format, authentication flow, or tenant policy is incompatible with opening the file through VBA.
Sub OpenSharePointFileByUrl()
Dim fileUrl As String
Dim wb As Workbook
' Use the direct HTTPS address of the workbook.
fileUrl = "https://tenant.sharepoint.com/sites/Team/Shared%20Documents/Reports/Sales.xlsx"
On Error GoTo OpenFailed
Set wb = Workbooks.Open(Filename:=fileUrl, UpdateLinks:=0, ReadOnly:=True)
Exit Sub
OpenFailed:
MsgBox "Excel could not open the SharePoint URL: " & Err.Description, vbExclamation
End Sub
Do not put a username, password, or access token into the URL or macro. Modern Microsoft 365 sign-in uses security controls that should not be bypassed by embedding credentials. If direct URL opening prompts repeatedly or returns an access error, use the synchronized-folder approach or ask your Microsoft 365 administrator about the organization’s approved access method.
Choosing between a synced path and a URL
The best choice depends on where the macro runs and how stable the file location is. A synchronized path behaves much like a normal file path, which makes it straightforward for VBA and easier to troubleshoot. Its tradeoff is that it depends on OneDrive being configured for the Windows user and the library being synchronized at the expected location.
- Use a synced path when a person runs the macro in desktop Excel and OneDrive is set up on that computer. This is generally the practical choice for a workbook used repeatedly.
- Try a direct URL when you have a true file URL, desktop Excel is already signed in, and your organization’s SharePoint configuration allows it. Test it with a noncritical workbook first.
- Use a file picker or configurable setting when users have different sync paths. This avoids assuming everyone has the same Windows profile or library location.
- Do not treat a sharing link as a guaranteed VBA path. A link designed for a browser or a particular recipient may not behave like a direct workbook address.
For a macro used by a team, the path strategy is part of deployment, not just a coding detail. Document which library users must sync, confirm they have access, and decide how the macro should respond if the file is missing. If automation must run on a server without a signed-in desktop user, a simple Workbooks.Open macro is often the wrong architecture; have an administrator select a supported, policy-compliant automation method rather than storing credentials in VBA.
Common errors and practical fixes
“File not found” for a synced path: Check spelling, spaces, the library name, and the file extension. Confirm that the library is still synchronized and that the Windows account running Excel is the account that has access. Paths can change if a user resets OneDrive, changes organizations, or renames a local folder.
Access denied or repeated sign-in prompts: Open the site in a browser using the same work account, confirm that the account can open the workbook, then check Excel’s signed-in account. The browser and Office desktop app can have different active accounts. Your administrator may also restrict downloads, desktop-app access, or particular authentication methods.
The browser opens, but VBA does not: The browser may follow a redirect or interpret a sharing link that Excel’s file-opening method does not handle in the same way. Try syncing the library and opening its local path. A successful browser test confirms that the user can reach something at that link; it does not prove that the URL is a direct workbook filename suitable for VBA.
The file exists but will not open: Check that it is a supported Excel workbook, that it is not still being uploaded or synchronized, and that no dialog is waiting behind another window. A workbook already open by another user may be available read-only or may show a conflict, depending on how it is accessed. Avoid repeatedly launching the macro while OneDrive is still syncing.
It works on one computer but not another: Compare the actual local paths, OneDrive accounts, permissions, and Excel sign-in state. Do not copy a coworker’s full path and assume it will resolve on your machine. For shared code, let each user configure the path or select the file, and provide a clear message when the expected workbook cannot be found.
Security and workbook-handling considerations
Only open SharePoint files from sites and users you trust. A workbook can contain formulas, links, data connections, and macros of its own; opening a workbook is not the same as verifying that its contents are safe. The examples use ReadOnly:=True and UpdateLinks:=0 to avoid editing the source file and to avoid automatically updating external workbook links, but these options do not certify the file or replace your organization’s security controls.
Macros may be blocked by Microsoft 365 security settings or organizational policy. Use a trusted, approved location and follow your organization’s rules; do not lower macro security or enable content just to make an unknown file run. If the macro will write changes, consider opening a working copy or saving output to a separate file, and make the intended behavior clear before running it. A read-only open is a sensible default when the task is simply to inspect or copy data.
Frequently Asked Questions
Can VBA open a SharePoint Excel file directly?
Sometimes. Desktop Excel can open a direct SharePoint file URL when the account, URL format, and organization settings allow it. A browser sharing link is not always a direct file URL. If direct opening fails, sync the library with OneDrive and use the workbook’s local path.
How do I get the local path of a SharePoint workbook?
Sync the document library with OneDrive, then locate the workbook in File Explorer. Use its full path, including folders and the filename. The path is specific to that computer and user, so do not assume another person’s local path will match yours.
Will this VBA work in Excel for the web?
No. Excel for the web does not run VBA macros. Run the macro in desktop Excel for Windows or Mac. On a Mac, use the path to the locally synchronized file and account for differences in the Mac folder path.
Why does my SharePoint sharing link fail in Workbooks.Open?
Sharing links may lead to a preview page, include redirect information, or require an authentication flow that Workbooks.Open cannot use as a file path. Find the direct workbook address if your setup supports it, or use a OneDrive-synchronized path instead.
Can I open the workbook without syncing the library?
You can try a direct HTTPS file URL in desktop Excel, but whether it works depends on the URL and Microsoft 365 configuration. If it is unreliable, syncing is the simpler alternative. For unattended or service-based automation, use an administrator-approved approach rather than embedding credentials in VBA.
Related
- RSVP List Template Excel: Build and Track Guest Replies
- Room List Template Excel: How to Build and Use One
- Resource List Excel Template: Set Up, Track, and Share
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel and Access VBA: What It Is and How to Use ItExcel and Access VBA (Visual Basic for Applications) is a programming language built into Microsoft Office that lets…
- VBA in Excel: Open the Editor with a Keyboard ShortcutTo open the VBA editor in Excel for Microsoft 365 on Windows, press Alt+F11. This opens the Visual…
- How to Copy and Paste Formulas with VBA in Excel SafelyTo copy a formula with VBA in Excel, copy its source cell and use PasteSpecial Paste:=xlPasteFormulas on the…
- VBA Array Contains: How to Check for a Value in ExcelA VBA array contains multiple values stored under one variable name, with each value reached by an index…
- Excel VBA: Highlight Cells in an Array LoopTo highlight worksheet cells while looping through an array in Excel VBA, read a range into an array,…
- VBA InputBox Yes/No Questions: Use MsgBox CorrectlyVBA’s standard InputBox cannot display Yes and No buttons; it collects text instead. To ask a true yes-or-no…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.