Connection to workbooks in use by another user

[stfitch2007]

Hello,

I have one workbook running a form at a terminal for employees to scan in parts. this data is then entered into this workbook.

On another computer i have a second workbook that will import the data from the first using a workbook data connection. It is using the JET OLEDB 12 provider.

The problem is that when this second workbook tries to access the data, if the first sheet is open, the import popup will flash on the screen a few times and then the first sheet will be opened in my second workbook as a read only file. the data is imported correctly but i would like the import function not to open the first sheet in my second users screen.

Is there any way to import the data off of the first workbook without having excel open the first workbook as a read-only file in the second workbook?

the OLEDB script is as follows (replaced the file name but it is on the network, both workbooks are in the same folder on the network btw):

Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=S:\filename.xlsm;Mode=Share Deny Write;Extended Properties="HDR=YES;";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Engine Type=35;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False

Sorry if that was difficult to follow. i cant really demonstrate the problem without the network. also i was not sure if there was a better way to post the script since its not really "code".

Is there a better way to import data from another excel sheet without "disturbing it" / opening it that i am missing?

Thank you for the help

[ELeGault]

Can set it up in a power pivot to refresh the data that way.
Similar to what you are doing now but it will not open the first document and then you can use a pivot table view or multiple even to have reports or lists ready on refresh.

Or you can link directly =A1 of the other workbook and then make an entire sheet link directly to it.
If you go this route, I would recommend opening both, only link the same area as the data that is in the sheet + say 1000 rows. Then the user that opens the second (Sounds like you) would need to extend that row count accordingly making sure there are always a set of rows at the bottom that are nothing but 0's....
When you open the linked sheet it will ask if you want to Update, simply say yes (As long as it is a direct link, not a formula to pull IF for example, it will update to the last save)

Those are the 2 options I know of and I prefer PPivot over the other, but it does require a bit of setup (Worth it) especially when linking multiple sources. If it is just the one, you should be able to just set up the one source and start making pivots out of it.

Cheers -
ELeGault