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).

See also:

• Microsoft MSDN Excel Object Model Overview

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:

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:

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:

• Excel VBA, Working With Sheets

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:

• Get Data From Another Workbook.xlsm
• Data.xlsx

See also:

• VBA, Excel Automation From Other Applications
• Microsoft MSDN Excel Object Model Overview
• Excel VBA, Working With Sheets

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