In this tutorial we will guide you on how to open an Excel file via VBA. Visual Basic for applications or VBA is designed by Microsoft to be used along with handful of Microsoft applications such as Excel or Access. It comes up with Microsoft Excel 2007 and 2010 as built in tool. By using VBA, we can record our own macros and write scripts for performing specific tasks as per our requirements. If you are working with Excel and you need to open a particular file over and over again then you may take help from VBA and write up a simple code to let VBA open the Excel File for you in a single click.
Step 1 – Open Visual Basic editor
To use VBA for opening excel file, move over to the Developer Tab and click on the Visual Basic option to open up the VBA editor.
Step 2 – Insert a New Module
Once opened, open up the Insert menu and select the Module option to insert a new module.
Step 3 – Enter a new Sub Function
Let’s start by creating a new macro in VBA for opening excel file, by using the Sub keyword and naming the macro Open Workbook.
Now we will use the Workbooks.Open property to open up an excel file already present in out E Drive. For that we will define the path within quotes right after the keyword.
Step 4 – Run the Macro
Once done, go back to the Excel Sheet and press “ALT + F8” to open up the macros window. Over here, run the newly created macro and you will notice that the workbook that you linked to earlier will open up.
Step 5 – Add another file to open
It’s always a good idea to include the extension along with the file name, but it’s not necessary. Your workbook would still open even if you don’t include the file extension here. For example, let’s open up the “Yearly expenses” workbook, also present in the E drive, but this time, we will not define the file extension.
Once you run the macro, you will notice that the workbook will open up without giving you any errors.
And that’s basically how you can use VBA to open excel files using visual basic.
Script can be found here:
https://howtech.tv/wp-content/images/004609/130715_8 _Script.txt
Home