What is the best way to access a large volume of data stored in one workbook, from a number of different workbooks? I am comfortable employing macros. But I don't want to copy the data source as a sheet into the different workbooks where it is to be accessed.
I imagine retreiving the data (which essentially forms a 2D array), from a single user defined worksheet function in the different workbooks. I want to know how to get a worksheet function to open the source workbook, find the required data, close the source workbook and return the required data effeciently. Perhaps it doesn't even need to be opened?
Appreciate your thoughts.
You have a couple of different ways to go, depending on the complexity of your data.
An external link can be used in worksheet formulas to refer to data in other workbooks without having to open those workbooks, in most cases. Some functions (INDIRECT, I think) do require the referenced workbook to be open, but most don't.
VBA requires a workbook to be open to reference data from code. Opening and working with multiple workbooks in VBA is straightforward. Look up Workbooks.Open, for example.
There are various tradeoffs but either one can work. If you can provide more specifics about your source workbook and how it will be referenced, then I'm sure folks here would be happy to provide more specific advice.
Making the world a better place one fret at a time
||||||
If someone helped you, please click on the star icon at the bottom of their post
If your problem is solved, please update the first post:
EDIT, Go Advanced button, set Prefix to SOLVED
[code]
' Enclose code in tags like this
[/code]
Don't attach a screenshot--just attach your Excel file! It's easier and will let us experiment with your data, formulas, and code.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks