Best way to access data from another excel workbook

Question

What would be the best way to get read-only access to data stored in another excel workbook. There are two posibilities that I'm aware of, each with their pros and cons.

Option 1) Reference the data directly

Example: =[test1.xls]Sheet1!$A$1

• pro: Data updated when workbook is opened
• pro: Works even when the referenced workbook is closed
• con: Operation can't be data driven
• pro: works in all spreadsheets, no security limitation

Option 2) Reference the data using INDIRECT and ADDRESS

Example: =INDIRECT(ADDRESS(B7,B6, 1, TRUE, B4))

where B7 contains row index, B6 contains column index, B4 contains workbook/worksheet name

• pro: The location of data can be data driven
• con: doesn't work when referenced book is closed
• con: workaround to auto-load the referenced book don't work on default macro security level

Answer 1

If you don't do this much, or need 1 cell value as in your example, Option 1 is a good way to go. If you need to:

1. Do this sort of thing frequently
2. Make dynamic queries based on what else might be going on in a workbook (or take in user input)
3. Pull in lots of data, or filter / sort the data your grabbing
4. Process the data your pulling in before you present it
5. etc.

You may want to write 1 or more macros that use ADO. You can use ADO in excel to pull data from excel, other databases or text files into your spreadsheets. I use it a ton. So much so, I have a dedicated class module to handle the details for me.

Answer 2

If the reason you're concerned about whether the technique is data driven is because you're concerned about the reference being stale when the data in the other workbook changes, you can avoid the problem by using a named range.

To create a named range: In the workbook that has the data, you select the cells that have the data click in the name box (where the cell(s) row&column appear), and type in the name.

Referencing the range: To refer to the named range from another sheet:

'[MyData.xls]Sheet1'!MyNamedRange

If you need a particular value from, say a table, you can use the various lookup functions such as vlookup, hlookup, index to select a particular value.

For example, If I had the following table in the workbook "Sales.xls" on the "Grocery" worksheet, and I've defined a named range "tableSales" for the entire table,

Item Sales Bananas 343 Grapes 123 Eggs 756

Then I can look up the "Eggs" sales in another workbook with the following:

=VLOOKUP("Eggs",'[Sales.xls]Grocery'!tableSales,2,false)

(vlookup's first parameter is the lookup value, the 2nd is a range for the table, third parameter specifies which column to retrieve, and if the 4th is set to true, it will attempt find an approximate match)