Excel – Best way to access data from another excel workbook

microsoft excel

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

Best Answer

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.

Related Question