Excel VBA: How to Open an Excel File as Read-Only

VBA & macros · Updated November 19, 2026

To open an Excel workbook as read-only with VBA, pass ReadOnly:=True to Workbooks.Open. For example, Set sourceBook = Workbooks.Open(Filename:=filePath, ReadOnly:=True) opens the file in Excel without allowing changes to be saved back to that original workbook.

Open a workbook with the ReadOnly argument

The Workbooks.Open method opens a workbook and returns a Workbook object. Assigning that result to a variable gives your macro a reliable way to refer to the workbook it just opened. In the example below, change the path to an existing Excel file on your computer.

Sub OpenWorkbookReadOnly()
    Dim filePath As String
    Dim sourceBook As Workbook

    ' Set this to the full path of the workbook to open.
    filePath = "C:\Reports\Quarterly.xlsx"

    ' Open the source file without write access.
    Set sourceBook = Workbooks.Open(Filename:=filePath, ReadOnly:=True)

    MsgBox "Opened read-only: " & sourceBook.Name
End Sub

Run the macro from desktop Excel for Windows, and the workbook should open in read-only mode. You can still select cells, copy data, use formulas, and inspect sheets. Read-only mode limits saving changes to the opened source file; it does not make the workbook a static preview. The workbook remains open in Excel until you close it.

Before running any macro, save a copy of your workbook. VBA actions cannot be undone with Ctrl+Z, so a backup is useful if the macro is later changed or expanded to edit data.

What ReadOnly:=True actually does

ReadOnly:=True is a named argument in the Workbooks.Open method. It tells Excel to open the workbook in read-only mode instead of requesting normal write access. The argument applies to the workbook being opened, not automatically to every workbook in the Excel session.

Read-only is an Excel access mode, not a password or a security boundary. It helps prevent accidental edits from being saved to the source file through that open workbook. It does not encrypt the file, restrict copying, or stop another person or process from changing the file if they have permission to do so. For sensitive information, use file permissions and appropriate protection rather than relying on a VBA argument.

Excel may also open a workbook as read-only for reasons unrelated to your macro, such as file permissions, another user’s lock, or a read-only file attribute. That is why it is useful to check the resulting workbook’s status rather than assume every open operation succeeded exactly as intended.

Useful Workbooks.Open arguments

The ReadOnly argument is often enough, but other optional arguments can make automated opening more predictable. For example, when a workbook contains external links, Excel may ask whether to update them. If the macro should open the file without updating external references, use UpdateLinks:=0 as well:

Sub OpenWithoutUpdatingLinks()
    Dim filePath As String
    Dim sourceBook As Workbook

    ' Use the full path to the source workbook.
    filePath = "C:\Reports\Quarterly.xlsx"

    ' Open read-only and do not refresh external links.
    Set sourceBook = Workbooks.Open( _
        Filename:=filePath, _
        UpdateLinks:=0, _
        ReadOnly:=True)
End Sub

In this method, UpdateLinks:=0 means external references are not updated when the workbook opens. It does not remove links or guarantee that displayed values are current; the workbook may show values saved the last time its links were updated. If current linked data matters, decide deliberately whether links should refresh and whether Excel should be allowed to prompt.

Other arguments include IgnoreReadOnlyRecommended, which controls whether Excel displays a recommendation prompt, and AddToMru, which controls whether the file is added to the recently used list. These arguments do not replace ReadOnly:=True. In particular, ignoring a recommendation is not the same as enforcing read-only access.

Use a reliable file path

The filename must point to a workbook Excel can access. A full Windows path is usually clearest, such as C:\Reports\Quarterly.xlsx. In VBA strings, the backslash is written normally; it does not need to be doubled. Confirm the folder, filename, extension, and spelling, especially if the path is assembled from a cell or user input.

For a workbook stored beside the macro workbook, you can build a path from ThisWorkbook.Path, which refers to the workbook containing the code. This differs from ActiveWorkbook, which means the workbook currently active in the Excel window and may change while the macro runs. If the macro workbook has not been saved, its path may be empty, so a path built from it will not point to the expected folder.

Network shares, synced folders, and cloud locations can have additional access or synchronization conditions. A file that appears in File Explorer may not be available to Excel under the account or permissions running the macro. For a cloud-synced workbook, make sure the file is available locally or use a supported, accessible path; do not assume a web sharing link is interchangeable with a local file path.

Read-only mode versus a read-only recommendation

Excel has more than one feature that uses the phrase “read-only.” A workbook can be marked as read-only recommended, prompting a person to open it that way, while still allowing write access if the person chooses to edit. The IgnoreReadOnlyRecommended argument can suppress that recommendation prompt, but it does not itself make the workbook read-only.

By contrast, ReadOnly:=True requests read-only access for the macro’s open operation. A workbook may also be read-only because the operating system or a server grants only read permission, or because another user has it open in a way that prevents shared write access. Those situations can affect what Excel allows even if the macro did not request read-only mode.

