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.
Open Workbook from Path Using Excel VBA:
4 Examples
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.
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.
- 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 SubHere, 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: Excel VBA to Open Workbook in Background
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.
- 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: Excel VBA to Open Workbook from Path in Cell
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.
- 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
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.
- 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 Workbook and Run Macro Using VBA
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
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.
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.
Download Practice Workbook
You can download the practice workbook from here:
Conclusion
I hope you found this article helpful. Here, I have explained 4 suitable examples to open a workbook from Path using Excel VBA. Please, drop comments, suggestions, or queries if you have any in the comment section below.
Related Articles
- How to Open Workbook as Read-Only with Excel VBA
- 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
