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

With Excel VBA you can directly open a workbook from a path. In this article, I will demonstrate how to open a workbook from a path using Excel VBA.


Download Practice Workbook

You can download the practice workbook from here:


4 Examples to Open Workbook from Path Using Excel VBA

Here, I will describe 4 suitable examples to open a workbook from Path using Excel VBA. Also, for your better understanding, I’m going to use sample data that has 3 columns. Those are Customer Name, Sales, and Profit.

Excel VBA Open Workbook from Path


1. Use of VBA Workbooks Method to Open Workbook from Path in Excel

You can employ a VBA code using the Workbooks Method for opening Workbook from Path in Excel. The steps are given below.

Steps:

  • Firstly, you need to open your worksheet. Here, you must save the Excel file as Excel Macro-Enabled Workbook (*xlsm).
  • Secondly, you have to choose the Developer tab >> then select Visual Basic.

Use of VBA Workbooks Method to Open Workbook from Path in Excel

  • At this time, from the Insert tab >> you have to select Module.

  • After that, write down the Code given below in Module1.
Sub Open_WB_from_Path()
Workbooks.Open "E:\Softeko\36\Open Workbook.xlsx"
End Sub

Here, in the path location, you must mention the extension of the file type.

Code Breakdown

  • Here, I have created a Sub Procedure named Open_WB_from_Path.
  • Then, I used the Workbooks Method to open the Workbook.
  • Also, you must write down the Path location in between the Inverted Comma.

  • Now, Save the code and then Click on Run.

Finally, you will see the workbook from the given Path.

Read More: How to Open File Dialog Default Folder with Excel VBA (3 Ways)


2. Merging Path and File Name Properties to Open Workbook

You can apply another VBA code merging the Path and File Name properties to open a Workbook from Path in Excel. The steps are given below.

Steps:

  • Firstly, you need to open your worksheet. Here, you must save the Excel file as Excel Macro-Enabled Workbook (*xlsm).
  • Secondly, you have to choose the Developer tab >> then select Visual Basic.

Merging Path and File Name Properties to Open Workbook Using Excel VBA

  • At this time, from the Insert tab >> you have to select Module.

  • After that, write down the Code given below in the Module2.
Sub Open_my_WB()
 Dim my_P As String, my_F As String
 Dim my_WB As Workbook
    my_P = "E:\Softeko\36\"
    my_F = my_P & "Open Workbook.xlsx"
 Set my_WB = Workbooks.Open(my_F)
End Sub

Code Breakdown

  • Here, I have created a Sub Procedure named Open_my_WB.
  • Next, declare some variables my_P and my_F as String, and my_WB as Workbook.
  • Here, the my_P denotes the Path of the file.
  • Furthermore, my_F denotes the file name with location. Moreover, ampersand operator (&) concatenates the Path and File name.
  • After that, I used the Workbooks method to open the file.

  • Now, Save the code and go back to the Excel File.
  • Then, from the Developer tab >> select Macros.

  • At this time, select Macro (Open_my_WB) and click on Run.

Lastly, you will see the workbook.

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


Similar Readings


3. Employing Workbooks Method with Cell Reference to Open Workbook

You can employ the Workbooks method with Cell Reference to open a workbook in Excel using VBA code. The steps are given below.

Steps:

  • First, you should type the file location in a cell. Here, I have written it in cell C11.

Employing Workbooks Method with Cell Reference to Open Workbook in Excel

  • Now, you should follow the steps of method-1.
  • Then, write down the following Code in the Module3.
Sub Open_WB_through_CellReference()
Dim WB_P As Variant
Set WB_P = Worksheets("Cell").Range("C11")
Workbooks.Open (WB_P)
End Sub

Using Cell Reference in VBA Code to Open Workbook in Excel

Code Breakdown

  • Here, I have created a Sub Procedure named Open_WB_through_CellReference.
  • Next, declare a variable WB_P as Variant.
  • After that, set the location in WB_P through Cell Reference.
  • Then, I used a Workbooks method to open the workbook.

  • Now, Save the code and go back to the Excel sheet.
  • Then, from the Developer tab >> select Macros.

  • At this time, select Macro (Open_WB_through_CellReference) and click on Run.

Finally, you will see the workbook from the given Path.

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


4. Applying GetOpenFilename Method to Open Workbook from Path in Excel

You can employ the GetOpenFilename method to open a workbook in Excel using VBA code. The steps are given below.

Steps:

  • Firstly, you should follow the steps of method-1.
  • Secondly, write down the following Code in Module4.
Sub Open_WB()
    Dim Dialog_Box_File As String
    Dialog_Box_File = Application.GetOpenFilename()
    Workbooks.Open (Dialog_Box_File)
End Sub

Applying GetOpenFilename Method to Open Workbook from Path in Excel VBA

Code Breakdown

  • Here, I have created a Sub Procedure named Open_WB.
  • Next, declare a variable Dialog_Box_File as String.
  • After that, I used two methods. They are GetOpenFilename and Workbooks methods to open the workbook.

  • Finally, Save the code and Run the code. Here, you can follow method-3.

At this time, you will see the following dialog box named Open.

  • Now, you should select your file.
  • Then, you need to press on Open.

Lastly, you will get the opened workbook.

Opened Workbook by Using Excel VBA

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


💬 Things to Remember

  • You can use any of the methods. Because all of them are handy and easy.
  • In addition, in the path location, you must mention the extension of the file type.
  • Furthermore, in the case of method 4 you need not to mention the path. Here, you should choose the file in the Open window.

Conclusion

I hope you found this article helpful. Here, I have explained 4 suitable examples to open a workbook from Path using Excel VBA. You can visit our website Exceldemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles

Musiha

Musiha

I am Musiha, graduated from Naval Architecture and Marine Engineering Dept., BUET, currently residing in Dhaka, Bangladesh. And my passion is to grow up my skillsets with industry demands.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo