Data storage and access

**firefly2k8** · 03-04-2010, 07:35 AM

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.

**6StringJazzer** · 03-05-2010, 12:39 PM

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.

Data storage and access

LinkBack

Thread Tools

Rate This Thread

Display

Data storage and access

Re: Data storage and access

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions