I wonder if your users are opening/extracting/closing by hand, or if it's
all happening with VBA?



In any case, it is possible to use ADO and refer to each file as a database.
I never did timing tests of the two methods to see which one is faster.

Here is a link where you can get the ADO routines for copying data  from a closed
workbook:
http://www.rondebruin.nl/tips.htm

Also, here is another approach which does not use ADO:

This opens the destination wb, specifies the destination cell range and enters
formulas that reference the source wb. The source wb is not opened. The formula
results are then converted to values. Note that the "A1" in the line
r.Formula = "='" & P & "\[" & FN & "]Sheet1'!A1"
will increment "A2", "A3" ... etc for each cell in range r. So a loop isn't
necessary.

Sub TransferData()
Dim r As Range
Dim ws As Worksheet
Dim wb As Workbook
Dim P As String, FN As String
FN = Application.GetOpenFilename("Excel Files(*.xls), *.xls")
Set wb = Workbooks.Open(FN) 'Destination wb
Set ws = wb.Sheets(1) 'Destination ws
Set r = ws.Range("A1:A100") 'Destination cell range
P = wb.Path 'Assumed path of source wb same as destination wb
FN = "SourceWB.xls" 'Substitute name of source wb
r.Formula = "='" & P & "\[" & FN & "]Sheet1'!A1"
r.Value = r.Value 'Transform r formulas to values
wb.Close True 'Close destination wb
End Sub

Somebody named Greg posted the above code in reply to a similar question in
another forum.

There was a method I used long ago, for extracting data  from a
damaged (a.k.a. target) file (up to the error). It obviously works
for good files  as well. It involved creating an ordinary link to a
cell in the target (unopened) file using a simple formula in a cell in
the destination (open file). I imagine the equivalent can be done in
VBA to reference unopened File data by building the object acession
out to the unopened file - don't know, but this could come in handy
for my current projects.


Lets see it I remember enough... It may be equivalent to ADO. I don't
know.

1- Open a new ("dummy") workbook. Name doesn't matter, It will be
discarded later.

2- Open the "destination" workbook. Where you want the data to
appear and be used.

3- Select (Click on) a cell in the "destination" sheet where you want
one of the cell's data to GO.
Type an Equals sign "=". Now go into the dummy Sheet and Select
(click on) the cell where the data *would be* if this had been the
Target (Un opened) file. Type ENTER. This creates a link to the
*location* from which you want data retrieved, but not the actual file.
You are now back in the Destination file. Where the "Dummy"
FileName has been automatically inserted into this formula.

4- Change the FileName in this foemula to the actual Target FileName.
Being careful to preserve the rest of the formula { the ' and !
characters, etc.). There is probably an issue with the Path of the
Target file. I believe this method works if all files are in one
directory. Otherwise The full FileName.Path is probably needed in
this step.

6- Now just Copy this formula into all cells where you want to get
data "from" in the Target File. NOTE: This is where the data is in
the Un-opened "Target" File. This effectively duplicates the Target
Sheet's data.Values as well as locations. The formulas will
automatically maintain the relative references just as in a single
sheet. AND the data automagiclly appears in the destination sheet.

In General, you can get the data from *any* cell in the un-opened
sheet by simply setting that cell reference-Address in the formula.
You determine in which cell in the destination sheet the data appears,
by the *location* of the formula.

We do this quite frequently; we have an index &
metrics file that mines data  from ~50 workbooks  in
different locations on different company servers.
In the first column is the file name, and then
subsequent columns are the cells we are mining.
Ex. –
First row is header row.
Second row - Cell A2 would be filename ABC.xls
B2 formula would be
=’servernam\dir1\subdir\folder1\subfolder1\[ABC.xls]Sheet1`!$C$12
C2 formula would be
=’servernam\dir1\subdir\folder1\subfolder1\[ABC.xls]Sheet1`!$C$13

Cell A3 would be filename DEF.xls
B3 formula would be
=’servernam\dir1\subdir\folder1\subfolder1\[DEF.xls]Sheet1`!$C$12
C3 formula would be
=’servernam\dir1\subdir\folder1\subfolder1\[DEF.xls]Sheet1`!$C$13

The easiest method to start would be create your index
sheet, open the first workbook, copy the cell from the
source workbook, select the destination cell, Paste
Special…, select Paste Link, then copy this formula &
find/replace as required.

The path must be enclosed in tic marks (`) with the
file name enclosed in brackets ([]). I use the ($)
anchor preceding both the column & row of the cell ID
so I can copy the formula in the index then do
find/replace.

It takes a while to set up, but then it is just a
matter of maintenance. When you open the file, it
will ask if you want to update, if you select yes it
may take some time. If you select no, it will open
with the data last saved. Then you can go to Edit,
Links, & scroll to the file you want to update. Or if
you want to update all at this time, just press [Ctrl]
+ A to select all.

NOTE: the data retrieved is static, i.e. if you are
mining a calculated value it will be the value of the
source file last saved. If that value is changing,
such as time, it will not reflect until the source
file is opened and saved, and then your index links
must be updated.