Yes. In Excel for Microsoft 365 on Windows, VBA can ask Excel to open a SharePoint workbook with Workbooks.Open and ReadOnly:=False. That requests an editable opening; it does not override SharePoint permissions, a required checkout, or a lock, so check the opened workbook’s ReadOnly property before making changes.
What “edit mode” means for a SharePoint workbook
Opening a file for editing is not the same as forcing an exclusive lock. Excel asks SharePoint to open the workbook with write access, and SharePoint and the Office sign-in session determine whether the request can be granted. A user with read-only permission cannot gain edit rights through VBA. A library configured to require checkout, a file already checked out by someone else, or an access policy can also prevent editing.
Excel may open the workbook even when it cannot grant write access. The workbook’s ReadOnly property indicates whether the current Excel instance has it open as read-only. Treat that property as the practical check rather than assuming that the absence of an error means the file is editable. Also, an editable opening does not guarantee that a save will succeed later: connectivity or a permission change can affect the save.
Use Workbooks.Open with a direct file URL
For a SharePoint document library, use the workbook’s direct file address, not just the site home page. A typical address has a form like https://contoso.sharepoint.com/sites/Finance/Shared%20Documents/Budget.xlsx. Replace the example tenant, site, library, folder, and file with the actual values. Spaces in a URL path are commonly represented as %20; the slashes between folders remain slashes.
The following macro requests a writable opening, avoids updating external links automatically, and reports if Excel opened the file read-only. Replace the sample URL before running it.
Sub OpenSharePointWorkbookForEditing()
Dim fileUrl As String
Dim wb As Workbook
' Replace this example with the direct SharePoint file URL.
fileUrl = "https://contoso.sharepoint.com/sites/Finance/Shared%20Documents/Budget.xlsx"
On Error GoTo OpenFailed
Set wb = Workbooks.Open(Filename:=fileUrl, _
UpdateLinks:=0, _
ReadOnly:=False, _
Notify:=False, _
AddToMru:=False)
If wb.ReadOnly Then
MsgBox "The workbook opened read-only. Check permissions, checkout, or locks.", vbExclamation
Else
MsgBox "The workbook opened with write access. Save changes with Ctrl+S.", vbInformation
End If
Exit Sub
OpenFailed:
MsgBox "Could not open the SharePoint workbook: " & Err.Description, vbExclamation
End Sub
UpdateLinks:=0 means Excel does not update external references as part of opening; it is not a SharePoint access setting. If the workbook relies on external links, decide separately whether and how those links should be updated. The macro opens the file but does not change or save its contents. Save a copy of important workbooks before running a macro: VBA actions cannot be undone with Ctrl+Z.
Find the right SharePoint address
In SharePoint, browse to the document library and locate the workbook. A link copied with the Share command may be a sharing link, sometimes containing a redirect, token, or query string. Such a link can open a browser page or fail when passed to Excel as a filename. Prefer the actual file path or a direct file URL that Excel desktop can resolve. SharePoint layouts and access policies vary, so there is no single link format that works in every organization.
- Confirm that the address identifies the workbook itself and ends with its real file name and extension.
- Check that the URL points to the right site, library, folder, and file. A similarly named workbook in another library is easy to mistake.
- Do not remove query parameters blindly. First establish whether the copied link is a sharing link or a direct file address, then use the address appropriate for Excel.
- If the URL is long or redirects to a web page, try opening the workbook from a synced library folder instead.
A direct URL may still prompt for sign-in or fail if Excel cannot use the current authentication session. Sign in to the same work or school account in the desktop Office apps that has access to the SharePoint site. If your organization uses multifactor authentication or conditional access, follow its normal sign-in process rather than embedding credentials in a macro.
Use a synced OneDrive folder when the URL is unreliable
A practical alternative is to sync the SharePoint document library with the OneDrive sync app, then open the workbook through the synced folder’s local path. In SharePoint, use the library’s sync option if your organization permits it, wait for the file to appear in File Explorer, and use that path in Workbooks.Open. This often works better for VBA that already uses local file paths, while the sync app handles communication with SharePoint.
A synced path is still a local representation of a cloud file, not a guarantee that changes have reached the server. Check the OneDrive sync status before relying on a recent update, and avoid editing conflicting offline copies. The path can also differ between users and computers, so a macro that hard-codes something such as a particular user’s profile folder may not work for colleagues. For a shared solution, make the path configurable or document the expected synced location.
Do not treat a mapped drive or an old WebDAV-style path as universally reliable. Those methods can depend on Windows configuration, authentication, and organizational policy. A direct URL or a currently synced library is generally easier to explain and maintain.
Permissions, locks, and coauthoring
SharePoint access is controlled separately from VBA. Verify that the signed-in account has permission to edit the file and that the library has not imposed a checkout or approval rule that blocks the intended workflow. If Excel reports read-only access, ask the site owner or administrator to check the file’s permissions and library settings rather than repeatedly changing the macro.
Several people may be able to edit a supported workbook through Excel’s coauthoring features, so “edit mode” does not necessarily mean one person has an exclusive lock. Conversely, another user’s checkout, a legacy locking condition, or a temporary service issue can interfere with editing. Coordinate before a macro makes large changes, especially if other people may be editing the same workbook.
VBA runs in desktop Excel, not Excel for the web. A workbook stored in SharePoint can be opened in desktop Excel to run a macro, subject to the file format and organization’s macro policies. The web app does not execute VBA. A macro should also not assume that other collaborators will see its changes instantly; allow Excel and the sync or coauthoring process to finish saving and updating.
Check the result and handle errors responsibly
The example checks wb.ReadOnly immediately after opening. If it is True, do not proceed with code that assumes it can write changes. Report the situation, close without saving if appropriate, and resolve the access issue. If it is False, Excel has opened the workbook for writing, but the macro should still handle save errors and avoid closing a workbook that another part of the user’s session needs.
For production automation, consider checking whether the workbook is already open in the same Excel instance before opening another copy. Also decide what the macro should do if the file cannot be reached: stop with a clear message, retry only under a deliberate policy, or use an approved local workflow. Avoid silently saving a different workbook or a local duplicate under the SharePoint file’s name; that can create confusing versions and overwrite the wrong content.
On Windows, Ctrl+S saves the active workbook. On Mac, use Command+S. The exact SharePoint URL and authentication experience can differ on Mac, so a library synced by OneDrive may be the more practical route there. Excel for the web has no VBA execution equivalent; use desktop Excel for this macro.
Security and macro considerations
Only run VBA from a source you trust. A macro that opens a workbook can expose information to code in that workbook or in the macro project, and a downloaded or shared workbook may carry unsafe content. Do not put passwords, access tokens, or other secrets into the URL or the VBA code. Let Office and your organization’s authentication controls manage access.
Excel’s macro policy may block the procedure or require a trusted location or approved signature. On Windows, macro options are under File > Options > Trust Center > Trust Center Settings > Macro Settings, although an administrator may control these settings. Do not enable all macros simply to make one script run. Save a backup copy before running code that edits a workbook, because VBA changes cannot be reversed with Ctrl+Z.
Common problems and what to try
- The macro says it cannot open the file: verify the direct URL, spelling, encoded spaces, network access, and Office sign-in. Try opening the same file manually in desktop Excel.
- The workbook opens read-only: confirm edit permission, checkout status, library rules, and whether another process or user is holding a restriction. The
ReadOnlycheck is the signal to stop write operations. - The browser opens instead of Excel: the supplied address may be a sharing or web-view link rather than a file address. Try a direct library path or the synced folder.
- The local copy looks out of date: check OneDrive sync status and connectivity before editing. A local path does not prove that the latest cloud version has downloaded.
- The macro is blocked: use the organization-approved macro process and confirm that the code is trusted; do not lower security settings broadly.
Frequently Asked Questions
Can VBA force a SharePoint workbook to open in edit mode?
No. ReadOnly:=False requests write access from Excel, but SharePoint permissions, checkout requirements, locks, and policies decide whether it is granted. Check Workbook.ReadOnly after opening.
Does Workbooks.Open work with a SharePoint URL?
It can work with an accessible direct file URL when Excel desktop can authenticate to the site. Sharing links and redirect URLs may not work as a filename. If the URL route fails, try the file in a synced OneDrive library.
Why does my SharePoint workbook open as read-only?
Common reasons include read-only permission, a required checkout, another user’s checkout, a lock, or an authentication or policy issue. VBA cannot bypass these restrictions; check the account and ask the site owner about library settings.
Can Excel for the web run this VBA macro?
No. Excel for the web does not run VBA macros. Open the SharePoint workbook in the Excel desktop app to use VBA, assuming macros are allowed by your organization.
Is a synced SharePoint file safe to edit with VBA?
It can be a useful route, but confirm that the file is synced and that you are editing the intended copy. Sync delays or conflicts can matter, and VBA still cannot grant permissions that the signed-in account lacks.
Related
- Excel Custom Number Format: Show Numbers in Thousands
- Excel Shortcut to Select the Rest of a Column Down
- Excel Shortcut to Select an Entire Column
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Sort: How to Sort Data with a MacroTo sort a worksheet range with Excel VBA, define the full range, add one or more sort keys,…
- Excel VBA String to Number: Safe Conversion MethodsIn Excel VBA, convert text that represents a number with a conversion function such as CDbl for decimals…
- VBA Range Variables: How to Declare and Use ThemA VBA Range variable is an object variable that refers to one or more cells in an Excel…
- Close MsgBox VBA: Reliable Ways to Dismiss a DialogTo close a VBA MsgBox, click one of its buttons or use a keyboard action the dialog supports;…
- Excel VBA AutoFilter: Exclude Multiple ValuesExcel VBA AutoFilter cannot use an array of “not equal to” criteria as a general exclusion list. An…
- Excel Macro Filter: How to Automate Data FilteringTo filter a worksheet with a macro, use VBA’s AutoFilter method on a range that includes its header…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.