excel 'import data' locks source workbook

[durandal05]

Hello,
I have a workbook which is updated as a log by several staff members continuously throughout the day. Another workbook has Pivot Tables that run off data imported from the other (shared) workbook. Problem is, whenever someone wants to open it up and look at the Pivot Tables, the source workbook becomes 'locked for editing' until they leave.

How can you import data without 'locking' the external source?

I would have thought this was a common problem but i can't find any solutions after searching... any ideas?

Appreciate any help.

[reggie1000]

hello durandal05,

If the user run within the same network i.e from a shared drive, then go to tools/share workbook, in the edit tab, click "allow changes by more than one user at the same time".

This will unlock the closed editing by one user, but it does cause some clashes when two or more users alter the same cells, but excel will notify the last saving user which changes they want to save.

hope it helps

cheers reg

[durandal05]

thanks for trying to help but the workbook is already shared
accessing it as a query from another spreadsheet somehow locks out the other users

i guess i can sort of avoid the problem by switching off the 'enable automatic refresh' message and the option to refresh on open, and then i can manually refresh the data periodically

when i look at 'edit query' there's a whole lot of code (or commands) which i'm not familiar with, perhaps the answer lies therein