A write-reservation password is another distinct case: it is intended to control who can open a workbook for editing. Do not confuse that with file encryption or a password required to open the workbook at all. If a workbook requires an open password, the macro may need the appropriate password argument, and that password should be handled securely rather than embedded as plain text in widely shared code.

Check that Excel opened the workbook read-only

After opening, inspect the returned workbook’s ReadOnly property. It is a Boolean value: True means Excel has the workbook open read-only, and False means it is not currently in that mode. This check is useful when your automation must decide whether to continue, display a message, or close the workbook.

One important edge case is a workbook that is already open in the same Excel instance. Excel generally uses the existing open workbook rather than creating a second independent copy of the same file in that instance. If it was already opened with write access, a later call requesting read-only may not turn that existing workbook into a separate read-only copy. Check whether the workbook is already open and inspect its status, or close it before opening it again with the desired mode.

For workflows that must never write to the source, treat the status check as a condition to verify, not as a substitute for a backup or sound permissions. If the status is not what the process requires, stop rather than continuing with steps that might alter data.

Common errors and troubleshooting

The most frequent problem is a path or filename error. If the specified file cannot be found, check the full path and extension and make sure the file has not been moved. A file that is already open elsewhere, unavailable on a network, or blocked by permissions can also produce a prompt or an error. The exact result depends on the file location, sharing arrangement, and Excel settings.

  • “Subscript out of range” or a file-open error: Confirm that the path is valid and that the workbook exists and is accessible.
  • A prompt appears unexpectedly: Check for a password, external links, a read-only recommendation, or a file already in use. Use only the relevant Workbooks.Open arguments; suppressing a prompt does not fix its underlying cause.
  • The workbook opens, but the macro edits something else: Refer to the variable assigned from Workbooks.Open, such as sourceBook, instead of relying on ActiveWorkbook.
  • The workbook was already open: Check the existing workbook’s ReadOnly property, or close it before reopening it in the requested mode.
  • The macro does not run: VBA macros run in desktop Excel, not Excel for the web. Macro security settings may also block code from running.

A macro can use error handling to show a more helpful message when a file cannot be opened, but it should not silently ignore the failure and continue as though the workbook were available. For a production process, record which path was attempted and present a clear message that helps the user correct the problem.

Saving changes and making an editable copy

Read-only mode is appropriate when the source is a reference file or when a process should inspect or copy data without changing the original. If you edit cells in a read-only workbook, Excel cannot save those edits back to that same source in the ordinary way. To keep changes, use Save As and create a new file in a location where you have write permission. Choose a different name or folder so the original is not replaced accidentally.

If your goal is to edit a working copy, a common approach is to open the source read-only and then save a copy under a new filename before making changes. The exact workflow should be planned carefully: saving a copy creates a separate file, and later changes to that copy do not update the original. A read-only open also does not guarantee that the source remains unchanged by other users while your copy is being prepared.

For a macro that only reads values, use the workbook variable to qualify references, for example sourceBook.Worksheets("Data").Range("A1"). Explicit references are safer than relying on whichever sheet or workbook happens to be active. If the macro writes to another workbook, qualify that destination separately so the source and destination are not confused.

Windows, Mac, and Excel for the web

This VBA approach applies to desktop Excel for Microsoft 365 on Windows. Excel for Mac also supports VBA and the Workbooks.Open method, but paths differ: Mac paths may use a POSIX form such as /Users/name/Documents/Quarterly.xlsx, or a Mac-compatible path available to that installation. Verify the exact path and permissions on the Mac rather than copying a Windows drive path unchanged.

Excel for the web does not run VBA macros. A workbook may be opened in a browser, but a VBA procedure must be run in a desktop version of Excel. Windows shortcuts and ribbon locations are therefore not a replacement for running the macro in the desktop app. The macro itself does not require a special ribbon command if it is run from the VBA editor or assigned to a suitable control.

Frequently Asked Questions

How do I open an Excel file as read-only in VBA? Use Workbooks.Open(Filename:=filePath, ReadOnly:=True), where filePath contains the workbook’s accessible full path. Store the returned workbook in a variable so you can refer to it directly.

Does ReadOnly:=True stop other people from editing the file? No. It requests read-only mode for your Excel session. It does not change file permissions or prevent another person with write access from editing the file.

Can I save changes made to a read-only workbook? Not back to the same source in the normal way. Use Save As to create a separate editable copy, and choose a new filename or location if you need to preserve the original.

What is the difference between ReadOnly and IgnoreReadOnlyRecommended? ReadOnly:=True requests read-only access. IgnoreReadOnlyRecommended:=True suppresses a recommendation prompt; it does not enforce read-only mode by itself.

Why did VBA open the workbook as editable anyway? The workbook may already have been open in the same Excel instance, or Excel may have returned an existing workbook rather than opening another copy. Check the workbook’s ReadOnly property and close the existing instance before reopening if necessary.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.