How to Open Workbook as Read-Only with Excel VBA

Naturally, we may need to open the file from a parent Excel file using VBA as a read-only form. For the reason of keeping the original file intact. If you are curious to know how you can open a workbook as read-only using VBA in Excel, then this article may come in handy for you. In this article, we discuss how you can open a workbook as read-only using VBA in Excel with elaborate explanations.


Download Practice Workbook

Download this practice workbook below.


4 Easy Ways to Open Workbook as Read-Only with Excel VBA

We are going to use the below dataset for demonstration purposes. We got the product information for different types of products and their cost, revenue, profit, etc. how this workbook containing this valuable information could be open as read-only will be presented here detailed explanation.

excel vba open workbook read only


1. Directly Open Workbook as Read-Only

In this VBA code method, we are going to enter the file path directly into the VBA code. Users need to extract the file location beforehand using the code.

Steps

  • To begin, first, go to the Developer tab and click on Visual Basic. If you don’t have that, you have to enable the Developer tab, or you can also press ‘Alt+F11’ to open the Visual Basic Editor.

Directly Open Workbook as Read-Only

  • Then there will be a new dialog box. In that dialog box, click on Insert > Module.

insert module

  • Next, in the Module editor window, type the following code
Sub File_Open_Directly()
Dim wrkbk As Workbook
Dim filepath As String
filepath = "C:\Users\USER\Desktop\VBA Code"
Set wrkbk = Workbooks.Open(Filename:=filepath, ReadOnly:=True)
End Sub

  • Then close the Module window.
  • After that, go to View tab > Macros.
  • Then click on View Macros

view macros to open excel vba file

  • After clicking View Macros, select the macros that you created just now. The name here is File_Open_Directly. Then click Run.

  • Then you will notice the excel file in the designated location is now opened as read-only.
  • We can also notice the read-only notice on the right side of the file name.
  • So the file indeed opened as read-only.

excel file open with the read only extension.

🔎 Breakdown of the Code

Sub File_Open_Directly()

⮚ First, we provide a name for the sub-procedure which is File_Open_Directly.

Dim wrkbk As Workbook

⮚ Then, we set the workbook name as the Workbook variable.

Dim filepath As String

⮚ Then, we declare the file path as a String variable.

filepath = “C:\Users\USER\Desktop\VBA Code”

⮚ Right after that, set the filepath variable with your desired file location path.

Set wrkbk = Workbooks.Open(Filename:=filepath, ReadOnly:=True)

⮚ Then, we open the file in the file_path directory variable and set the ReadOnly as True.

End Sub

⮚ Finally, we end the sub-procedure of this code.

Read More: How to Open Folder and Select File Using Excel VBA (4 Examples)


2. Open Workbook as Read-Only Using Dialog Box with Excel VBA

Compared to the previous method, users need not remember the code or extract the file location beforehand. They can extract the location by simply selecting the file directly through the file explorer window.

Steps

  • First, go to the Developer tab and click on Visual Basic. If you don’t have that, you have to enable the Developer tab. Or you can also press ‘Alt+F11‘ to open the Visual Basic Editor.

Open Workbook as Read-Only Using Dialog Box

  • Then there will be a new dialog box. In that dialog box, click on Insert > Module.

inserting vba module

  • Next, in the Module editor window, type the following code.
Sub File_Open_Through_Dialog_Box()
Dim File_Explorer As Office.FileDialog
Dim selection_item As String
Set File_Explorer = Application.FileDialog(msoFileDialogFilePicker)
With File_Explorer
.Filters.Clear
.Filters.Add "Excel File type", "*.xlsx?", 1
.Title = "Choose your file"
.AllowMultiSelect = False
.InitialFileName = "C:\Desktop"
If .Show = True Then
selection_item = .SelectedItems(1)
End If
Set book = Workbooks.Open(Filename:=selection_item, ReadOnly:=True)
End With
End Sub

  • Then close the Module window.
  • After that, go to View tab > Macros.
  • Then click on View Macros.

view macros to choose file

  • After clicking View Macros, select the macros that you created just now. The name here is File_Open_Through_Dialog_Box. Then click Run.

  • After clicking Run, we can see that the file open dialog box is now open.
  • From that dialog box, we need to select the file that we intend to open as a read-only.
  • Select the file and click Open.

