Excel VBA, Get Data From Another Workbook Through Automation
In this article I will explain how you can use VBA for Excel to get data from another workbook.
Excel Object Model:
In the figure below you can see the basic object model of an Excel application:
Application: As you can see at the top of the hierarchy there is the Excel application. Each application can have several workbooks.
Workbooks: Excel files are workbooks. You can have several excel workbooks (files) open at the same time, but there will only be one Excel application open. You can test this by looking at the task manager.
Worksheet: Each workbook consists of at least one worksheet. By default workbooks have 3 worksheets (Sheet1, Sheet2, Sheet3).
Method 1, Adding a Workbook to the Current Application:
In this method a new workbook will be added to the collection of workbooks of the current Excel application. In other words we will not be creating a new Excel application object, but only adding a new workbooks to its collection of workbooks. This is done through the code below:
Where “D:StuffBusinessTempData.xlsx” is the path of the second excel workbook.
Assume the following data is in sheet1 of the second workbook:
Note how the cells in the second workbook were referenced using their complete name:
We could have also chosen to use this:
Note: The code below would have resulted in an error:
For more information about the different methods of referencing worksheets, please see:
Method 2, Creating a New Excel Application:
The method explained in the previous section has its pros and cons:
Pros: It is fast.
Cons: A workbook will flash on the screen and disappear after closing.
Having a workbook flash on the screen and disappear might not be a very professional thing to see in a program. One method to overcome this is to do the following:
- Automate a new Excel application object.
- Sets its visible property to “hidden”.
- Open the second workbook from the new excel application object.
The problem with this method is that the program will pause a little until the new excel application is automated.
This is assuming the second file is located in the path “D:StuffBusinessTempData.xlsx”.
The complete code can be seen below:
Note: Don’t forget to close the excel application object or you will end up with resource leakage.
You can download the files and code related to this article from the link below:
- VBA, Excel Automation From Other Applications
- Microsoft MSDN Excel Object Model Overview
- Excel VBA, Working With Sheets