To open an Excel workbook as read-only with VBA, use Workbooks.Open and set its ReadOnly argument to True. For example, Set sourceWb = Workbooks.Open(Filename:=filePath, ReadOnly:=True) opens the file without granting the session permission to save changes back to that original workbook.
What read-only means in Excel
Read-only is an opening mode, not a special file format. Excel opens the workbook so your VBA code can inspect its worksheets, cells, names, and other contents, while preventing the session from saving changes over the original file. The workbook remains useful as a data source: a macro can read values from it and use them in another workbook.
Read-only does not necessarily mean that every cell is frozen on screen. Excel may let someone make temporary edits during the session, but those edits cannot be saved back to the same source file in the normal way. A user who wants to keep changes can generally use Save As to create a separate copy, subject to permissions and file restrictions. This is different from worksheet protection, which limits particular editing actions, and from file permissions, which are enforced by Windows or a storage service.
Opening a source read-only is useful when a macro only needs to import or analyze data, when the file is shared, or when avoiding accidental edits matters. It also reduces the chance that the macro will claim an exclusive editing lock on a file that another person needs to update.
The Workbooks.Open argument
Workbooks.Open is Excel VBA’s method for opening an existing workbook. Its named arguments let you specify the file path and control how Excel opens it. Setting ReadOnly to True requests read-only access for that opening; it does not change the file’s permanent attributes or make the file read-only for everyone.
The result of the method is a Workbook object. Assigning that result to a variable gives the macro a reliable reference to the opened workbook, so it can read from the correct file even if other workbooks are already open. The Workbook object also exposes a ReadOnly property that reports whether Excel opened that particular workbook read-only.
Use a full path when the macro must open a specific file. A path such as C:\Reports\Sales.xlsx is an example for Windows; substitute the real folder and filename. A relative filename may depend on Excel’s current folder, which can differ from the folder containing the macro workbook.
A complete VBA example
This example checks that the file exists, opens it without updating external links, and displays the workbook’s name. Before running a macro, save a copy of any workbook the macro might change: macros cannot be undone with Ctrl+Z. This example only opens and identifies the source workbook, but the precaution is useful when extending it.
Sub OpenSourceReadOnly()
Dim filePath As String
Dim sourceWb As Workbook
' Replace this example path with the workbook you want to read.
filePath = "C:\Reports\Sales.xlsx"
If Len(Dir(filePath)) = 0 Then
MsgBox "File not found: " & filePath
Exit Sub
End If
On Error GoTo OpenError
' Open as read-only and do not update external links.
Set sourceWb = Workbooks.Open( _
Filename:=filePath, _
UpdateLinks:=0, _
ReadOnly:=True, _
IgnoreReadOnlyRecommended:=True, _
AddToMru:=False)
MsgBox "Opened " & sourceWb.Name & _
"; read-only status: " & sourceWb.ReadOnly
Exit Sub
OpenError:
MsgBox "Excel could not open the workbook: " & Err.Description
End Sub
The error handler gives a message if Excel cannot open the file—for example, because the path is wrong, the file is unavailable, or a password is required. In a larger automation project, you may want the error handler to log the problem or close any workbooks the macro opened. This short example leaves a successfully opened workbook open so you can inspect it.
Choosing the other opening options
The example includes optional arguments that are useful in some workflows. They do not replace ReadOnly:=True; each controls a different part of the opening process.
UpdateLinks:=0tells Excel not to update external workbook links as it opens the file. This avoids link-update prompts and changes caused by fetching linked data. Omit or change this argument if the macro is supposed to refresh links, and consider how the source workbook’s formulas depend on those links.IgnoreReadOnlyRecommended:=Truesuppresses the workbook author’s “read-only recommended” prompt. It does not itself force read-only access. The separateReadOnly:=Trueargument is what requests read-only mode.AddToMru:=Falseprevents the opened file from being added to Excel’s recent-file list. Remove the argument or set it toTrueif you want the file included there.
If you only need the basic behavior, the shorter call is sufficient: provide the filename and set ReadOnly:=True. Add other arguments only when their effects are appropriate for the task. In particular, do not disable link updates automatically if the macro relies on fresh linked values.
Reading data from the opened workbook
Once the workbook is open, use the Workbook variable to refer to its worksheets and ranges. For example, a macro can read a cell with an expression such as sourceWb.Worksheets("Data").Range("A1").Value. The sheet name must match the tab name in the source file. If the sheet might not exist, add error handling rather than assuming that every workbook has the same layout.
Keeping a reference to the source workbook is safer than relying on whichever workbook happens to be active. Code that uses ActiveWorkbook can point at the wrong file if another workbook becomes active while the macro runs. Use the object variable for all source references, and close the workbook when the macro has finished reading it if the user does not need to keep it open.
Closing without saving is appropriate when the macro has only read data or has made temporary edits that should be discarded. For a workbook variable named sourceWb, the relevant method is sourceWb.Close SaveChanges:=False. If the macro needs to preserve output, write it to a separate destination workbook or file instead of trying to overwrite the read-only source.
Read-only mode versus “read-only recommended”
These settings are often confused. A workbook can be saved with a recommendation that users open it read-only, but the recommendation is a prompt, not a restriction. A user can choose to edit the workbook if the file and folder permissions allow it. VBA’s IgnoreReadOnlyRecommended argument only controls whether that prompt is ignored when opening the file.
By contrast, ReadOnly:=True asks Excel to open the workbook in read-only mode for the current session. Neither setting is a security mechanism. If the goal is to prevent users from changing or viewing sensitive information, use appropriate access controls and protection; do not rely on a VBA opening option as a substitute.
Worksheet protection is different again: it can restrict editing of locked cells or worksheet structure, but does not necessarily stop a user from opening the workbook or copying data. Windows file permissions and service permissions control access at the storage level. Choose the control that matches the actual requirement.
Common problems and how to resolve them
- File not found: Check the complete path, extension, spelling, and folder permissions. A file in a mapped network drive may not be available under the same drive letter when a macro runs under another account or process.
- Password prompt: Read-only mode does not bypass workbook passwords or encryption. If the file requires a password to open, Excel still needs the correct password. Do not put sensitive passwords in an exposed macro.
- External-link prompts or stale values: The example uses
UpdateLinks:=0, so linked workbooks are not refreshed during opening. Decide whether the task needs current linked values, and handle any required update or prompt deliberately. - Another user has the file open: A read-only opening avoids requesting write access, but network locations and cloud storage can have their own locking and coauthoring behavior. The exact prompt or status can depend on where the workbook is stored and how Excel accesses it.
- It still appears possible to edit: Read-only mode is about saving to the original file, not necessarily disabling every on-screen edit. Check
sourceWb.ReadOnlyin the macro and use Save As if a separate edited copy is intended. - Macros or Protected View: A workbook’s security settings, file origin, or organization policy may limit its contents or trigger Protected View. Read-only mode does not override Office security warnings or trust settings.
When debugging, inspect the exact value of the path and the error description from VBA rather than assuming the read-only argument failed. A file-access error and a read-only status are separate issues: Excel can be unable to open a file at all, or it can open it successfully but without write access.
Windows, Mac, and saving behavior
The Workbooks.Open method and its read-only argument are part of Excel VBA on both Windows and Mac. The main practical difference in a simple example is often the path format: Windows commonly uses paths such as C:\Reports\Sales.xlsx, while a Mac file path typically uses a location under /Users/. Confirm the path format and access permissions for the machine where the macro will run.
If you try to save changes from a read-only session, Excel will not save them over the original file through that session. Use Save As to create a separate copy if you intend to retain those edits, and confirm that the destination is writable. The usual save shortcut is Ctrl+S on Windows and Command+S on Mac, but a shortcut cannot grant permission to overwrite a read-only source.
Read-only is a sensible safeguard against accidental overwrites, but it is not a backup. If a macro writes results into another workbook, save that destination deliberately and verify that the output is correct. Keep an untouched copy of important source data according to your normal file-management process.
Frequently Asked Questions
What is the VBA syntax to open an Excel file as read-only?
Use Workbooks.Open(Filename:=filePath, ReadOnly:=True), where filePath contains the workbook’s full path. Assign the result to a Workbook variable when the macro will read from it.
Does ReadOnly:=True prevent all edits?
No. Excel may allow temporary edits in the open session, but the read-only session cannot save them over the original workbook. Use Save As to retain a separate copy, if permitted.
What is the difference between ReadOnly and IgnoreReadOnlyRecommended?ReadOnly:=True requests read-only access. IgnoreReadOnlyRecommended:=True only suppresses a recommendation prompt saved with the workbook; it does not enforce read-only mode.
How can I check whether VBA opened the file read-only?
Check the opened Workbook object’s ReadOnly property. It returns True when Excel has that workbook open as read-only.
Can VBA open a password-protected workbook as read-only?
Read-only mode does not bypass a password required to open a workbook. Excel still requires the appropriate credentials, and a macro should not expose passwords in code that other users can inspect.
Can I use this with an Excel file on Mac?
Yes. Excel for Mac supports VBA’s workbook-opening method, but use a path that is valid on that Mac and ensure the account running Excel can access the file.
Related
- Sample Customer List Excel: Fields, Template, and Setup
- Sales Order Tracking Excel Template: Setup and Tips
- RSVP List Template Excel: Build and Track Guest Replies
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Get Workbook Path in VBA: FullName vs PathTo get a workbook’s folder in VBA, read its Path property; to get the complete location including the…
- VBA Loop Through Dictionary Items: Keys, Values, and TipsTo loop through a VBA dictionary’s entries, use For Each on its keys, then retrieve each associated value…
- VBA Range.Hidden: How to Check Hidden Rows and ColumnsIn Excel VBA, Range.Hidden checks or sets whether entire rows or columns are hidden; it does not hide…
- Excel VBA AutoFilter: Exclude Multiple ValuesExcel VBA AutoFilter cannot use an array of “not equal to” criteria as a general exclusion list. An…
- Excel VBA: Find the Last Used Row in Any WorksheetTo find the last row containing content anywhere on a worksheet in Excel VBA, use Range.Find with SearchOrder:=xlByRows…
- Excel VBA Dictionary Count: What .Count ReturnsIn Excel VBA, get the number of entries in a Dictionary with its .Count property: use dict.Count. It…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.