Import External Data without locking source sheet

[juke_juke13]

I'm about to pull my hair out. All I want to do is link spreadsheets through an import data query function. However I want the source data sheet to be open for editing. Everytime I try to open it while the import data sheet is open however it says it is locked.

Anyway to just pull the data without locking the source sheet?

 

[juke_juke13]

If anyone has a suggestion it would be much appreciated. I've googled this to no end and there is not much help out there. Seems like a problem that would be pretty common but also resolvable.

Problem:
-allowing source spreadsheet to be opened/edited while another sheet accesses data through import data function.

Cheers!

 

[Andrew Poulsom]

I can't reproduce that behaviour (Excel 2003). What steps did you take to query the data?

 

[juke_juke13]

AP is on it, consider it solved!

Maybe the reason this has been impossible to find is b/c it's specific to something our computers run. Hope not!

I just used an import external data > new database query

And followed the setup wizard for an excel file data import.

Every time this sheet is open and I goto open the source sheet it's locked for editing by ""

 

[Andrew Poulsom]

Where is the source of the query located?

 

[juke_juke13]

Just an excel file.

Dates along the left column, years along the top row, and data numbers in the middle. The whole purpose of this was to basically pull in all that info without using a macro that simpled opened the source sheet, copied and pasted.

 

[ternopil]

Data - Connections - Add
Connection string:

DSN=Excel Files;DBQ=c:\temp\test.xls;DefaultDir=c:\temp\;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;ReadOnly=1;

Command text:

SELECT * FROM [Sheet1$]

OR

SELECT * FROM [Sheet1$A1:D100] d WHERE d.Date IS NOT NULL

for more advanced queries
I hope you've got the idea