Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Open Workbook with Variable Name with Excel VBA

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.


Download Practice Workbook

Download this practice workbook below.


4 Easy Ways to Open Workbook with Variable Name Using VBA in Excel

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.

How to Open Workbook with Variable Name Using VBA in Excel


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.

Mentioning File Path to to Open Workbook with Variable Name Using VBA in Excel

  • 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.

Open 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.

Open Macro Dropdown

  • 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.

showing output using the workbook.add property to Open Workbook with Variable Name Using VBA in Excel

🔎 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: How to Open Workbook from Path Using Excel VBA (4 Examples)


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.

Without Mentioning File Path to Open Workbook with Variable Name Using VBA in Excel

  • 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.

Open 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.

Open Macro Dropdown

  • 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.

Result to 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.


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.

Open workbook as Read Only with Variable Name Using VBA in Excel

  • 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.

Open 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.

Open Macro Dropdown

  • 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.

the sample file opened here as read only in Excel

🔎 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.

Using Message Box to Open Workbook with Variable Name Using VBA in Excel

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

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.

Open Macro dropdown

  • 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.

messege box confirming that the file opened successfully

  • 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.

Messege box saying file not opened successfully

🔎 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.

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


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.

Use of Dialog Box toOpen Workbook with Variable Name Using VBA in Excel

  • 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.

Run macro to Open Workbook with Variable Name Using VBA in Excel

  • 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 Folder and Select File Using Excel VBA (4 Examples)


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.

Using Workbook.Add Property to Open Workbook with Variable Name Using VBA in Excel

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

Indert Module to Open Workbook with Variable Name Using VBA in Excel

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.

Open Macro dropdown

  • 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.

showing output to open workbook with variable name with VBA

🔎 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.

Read More: [Fixed!] Method Open of Object Workbooks Failed (4 Solutions)


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.

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