Macros files are easy to open in VBA by using the macros recorder. The pre-existing file is then opened in with VBA. This tutorial will teach us how to open macros file using VBA.
Figure 1: How to open excel file with VBA
Open Excel VBA Editor
- To do this, we will open a workbook and press the “ALT+F11” keys as a shortcut to open the VBA editor.
Figure 2: Open VBA editor
- We will now create a new module by clicking on “insert” and select “module”
Figure 3: Open a new module in VBA
- We will now type in “sub Browseforfile”, press the “enter” key, go back to our workbook, press the “ALT+F8” keys to run the title “Browseforfile”.
Figure 4: Browse Title
- We will go back to our VBA page and paste “application.get”. This will bring out some options and we will select “GetOpenFilename”.
Figure 5: Getting a file name
- We will edit the filename by typing “myFile =” before the “application.getopenfilename”, and type “,,Browse for workbook” after it. We will press the “enter” key and type “stop”
- We will press “F8” thrice to select what we have just typed and a dialogue box will open to help us track the file path in our workbook, but will not open it yet.
Figure 6: Editing and selecting filename
Figure 7: Creating file path
- Now that the file is tracked into one of the cells on our workbook, we will clear off the “stop”, we will now type in this code in our VBA “Thisworkbook.sheets(“sheet1”).Range(“a4”) = myFile”. Thereafter, we will erase the “stop” on the page and click on “stop” in the tool icon.
Figure 8: Tracking file path
- We will go back to our workbook page and open macros using “ALT+F8”, and then we will click on “browseforfile” to open the VBA file location.
Figure 9: Browsing for file
- We will select the file and it will be displayed in “CELL A4”
Figure 10: Selected file
- To open any workbook file, we will go to the VBA page and type the code ‘open wb, then we will press the enter key and type “Workbooks.Open myFile”.
Figure 11: Opening another workbook document
- We will go back to our workbook page and click on “macro”, select the “browseforfile”, select any document we want to open and it will open on a new tab.
Figure 12: Opening a document
Figure 13: Document opened