selection of excel workbook file to open as read only

  • After that, the file is now open as read-only.
  • We can see that the file now has a read-only tag on the right side of the file name.
  • Observing the tag, we can therefore confirm that the file is now opened as read-only.

workbook file open as read only

  • Now we try to save by pressing Ctrl+S.
  • But as the file is opened as read-only, the attempt to save the file will fail.
  • Click OK after this.

error showing that the excel file can't be open as read-only

🔎 Breakdown of the Code

Sub File_Open_Through_Dialog_Box()

⮚ First, we provide a name for the sub-procedure which is File_Open_Through_Dialog_Box.

Dim File_Explorer As Office.FileDialog

⮚ Then, we set the file explorer dialog box as Office.FileDialog variable.

Dim selection_item As String

⮚ Then, we declare the selection_item as a String variable.

Set File_Explorer = Application.FileDialog(msoFileDialogFilePicker)

⮚ This line of code will open the file dialog box and set the file location in File_Explorer.

⮚ In the procedure, we will enter the necessary attributes of our dialog box.

Filters.Clear will clear any previous filter applied in the dialog box.

Filters.Add “Excel File type”, “*.xlsx?”, 1 will show only the xlsx files in the dialog box.

Title = “Choose your file” this will set the dialog box title

AllowMultiSelect = False disallow multiple selections of files.

 InitialFileName = “C:\Desktop” will open the immediate first window after opening the dialog box.

If.Show = True Then

       selection_item = .SelectedItems(1)

End If

⮚ This IF the procedure will save the file location in the selection_item variable.

Set book = Workbooks.Open(Filename:=selection_item, ReadOnly:=True)

⮚ This line will open the workbook save in the selection_item directory variable

End With

⮚ Here, we end with the procedure of this code.

End Sub

⮚ Finally, we end the sub-procedure of this code.

Read More: How to Open Workbook with Variable Name with Excel VBA


3. Open Multiple Workbooks as Read-Only from a Folder

Here, we can open multiple Excel files as read-only from the specified folder at the same time. The folder location needs to be extracted beforehand.

Steps

  • First, go to the Developer tab and click on Visual Basic. If you don’t have that, you have to enable the Developer tab. Or You can also press ‘Alt+F11‘ to open the Visual Basic Editor.

Open Multiple Workbooks as Read-Only from a Folder

  • Then there will be a new dialog box. In that dialog box, click on Insert > Module.

Sub File_open_multiple_workbooks_folder()
Dim wb As Workbook
Dim File_Path As String
Dim path_combine As String
File_Path = "C:\Users\USER\Desktop\Suprov\6165_62-0098_Rubayed Razib_excel vba open workbook read only\"
path_combine = Dir(File_Path & "*.xls*")
Do While path_combine <> ""
Set wb = Workbooks.Open(File_Path & path_combine)
path_combine = Dir
Loop
End Sub

  • Then close the Module window.
  • After that, go to the View tab > Macros.
  • Then click on View Macros.

view macros in order to open excel vba workbook

  • After clicking View Macros, select the macros that you created just now. The file name here is File_open_multiple_workbooks_folder. Then click Run.

  • After that, we can see that all the files from the specified folder are now open.
  • If we inspect the file name, we can see that the file name is accompanied by the Read-Only tag.

  • Furthermore, we can try to save the file by pressing Ctrl+S.
  • But as this file is a read-only file, it will fail to open the file and will show the below warning sign.
  • Click OK after this.

messege showing that the excel workbook vba can't open as read only

🔎 Breakdown of the Code

Sub File_open_multiple_workbooks_folder()

⮚ First, we provide a name for the sub-procedure which is File_open_multiple_workbooks_folder.

Dim wb As Workbook

⮚ Then, we set the workbook box as the wb variable in Workbook type.

Dim File_Path As String

⮚ Then, we declare the File_Path as a String variable.

Dim path_combine As String

⮚ Then, we declare the path_combine variable as a String-type variable.

File_Path = “C:\Users\USER\Desktop\Suprov\6165_62-0098_Rubayed Razib_excel vba open workbook read only\”

