Strange behaviour using ADO

[AgentSmith]

I have 1 spreadsheet (Calcs.xls) that queries another one (Data.xls) using ADO. It just reads a couple of tables that are stored in named ranges.

In Calcs.xls I have a reference to Microsoft ActiveX Data Objects 2.8 Library. The idea is to connect to Data.xls, download a the tables of data I need and then finish. The weird thing is that when I first open the ADO Connection (second line in code snippet below) Excel actually opens Data.xls, whereas I want it to remain closed.

    Set conn = New ADODB.Connection
    conn.Open "Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=c:\mypath\Data.xls;"

Initially this behaviour was temperamental but now it happens every time. Anyone got any thoughts as to what to do? I've tried changing from a Jet driver to an ODBC driver but it happens regardless.

Subsequently, after I closed Data.xls manually, I looked at Project Explorer in VBE. Data.xls was still open in there. Despite closing both the connection in VB and the spreadsheet in Excel. If I rerun the code and close Data.xls again then there is a duplicate project in Project Explorer.

I've done this sort of thing before and I'm sure it's not supposed to do this. Any help you can offer is greatly appreciated.

 

[AgentSmith]

PS - if it's relevant, both spreadsheets are currently saved on a network drive.

 

[RoryA]

You don't have a reference set to Data.xls do you? Was your Jet connection something like:

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\mypath\Data.xls;Extended Properties=""Excel 8.0;HDR=Yes;"""

 

[RoryA]

Oh, and the project still appearing in the VBE after closing the workbook sounds a lot like a COM add-in issue. Google's desktop search used to cause that sort of problem.

 

[AgentSmith]

That Jet connection string looks about right to me, but there is no reference to Data.xls. I'll have a look at the COM add-ins though - thank you

 

[AgentSmith]

Well, thanks to the chap sitting next to me, I've found the root of the problem. This behaviour only happens when someone else has Data.xls open exclusively (it is not a shared workbook).

Data.xls did have "read only recommended" switched on, but I tried disabling it and it made no difference. So it must be the fact that it's opened exclusively by another user. So ... how do I check if a spreadsheet is already openend exclusively without actually opening it? Some kind of API call perhaps?

 

[RoryA]

See here for example.

 

[AgentSmith]

Thanks again, Rory. I did find some stuff about API calls but the code I tested out was no quicker than this. Think I'll stick to your non-API route!

 

[Fazza]

If it suits, another approach might be to have the data in a different file. This new file would be created/updated each time that data.xls closes or some other event. The new file might be in a sub-directory so that it is 'out of the way': this new file exists only so that calcs.xls can query from it and rely on the file it is querying being closed, not shared, whatever.

 

[AgentSmith]

I did think about setting something like that up, Fazza. In the end I chose not to for various reasons (including apathy!).

However you have given me another similar idea. I could set Data.xls to always save a backup. Then, instead of querying the live spreadsheet, query the backup! Nice simple way to solve about 99% of the problems. Happy days