How-to Import Data From Another Workbook in Excel Using VBA
Although launched in 1985, Excel is still extremely popular in most industries and is used by almost every company one way or another. Some functionalities of the application could only be utilised by using VBA, which then makes this older software even more helpful when analysing and manipulating data.
Often you will find yourself copy-pasting data from other files into a consolidated workbook to further investigate the data from there. This process of copy-pasting data might take an tremendous amount of time. We could use this time to do more important things after we have automated this process using some simple VBA code. Let me explain how!
For this tutorial we will be importing copyright free weather data of Amsterdam from the past, which you can find here. Everyone should be able to follow this guide, even without any programming experience, though any prior coding experience will make certain concepts a lot easier to understand.
Developer Tab
Before we start writing some code, it is important to enable the functionalities needed. Since I have been writing about VBA quite a bit lately, you could best follow the steps under the subtitle ‘How to Build a Macro with Visual Basic for Applications’ in this article.
After you have enabled the Developer tab in your ribbon, save your workbook as an Excel Macro Enabled Workbook (.xlsm), open the Visual Basic editor and double-click the ThisWorkbook option in the Project Explorer in the left-upper corner.
This should then open a new pane in the editor where you could start typing some code, as shown in Figure 01.
Writing VBA Code
In the new opened pane we will start writing the code needed to import data from external sources.
Type Sub, short for subroutine, enter ImportData as name, conclude with two parentheses and press enter. Your macro should then be automatically completed by the editor, as shown in the code block below, where after we only need to enter the code that should be executed upon running the actual macro in between the two Sub keywords.
As discussed, the goal for this tutorial will be to copy paste the data from one sheet to another. In order to import another workbook, there are several mandatory steps, which I will explain thorougly below.
Within the earlier defined macro, we start by adding a variable on line two.
If you have never coded before, you could imagine a variable as a box to store information. Just like when you would be moving and write down the contents of the box, it is needed to define the name of the variable and its data type. Most used data types are strings (words and sentences), integers (rounded numbers) and doubles (numbers with decimals).
In case you are not sure what data type to use, the Variant type is the way to go. Do not use this type every time, as it will use more memory and therewith slow down your program.
In this case we are declaring a variable (aka preparing a box), so we could store the location of the file later.
In line four, the GetOpenFile method, which is built-in within Excel, will open a file explorer and return the location of the file you selected. The location, which you might recognise from those paths in the top bar of a regular file explorer, is then saved in our previously declared variable.
Before we proceed we need to check whether the user actually selected a file. If we would skip this part and no file would have been selected, the program would crash immediately. Because we included a check to see whether something is actually stored in the variable, the program knows what to do in both cases. If the file location variable is null (false), meaning no file is selected, Excel will play the iconic beep sound and exit the macro without too much hassle.
If there is a file selected, we need all the processing power of the device to handle the files open, which we can do by turning off the ScreenUpdating property. From then, everything on screen in Excel will be paused, while the device uses that power to calculate instead. To be honest, this might not be needed for now, but it is a good practice to get used to before you start building more complex macros where such a property will actually make a difference.
After we have turned off all the visuals, we will define an object using the Set keyword. To access the data from the workbook, we need to store this workbook as an object. Basically every method and property you would expect on the initial workbook is also available on this object.
To round off, we close the imported workbook and enable ScreenUpdating again in order to ensure everything looks normal again.
You could now run this code by pressing the green arrow in Visual Basic while your cursor is somewhere waiting between the two Sub keywords.
I know. You have selected the document with the weather data after the file explorer window opened, but apart from that, nothing happened. Don’t worry, that is actually correct!
Everything we have written now is needed to store the data from the external workbook in the object we called ImportWorkbook.
Now we will have some fun and make our lives a little easier!
Before programatically closing the file, you could use the Copy method to copy-paste the data from your imported workbook to the original one.
The block of code above copies cells A1:D25 (cells used in the data file discussed in the beginning of this tutorial) from the first worksheet of the imported workbook.
This method comes with an optional parameter which you could use to define its pasting location.
Not defining this new location for the data would copy the imported numbers to your clipboard where after you could paste them somewhere yourself instead.
In our case, we would like to paste the data in the upper-left corner (starting from cell A1) of the original file, which is targeted using the ThisWorkbook object.
If you would now paste this line of code in your already existing subroutine just above where we are closing the file, save & run the macro and import the downloaded data file from Meteoblue, you should see the data pasted in your original file.
Alternative Usage
As said, the ImportWorkbook includes everything you would expect from a workbook. Meaning that you could also get other information from the imported workbook that might that be needed.
Replacing the earlier used code to copy-paste the data from one sheet to another with the two lines above, should print you the name of the imported workbook and the sheet that is currently selected in the Immediate Window in your editor.
If you run the macro and do not see this information printed, select the View dropdown menu in the editor and make sure to select the Immediate Window.
In order to run this macro without the Visual Basic editor open, choose the Macros button in the Developer tab from the ribbon and from there select the macro you wish to run. As an alternative you could choose to select the Button option from the same tab and select the macro, which will create a button in your workbook that executes the macro when you press it.
Conclusion
Congratulations, you are now able to import data from an external workbook!
Feel free to experiment with this code, so you are able to use your own data files and please do not forget to leave a bit of applause if you liked this tutorial.
