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:

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

AgentSmith

Well-known Member
Joined
Mar 8, 2004
Messages
575
PS - if it's relevant, both spreadsheets are currently saved on a network drive.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,114
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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;"""
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,114
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

Well-known Member
Joined
Mar 8, 2004
Messages
575

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,114
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

See here for example.
 

AgentSmith

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

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
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

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

Watch MrExcel Video

Forum statistics

Threads
1,122,900
Messages
5,598,761
Members
414,258
Latest member
Dbarton0231

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
Top