How can I import data from shared excel files without locking the originals?

I would like to pull the data from multiple shared workbooks stored on our server into one local spreadsheet to update when I need. I tried using ';Import External Data'; in excel without changing any of the options, and the source data spreadsheet would be locked from editing whenever I had my local sheet open.





Is there any way to do this without locking the source data?How can I import data from shared excel files without locking the originals?
You can do what you have already done, Import external data, but you need to click the properties button and then uncheck the save query definition button to break the link between your sheet and the source data. this will then unlock the source data, however you will not be able to re-run your query, you will have to start from scratch if you want to re-import the data.How can I import data from shared excel files without locking the originals?
Yes and no.





You can use direct links to pull the data from other spreadsheets to the local one.





for example - on local sheet:


A1='[source1.xls]Sheet1'!$A$1 (whatever data you are trying to pull)


A2='[source2.xls]Sheet1'!$A$2


(note if you plan on coping and pasting the formula remove $'s)





now when you open your new local sheet excel should ask you if you would like to update links - Select yes.





I believe using this method, the other spreadsheets should remain unlocked while your local spreadsheet will have all the updated info you need.





*Please note:


This will only work if the data you are trying to pull is consistently in the same cell references.


Depending on the amount of data, this may take a while to initially set up, but will only take seconds later.

Bracelet

necklace