Excel file locked after data import 1

[XB9R]

Hi All,

I have exactly the same issue is described in thread707-1650324.
Unfortunatly there is no solution given ...

My specific case:
I want to use data from an excelfile, let's say "Data.xls" and import it in "Master.xls". The data file is refreshed once a day somewhere between 2 and 6 pm. I'm able to import the data (using the refresh functionality). This causes the Data.xls file to be locked until the master file is closed. ...And as long as the data file is locked, it can't be overwritten with the new daily update... which occurs somewhere between 2 and 6 pm ...

Any suggestion is welcome. I prefer not to open the data file.

Also any insight in the connection mode (Share Deny Write) and possible values would be appreciated.


Dave

 

[SkipVought]

Hi,

I have never experienced this and I do something like what you're doing every day.

The differences are these:

1. I add the QueyTable manually and use code to refresh.
2. I use ODBC rather than OLEDB (Data > Get External Data > From other sources > From Microsoft Query ... Select Exccel Files* and drill down to a workbook)
3. I use a Query to extact data, rather than connecting to a Table, and I think that this might be what is biting you. It would be simple for you to change like...

select *
From [YourTable$]

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[XB9R]

Hi Skip,

Thanks for your reply.

Not sure whether I understand you correctly.
a) When I use: Data > Get External Data > From other sources > From Microsoft Query > Databases tab ... Select Excel Files* and drill down to a workbook - I get an error that the file doesn't contain a table. I'm also not in a position to change the Data.xls file, so this approach doesn't work for me...
b) When I use: Data > Get External Data > From Microsoft Query > Queru's tab ... then there is nothing to select ...

c) What worked for me in this occasion: Data > Get External Data > From XLM ... then select all files and browse to the Data.xls ... open and you get the option to select the sheet... excepts that this locks the dat.xls file.

Now on your way:
1) What exactly do you mean with add the querytable manually? Do you mean: Data > Get External Data > From other sources > From Microsoft Query > Databases tab ... Select Excel Files* and drill down to a workbook --> That's exactly what I always do when I have to retrieve data from an Access db to Excel. I always create a button in Excel to refresh (In most occassions I have 2 queries to refresh: 1) data itself & 2) timestamp of the data). Works fine and I have never experienced a lock of the Access db.
2) I Always use From MS query ...
3) not sure what you mean ...

This is the first time that I try to import a sheet from another Excel workbook.


Dave

 

[SkipVought]

b) When I use: Data > Get External Data > From Microsoft Query > Queru's tab ... then there is nothing to select ...

In the Add Tables window, click the OPTIONS b utton and make sure that ALL boxes are checked.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[XB9R]

Hi Skip,

Thanks a lot - I got it working. This is how I did it:

Data > Get External Data > From other sources > From Microsoft Query > Databases tab ... UNTICK the wizard ... Select Excel Files* and drill down to a workbook ... In the Add Tables window, click the OPTIONS button and make sure that ALL boxes are checked... Now you should be able to select the sheet and create the query and save it as a connectionfile.

Guess you hardly use the wizard anymore ...

Dave

 

[SkipVought]

I dislike that wizard! It is unchecked!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]