Strange behaviour using ADO

AgentSmith

Well-known Member
Joined
Mar 8, 2004
Messages
575
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.

Code:
    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.
 
Last edited:

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
You don't have a reference set to Data.xls do you? Was your Jet connection something like:
Code:
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\mypath\Data.xls;Extended Properties=""Excel 8.0;HDR=Yes;"""
 
Upvote 0
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.
 
Upvote 0
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 :)
 
Upvote 0
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?
 
Upvote 0
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! :)
 
Upvote 0
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.
 
Upvote 0
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 :)
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top