Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 661
Default 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...
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
enable automatic refresh Vass Excel Worksheet Functions 2 March 11th 06 05:36 AM
save to same file as opened dmtech Excel Discussion (Misc queries) 6 March 8th 06 07:25 PM
Excel allows access to more than one user to same file Bernie G Excel Discussion (Misc queries) 2 March 8th 06 10:03 AM
Can excel files be opened in fixed screen size preset by user? vabraham Excel Discussion (Misc queries) 1 August 28th 05 03:08 PM
Set up Excel to start new instance for each new file opened. Minimal_Subset Excel Discussion (Misc queries) 2 May 12th 05 01:36 AM


All times are GMT +1. The time now is 09:09 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"