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.
Open Workbook as Read-Only with Excel VBA: 4 Easy Ways
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.
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.
- Then there will be a new dialog box. In that dialog box, click on 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
- 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.
🔎 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: Excel VBA to Open Workbook in Background
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.
- Then there will be a new dialog box. In that dialog box, click on Insert > 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.
- 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.
- 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.
- 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.
🔎 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.
- 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.
- 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.
🔎 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. 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.
- 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.
- 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.
- 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.
🔎 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
Download Practice Workbook
Download this practice workbook below.
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.
Related Articles
- Excel VBA to Open Workbook from Path in Cell
- How to Browse for File Path Using Excel VBA
- How to Open Folder and Select File Using Excel VBA
- How to Open File Dialog Default Folder with Excel VBA
This is by FAR the BEST explanation i have ever read on the internet for a technical solution. I have (was) in the IT business from Programmer to Business owner and I find MOST technical explanations, lets just say, less than communicationalbe (I know this is not a word) but explains why i am saying. i will say this, i am 80 yrs old and am learning Excel VBA just for fun and building a system for some friends. I WILL be searching on your name for help in the future as my 1st place to look. Thank you so much.
Dear John Summers,
You are most welcome. Your appreciation means a lot to us to write such technical and informative article in future.
Regards
ExcelDemy