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:
Excel Object Model
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).

See also:


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:

Sub main()
Dim objWorkbook As Workbook
Set objWorkbook = Workbooks.Open( _
"D:StuffBusinessTempData.xlsx")
End Sub

Where “D:StuffBusinessTempData.xlsx” is the path of the second excel workbook.

Assume the following data is in sheet1 of the second workbook:

Excel Data Sheet
The code below will open the file “Data.xlsx”, read the first column of data, and close the file:

Sub Example1()
Dim objWorkbook As Workbook
Dim i As Integer
'open the workbook with data
Set objWorkbook = Workbooks.Open( _
"D:StuffBusinessTempData.xlsx")
'read the data from the first columns
For i = 1 To 9
Cells(i, 1) = _
objWorkbook.Worksheets("Sheet1").Cells(i + 1, 1)
Next i
'close the workbook
objWorkbook.Close
End Sub

Note how the cells in the second workbook were referenced using their complete name:

objWorkbook.Worksheets("Sheet1").Cells(i + 1, 1)

We could have also chosen to use this:

objWorkbook.Worksheets(1).Cells(i + 1, 1)

Result:

Result Excel, Automation

Note: The code below would have resulted in an error:

objWorkbook.Sheet1.Cells(i + 1, 1)

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:

  1. Automate a new Excel application object.
  2. Sets its visible property to “hidden”.
  3. 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.

Sub Example2()
Dim appExcel As Application
Dim objWorkbook As Workbook
Dim i As Integer

'create new excel application object
Set appExcel = New Application
'set the applications visible property to false
appExcel.Visible = False
'open the workbook with data
Set objWorkbook = appExcel.Workbooks.Open( _
"D:StuffBusinessTempData.xlsx")
End Sub

This is assuming the second file is located in the path “D:StuffBusinessTempData.xlsx”.

The complete code can be seen below:

Sub Example3()
Dim appExcel As Application
Dim objWorkbook As Workbook
Dim i As Integer

'create new excel application object
Set appExcel = New Application
'set the applications visible property to false
appExcel.Visible = False
'open the workbook with data
Set objWorkbook = appExcel.Workbooks.Open( _
"D:StuffBusinessTempData.xlsx")
For i = 1 To 9
Cells(i, 1) = objWorkbook.Worksheets( _
"Sheet1").Cells(i + 1, 1)
Next i
'close the workbooks
objWorkbook.Close
'close the application
appExcel.Quit
End Sub

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:

See also:

If you need assistance with your code, or you are looking for a VBA programmer to hire feel free to contact me. Also please visit my website www.software-solutions-online.com

2 thoughts on “Excel VBA, Get Data From Another Workbook Through Automation”

  1. cheng says:

    enjoyed your clear concise explanation on VBA prgm above. Simple and unclutted not too confusing. much appreciated…

  2. Cole Young says:

    On the code above, the “Set objWorkbook = appExcel.Workbooks.Open( _
    “D:StuffBusinessTempData.xlsx”)”

    I am trying to change my location to D:\Documents\Templates\FSSF\Orders with this code

    Set objWorkbook = Workbooks.Open( _
    “D:\Documents\Templates\FSSF\Orders: 2015.07.31_FSSF_OKC_Name_description.xlsx”)

    I want to be able to search mutiple documents with different Names and descriptions, is there a way for me to do that.

    Additionally, when I search for the document, it says it can not be found.

Leave a Reply

Your email address will not be published. Required fields are marked *

privacy policy