+ Reply to Thread
Results 1 to 2 of 2

Data storage and access

  1. #1
    Forum Contributor
    Join Date

    Data storage and access

    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.

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit

    Re: Data storage and access

    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.
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1