Naturally, we may need to open the file from a parent Excel file using VBA in a different directory. But at the same time, we have various types of criteria also about how we want to open the workbook. If you are curious to know how you can open a workbook with the variable name, then this article may come in handy for you. In this article, we discuss how you can open a workbook with variable name using VBA in Excel with elaborate explanations.
Open Workbook with Variable Name Using VBA in Excel: 4 Easy Ways
We are going to use the below dataset for the demonstration. We have the product information of several products with their Id. this is a sample file that we are going to open using the VBA code.
1. Using Workbook.Open Property
Using the Workbook.Open property, we can open files by mentioning the directory, or not mentioning the location. We also can make the opened file read-only.
1.1 Open Workbook Mentioning File Path
In the next method, we are going to use the Workbook.Open Property to open the file directly from the mentioned file location directory. No matter where the file is located, we can open the file easily.
Steps
- We have a file stored in the documents folder which we need to open.
- We will use the file name as a variable and then open the file using a small VBA macro.
- The Exact file directory of the file is shown below in the properties window.
- 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’ for opening the Visual Basic Editor.
- Then there will be a new dialog box, in that dialog box, click on the Insert > Module.
- Next, in the Module editor window, type the following code:
Sub Open_with_File_Path()
Dim Open_File As String: File_path = "C:\Users\User\OneDrive\Documents\Sample"
Dim wrkbk As Workbook
Set wrkbk = Workbooks.Open(Filename:=File_path)
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 Open_with_File_Path. Then click Run.
- After then the Sample file is going to open.
🔎 Breakdown of the Code
Sub Open_with_File_Path()⮚ First, we provide a name for the sub-procedure which is Open_with_File_Path.
Dim Open_File As String: File_path = "C:\Users\User\OneDrive\Documents\Sample"⮚ Then, we put the location of the file in the File_Path variable
Dim wrkbk As Workbook⮚ Then, we declare our variable wrkbk, whose type is a workbook.
Set wrkbk = Workbooks.Open(Filename:=File_path)⮚ Then, we open the file named in the File_Path directory variable and set the file as wrkbk variable.
End Sub⮚ Finally, we end the sub-procedure of this code.
Read More: Excel VBA to Open Workbook from Path in Cell
1.2 Open Workbook Without Mentioning File Path
In the next method, we will open the file from the parent folder, where the main file is saved. The file can be opened without mentioning any location in the code. This file just has to be in the same folder as the parent folder.
Steps
- We have a different file saved in the same directory where the parent Excel file is now saved.
- The file name is 1.
- 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’ for opening the Visual Basic Editor.
- Then there will be a new dialog box, in that dialog box, click on the Insert > Module.
- Next, in the Module editor window, type the following code:
Sub Open_without_File_Path()
Dim wrkbk As Workbook
Set wrkbk = Workbooks.Open(Filename:="1.xlsx")
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 Open_without_File_Path. Then click Run.
- After pressing Run, you will notice that the file named 1 is now open.
- And this is how we open the workbook with variable name using VBA in Excel.
🔎 Breakdown of the Code
Sub Open_without_File_Path()⮚ First, we provide a name for the sub-procedure which is Sub Open_without_File_Path()
Dim wrkbk As Workbook⮚ We declare wrkbk as a variable in Workbook type
Set wrkbk = Workbooks.Open(Filename:="1.xlsx")⮚ We open file from the parent directory named 1.xlsx.
End Sub⮚ Finally, we end the sub-procedure of this code.
Read More: How to Open Workbook from Path Using Excel VBA
1.3 Open Workbook as Read Only
The method is quite similar to the first method, but here we will open the file in read-only mode, meaning we won’t able to alter any data or value in the Excel file.
Steps
- The file we want to open is saved in the document folder.
- And this is the file we want to open as read-only.
- 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’ for opening the Visual Basic Editor.
- Then there will be a new dialog box, in that dialog box, click on the Insert > Module.
- Next, in the Module editor window, type the following code:
Sub Open_with_File_Read_Only()
Dim wrkbk As Workbook
Set wrkbk = Workbooks.Open("C:\Users\User\OneDrive\Documents\Sample", 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 Open_with_File_Read_Only. Then click Run.
- After clicking Run, we will see that the file is now opened as read-only, as shown in the title bar.
🔎 Breakdown of the Code
Sub Open_with_File_Read_Only()⮚ First, we provide a name for the sub-procedure which is Open_with_File_Read_Only()
Dim wrkbk As Workbook⮚ We declare wrkbk as variable in Workbook type
Set wrkbk = Workbooks.Open("C:\Users\User\OneDrive\Documents\Sample", ReadOnly:=True)⮚ The file will then open from the designated directory, and the file will be set read-only by the last argument.
End Sub⮚ Finally, we end the sub-procedure of this code.
Read More:Â How to Open Workbook as Read-Only with Excel VBA
2. Using Message Box
Almost similar to the previous method, we can open files through the VBA code here in Excel, but in this case, we will incorporate a small message box here.
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’ for opening the Visual Basic Editor.
- Then there will be a new dialog box, in that dialog box, click on the Insert > Module.
- Next, in the Module editor window, type the following code:
Sub Open_File_with_Messege_Box()
Dim path As String:
path = "C:\Users\User\OneDrive\Documents\Sample.xlsx"
If Dir(path) <> "" Then
Workbooks.Open (path)
MsgBox "The File Opened Successfully"
Else
MsgBox "Opening of the File Failed"
End If
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 Open_File_with_Messege_Box. Then click Run.
- Then we got the warning box, which is showing that The File Opened Successfully.
- Then click OK.
- And then we will see that the file is now open.
- And then we try to alter the code a little bit.
- We change the file name to Sample10, and there is actually no file named Sample10 in the documents folder.
- Then we Run the code again, and there is a message box saying Opening of the File Failed.
- Click OK after this.
🔎 Breakdown of the Code
Sub Open_File_with_Messege_Box()⮚ First, we provide a name for the sub-procedure which is Open_with_File_Read_Only()
Dim path As String:⮚ We declare wrkbk as a variable in the Workbook type
If Dir(path) <> "" Then
Workbooks.Open (path)
MsgBox "The File Opened Successfully"
Else⮚ This line will check out whether the file named Sample is available in the directory or not, If the file is there, then it will open the file and at the same time will show the message.
MsgBox "Opening of the File Failed"⮚ If there is no file named Sample in the directory, then this message will be delivered.
End If⮚ Finally, we end the sub-procedure of this code.
End Sub⮚ Finally, we end the sub-procedure of this code.
3. Use of Dialog Box to Open File
Extracting the file location directory and importing them each time in the VBA code is quite cumbersome. To resolve the issue, we will show how you can use a dialog box to select file from any directory.
Steps
- Now we will open the file using the file explorer dialog box.
- 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’ for opening the Visual Basic Editor.
- Then there will be a new dialog box, in that dialog box, click on the Insert > Module.
- Next, in the Module editor window, type the following code:
Sub Open_File_with_Dialog_Box()
Dim Dbox As FileDialog
Dim File_Path As String
Dim wrkbk As Workbook
Set Dbox = Application.FileDialog(msoFileDialogFilePicker)
Dbox.Title = "Choose and Open " & FileType
Dbox.Filters.Clear
Dbox.Show
If Dbox.SelectedItems.Count = 1 Then
   File_Path = Dbox.SelectedItems(1)
End If
Set wrkbk = Workbooks.Open(Filename:=File_Path)
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 Open_File_with_Dialog_Box. Then click Run.
- And then a new window opens. In the file explorer dialog box and select Sample and click OK.
- Then the file named Sample opened.
- And this is how we open the workbook with variable name using VBA in Excel.
🔎 Breakdown of the Code
Sub Open_File_with_Dialog_Box()⮚ First, we provide a name for the sub-procedure which is Sub Open_File_with_Dialog_Box()
Dim Dbox As FileDialog⮚ We declare Dbox as a variable in FileDialog type
Dim File_Path As String⮚ We declare File_Path as a variable in the File_Path As String type
Set Dbox = Application.FileDialog(msoFileDialogFilePicker)
Dbox.Title = "Choose and Open " & FileType⮚ There will be a dialog box from the first line. The next line denotes the dialog box name and the file type.
⮚ Dbox.Title will set the title of the dialog box. and FileType set the file type.
Dbox.Filters.Clear⮚ The Dbox.Filters.Clear will clear any previous filter applied in the diaog box
Dbox.Show⮚ The Dbox.Show will make the dialog box appear on the file.
⮚ This line will determine whether the user selected more than one file or not. If the user select more than one file, the whole procedure would stop.
End Sub⮚ Finally, we end the sub-procedure of this code.
Read More: How to Open File Dialog Default Folder with Excel VBA
4. Using Workbook.Add Property
Contrary to the previous methods, we will create a new Excel file in a predetermined directory and then we will open it using the Workbook. Add property.
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’ for opening the Visual Basic Editor.
- Then there will be a new dialog box, in that dialog box, click on the Insert > Module.
Next, in the Module editor window, type the following code:
Sub Open_File_with_Add_Property()
Dim File_Path As String: File_Path = "C:\Users\User\OneDrive\Documents\Sample.xlsx"
Dim wb As Workbook
Set wb = Workbooks.Add(File_Path)
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 Open_File_with_Add_Property. Then click Run.
- After clicking Run, you will notice that a new file is now created and opened.
🔎 Breakdown of the Code
⮚ First, we provide a name for the sub-procedure which is Sub Open_File_with_Add_Property()
⮚ Then we declare the File_Path variable as String type.
Dim File_Path As String: File_Path = "C:\Users\User\OneDrive\Documents\Sample.xlsx"⮚ And set the File_Path variable to the location of the file.
Dim wb As Workbook⮚ We declare wb as a variable in the Workbook type.
Set wb = Workbooks.Add(File_Path)⮚ The workbook is then added from the directory stored in the File_Path location using the Workbook.Add property.
End Sub⮚ Finally, we end the sub-procedure of this code.
Download Practice Workbook
Download this practice workbook below.
Conclusion
To sum it up, the issue of how we can open workbooks with variable names using VBA is answered here by 4 different examples. The VBA Macro method requires prior VBA-related knowledge to understand from scratch.
For this problem, a macro-enabled workbook is available to download where you can practice these methods.
Related Articles
- Excel VBA to Open Workbook in Background
- How to Open Workbook and Run Macro Using VBA
- How to Browse for File Path Using Excel VBA
- How to Open Folder and Select File Using Excel VBA
