This statement closes the If then condition in the Macro. Name is not equal to false then open the selected Excel file. Step 5: Enter the below code. This statement is If Then statement which is used when the file. Myfile_Name = Application.GetOpenFilename(FileFilter:="Excel Files(*.xl*),*.xl*)") Also, it will show the Excel file while browsing because we have put a filter for the Excel files.
This statement opens the below dialog box and you can browse the folders to go to the path where the file is saved. This statement declares the Filename as Variable because here we are not specifying any path or file name because it can change from time to time.
Step 2: Enter the below code which provides the name for your macro. Step 1: Follow step 1 mentioned in the first example for opening the Visual Basic Editor or Open Visual Basic Editor from Developer Tab. Suppose you want to open the “Test File” saved in the D drive of the computer then you just need to follow the below steps. The second part is to open the file for which you still need to be dependent on the First Method (VBA Workbooks.open Method). There are 5 arguments in the Application.GetOpenFileName but will only look at the basics like we did it in the first method. Now let us try to understand this method with the help of an example. The first part does not open the file which you want to open. This Part allows you to browse through your computer and select the path where your file is saved. The first part of this method is Application.GetOpenFileName. This method gives the option to browse in your computer to the location where your file is saved and opens that particular Workbooks which you want to open. Method #2 – Get Open FilenameĪs you know the challenges we face in the First method of the change in path and the File name, this method avoids both of the challenges. This makes the Job tedious and it is not error-free as well. So to make sure your macro runs perfectly without giving you an error, we will use the second method which is easy to handle and will not give you an error even when you don’t remember the pathname or even when the name of the file changes. So if the Path/Name of the file changes you need to change the VBA code of the Macro. The macro will not work even if the Name of the file changes by a single letter. This becomes challenging because many people don’t remember the path and if the Path of the file where it is saved changes a bit, the macro will not work.
The path mentioned above is quite simple because there are no folders and subfolders to get the Workbooks. After running the macro, you will find the excel file is automatically opened. As you can see in the above code, the macro will open the Excel file with the name “Test File.xlsx” which is saved in the D drive of the computer. Step 8: Run the code by pressing the F5 key or by clicking on Play Button. Workbooks.Open Filename:="D:\Test File.xlsx" Step 7: Enter the code which can open the workbook saved in the D drive of the computer. This code provides the name for your workbook. Step 6: Enter the below code in the VBA Module. Step 5: Once you have the developer tab, click on Visual basic as shown in the below screenshot.Īfter you click on the Visual Basics, you need to start coding for your macro. Step 4: Make sure the Developer field is marked as below and click ok. Follow the below steps to use Excel VBA Workbooks.open method. Suppose you want to open an excel file with the name “Test File” which is saved in the D Drive of the computer. Create a Macro in an Excel macro-enabled workbook. xlsm, etc). We can take the example of below macro to understand this function.
The “File_Name” is the name of the Workbooks that you want to open. But you need to be careful here because you need to provide the full path where the file is saved and name of the file with the extension of the files (.XLS. So the first step in this method is you should know the file name of the Excel Workbooks. Below is the statement to open the Workbooks.”Workbooks.open “File_Name” So we will just try to understand some basics of Workbooks.open and see how we can use this function in VBA to open the Workbooks. The first method in the VBA is to use the inbuilt function Workbooks.open. There are 15 optional arguments in the “Workbooks.open” function and to cover all the 15 arguments will take a lot of time.
You can download this VBA Workbook Open Excel Template here – VBA Workbook Open Excel Template Method #1 – Excel VBA Workbooks.open Method