Linking to excel file when its closed.

[gtslabs]

I use an excel file as a database holding 10,000+ records.
I have other report files that needs to have access to this data.
Currently we have a tab in each report file that looks like the
database and we copy and paste the data daily from this master into
each report page as needed. Now each sheet in the report does a
lookup of the tab within this file.

Is there an easier way to link to the main excel database? I need
access to the data even when the master file is closed. The last time
I played with links this master file had to be open to access the
data. Is there an alternative?

 

[Harald Staff]

Hi

See if this can suit your needs:
http://www.erlandsendata.no/english/index.php?d=envbadacimportwbado

HTH. Best wishes Harald

 

[Harlan Grove]

I use an excel file as a database holding 10,000+ records.
I have other report files that needs to have access to this data.
Currently we have a tab in each report file that looks like the
database and we copy and paste the data daily from this master into
each report page as needed.  Now each sheet in the report does a
lookup of the tab within this file.

Is there an easier way to link to the main excel database? I need
access to the data even when the master file is closed. The last time
I played with links this master file had to be open to access the
data. Is there an alternative?

If you're doing lookups against +10K record tables, it'd be orders of
magnitude faster to have that other file open when doing the lookups.

If you want a formula-driven approach, see

http://groups.google.com/group/microsoft.public.excel.worksheet.functions/msg/ac443753560f0075

 

[ShaneDevenshire]

Hi,

You could replace the hard copied data on in the individual files with a
link back to the master file. I certainly would not do VLOOKUP against a
closed 10,000K file as Harlan said.

You need to set these links up once and then they can be refreshed, with the
master closed or not, anytime. And the refresh is generally very fast.

I would name the range in the master where the data is including one row of
titles. Move to one of the target workbooks and move to the page where you
normally paste the data.
1. Choose Data, Import External Data, New Database Query,
2. Choose Excel Files* from the list of sources and click OK
3. Locate and select the file and click OK.
4. Any named ranges will be listed on the left, pick your named range and
click > and then Next three times,
5. Choose Return Data to Microsoft Office Excel and clck Finish
6. Select the top left cell where you want the data put (it will bring the
titles in so your target sheet should be empty to start) Click OK

Now when ever you want to refresh the data you can choose the command Data,
Refresh Data or click the Refresh All button if it is visible.

You need to do this once for each target file, but then your set.