Need to Query Excel File Opened by Another User
 

I need to be able to query data out of an Excel file while that file is open
(for write) by another user. However, if the excel file with the source data
is open (for edit) by a different user, then I get strange behavior when I
refresh the query; it opens the file that has the source data, and then the
application doesn't scroll and redraw correctly.

I am running Windows XP SP2, Excel 2003 SP2.

To re-create...

I have two files:
A.xls
B.xls

Enter data into a A.xls, and define a range = "fred" so that it resembles a
well-formed table. Save the file, but keep it open.

Create another file, B.xls, that queries the "fred" range from A.xls.
- Go to <Data | Import External Data | New Database Query...
- That shoudl bring up the "Choose Data Source" dialogue.
- Select, "Excel Files" and click OK.
- select A.xls
- You should see the query wizard
- Select the range, "fred" and select all the columns
- Click through all the "Next" buttons and "Finish"; confirm the query
works OK.

Close B.xls. Keep A.xls open. Go get your best buddy to open B.xls
(different network user). Your buddy will get B.xls and A.xls. (If you both
close all files, then either one opens B.xls, it'll only open B.xls.)

I also tried setting up A.xls as a link table in Access and have B.xls query
that link table, but I get the same erroneous behavior. I have also tried
creating a Data Access Page in MS Access (again using a link table to A.xls)
but MS Access cannot access the data at all if another user has A.xls open
for edit. (It works OK if A.xls is open by another user for read.)

Is there a way to allow MS Access or Excel to query an Excel file even
though that Excel file is open for writing by another user?

....paul...