Open Workbook as Read-Only

When working with workbooks using VBA, there may be times when you want to retrieve information from another workbook.

By opening the other workbook as read-only, you can prevent accidental changes to the data while displaying and retrieving the necessary information.

This page introduces how to open a workbook as read-only using VBA.

Open Workbook as Read-Only

To open a workbook as read-only, specify True for the ReadOnly argument of the Workbooks.Open method.

Workbooks.Open(ReadOnly:=True)

The following code is an example of opening sample.xlsx in the user’s Documents folder as read-only.

Dim wb As Workbook, path As String

_ User's_Documents_folder
path = Environ("USERPROFILE") & "\Documents\sample.xlsx"

_ Open_workbook_as_read-only
Set wb = Workbooks.Open(path, ReadOnly:=True)

_ Close_workbook
wb.Close

Notes

When a workbook is opened as read-only, it cannot be saved over the original file. You need to save it with a different name.

If the path is incorrect or you do not have access permissions, the workbook will not open. It is recommended to implement proper error handling.

The following code is an example of closing the workbook if it was already opened when an error occurs.

Public Sub OpenReadOnlyWorkbook()
  On Error GoTo ErrorHandler

  Dim wb As Workbook, path As String

  _ User's_Documents_folder
  path = Environ("USERPROFILE") & "\Documents\sample.xlsx"

  _ Open_workbook_as_read-only
  Set wb = Workbooks.Open(path, ReadOnly:=True)

  _ Some_processing

  _ Close_workbook
  wb.Close

  Exit Sub

ErrorHandler:

    _ If_workbook_was_already_opened,_close_it
    If Not wb Is Nothing Then
        wb.Close
    End If

    MsgBox "An error occurred."

End Sub

Summary

We introduced how to open an Excel workbook as read-only using VBA.

Please refer to the sample code above and customize it according to your needs.