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]

Foiled! Backups are one save behind ... mutter mutter ...

Guess I could do a manual backup instead in Workbook_BeforeSave.

 

[ZVI]

Hi Mike,

In my experience the same issue was with ADO connection to network source workbook. If someone in network opens this workbook in Excel then such behavior happens at ADO connection to it.

To solve issue just save network source workbook as shared workbook.
Don’t keep change history to avoid increasing of workbook file size.

Regards,
Vladimir