⮚ This line will set the folder location in the File_Path variable.

path_combine = Dir(File_Path & “*.xls*”)

⮚ This line will combine file direction with extension. Then store this into path_combine string type variable.

Do While path_combine <> “”

Set wb = Workbooks.Open(File_Path & path_combine)

path_combine = Dir

Loop

⮚ This line will look for the files with the xlsx extension in the designated folder. And if there is any file with the XLS extension, then it will open the file.

End Sub

⮚ Finally, we end the sub-procedure of this code.

Read More: How to Open Workbook from Path Using Excel VBA (4 Examples)


4. Using Input Box to Open a Workbook as Read-Only

Using this input box, we can choose whether we want to open the file as a read-only or editable document.

Steps

  • First, go to the Developer tab and click on Visual Basic. If you don’t have that, you have to enable the Developer tab. or you can also press ‘Alt+F11‘ to open the Visual Basic Editor.

Using Input Box to Open a Workbook as Read-Only

  • Then there will be a new dialog box. In that dialog box, click on Insert > Module.

Sub File_Open_using_Input_Box()
Dim wrkbk As Workbook
Dim x As Integer
Dim filepath As String
filepath = "C:\Users\USER\Desktop\VBA Code"
x = InputBox("Do you want to open it as Read-Only?" _
& vbCrLf & " If Yes then press 1" _
& vbCrLf & " If No then press 0")
Set wrkbk = Workbooks.Open(Filename:=filepath, ReadOnly:=x)
End Sub

  • Then close the Module window.
  • After that, go to View tab > Macros.
  • Then click on View Macros.

macros selection to open workbook as read only

  • After clicking View Macros, select the macros that you created just now. The file name here is File_Open_using_Input_Box. Then click Run.

  • After clicking Run, a new message box will open.
  • If you want to open the read-only, enter 1 and then click  OK.

choosing between two option

  • After clicking OK, your intended file will open.
  • And as we choose the read-only option, the file will open as read-only.
  • We can confirm this by noticing the read-only tag right side of the file name.

  • After that, we also tried saving the file, but it ultimately failed as the file was opened as read-only.
  • It will act as a warning message box. Click OK after this.

warning showing that the excel workbook can't be open as read only

🔎 Breakdown of the Code

Sub File_Open_using_Input_Box()

⮚ First, we provide a name for the sub-procedure which is File_open_multiple_workbooks_folder.

Dim wrkbk As Workbook

⮚ Then, we set the workbook box as the wrkbk variable in Workbook type.

Dim x As Integer

⮚ Then, we declare the x as an Integer variable.

Dim filepath As String

⮚ Then, we declare the filepath variable as a String type variable.

filepath = “C:\Users\USER\Desktop\VBA Code”

⮚ This line will set the file location in the File_Path variable.

x = InputBox(“Do you want to open it as Read-Only?” _

            & vbCrLf & ” If Yes then press 1″ _

            & vbCrLf & ” If No then press 0″)

⮚ This line of code will ask users whether they want to open the file as read-only or not and enter the value according to this. The response then will pass to the variable x.

Set wrkbk = Workbooks.Open(Filename:=filepath, ReadOnly:=x)

⮚ This line will open the file saved in the directory mentioned in the code, and whether the file will open as read-only or not will depend on the value of x entered in the previous step.

End Sub

⮚ Finally, we end the sub-procedure of this code.

Read More: How to Open Workbook and Run Macro Using VBA (4 Examples)


Conclusion

To sum it up, the issue of how we can open a workbook with variable names using VBA is answered here in 4 different ways.

For this problem, a workbook is available to download where you can practice these methods.

Feel free to ask any questions or feedback through the comment section. Any suggestion for the betterment of the Exceldemy community will be highly appreciable


Related Articles

 

Rubayed Razib Suprov

Rubayed Razib Suprov

Hi, I am Rubayed Razib Suprov. Graduated from the Bangladesh University of Engineering Technology in Naval Architecture and Marine Engineering department. I joined Exceldemy on March 2022. I have a keen interest in both fluid dynamics and data analysis. I try to teach people what I learned so far about the Excel tool to date, and hope I can make some positive impacts.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo