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