ADO to read from excel file; ignores locked file??

[xmlfan]

I use ADO to get data from an excel file. For testing purposes I created two files: Data.xlsx with some data and Adotest.xlsm with this piece of code:

    Dim sFullName As String
    sFullName = ThisWorkbook.Path & "\Data.xlsx"
    
    ' setup and open connection
    Dim sConnect As String
    sConnect = ""
    sConnect = sConnect & "Provider=Microsoft.ACE.OLEDB.12.0;"
    sConnect = sConnect & "Data Source=" & sFullName & ";"
    sConnect = sConnect & "Mode=Share Exclusive;" ' 12
    sConnect = sConnect & "Extended Properties=""Excel 12.0;HDR=YES;"""
    Dim adoConnection As ADODB.Connection
    Set adoConnection = New ADODB.Connection
    adoConnection.ConnectionString = sConnect
    adoConnection.Open
    
    ' get data
    Dim rsData As ADODB.Recordset, sSql As String
    Set rsData = New ADODB.Recordset
    sSql = "SELECT * FROM [Sheet1$]"
    rsData.Open sSql, adoConnection, adOpenForwardOnly, adLockReadOnly, adCmdText
    Sheet1.Cells.ClearContents
    Sheet1.Range("B2").CopyFromRecordset rsData
    rsData.Close
    
    ' close
    adoConnection.Close
    Set adoConnection = Nothing

Case 1a: run the code and don't be surprised.
Case 1b: step through the code but stop after the connection.open. Now try to open Data.xlsm from Excel or Explorer. Again no surprises (although... strange message isn't it?).

Case 2a: now first open Data.xlsx, then run the code. Hey, isn't the file locked? And hey, some headers!
Case 2b: again run to after .Open, switch to Data.xlsx, change some data, save, and switch back to Adotest. Continue stepping.

Case 3a: same as 2a but open in a new excel instance. Hey, at the .Open ADO opens Data.xlsx Read-Only!
Case 3b: you know...

I am very very much surprised that Ado doesn't throw an error when trying to open a locked file. Am i missing something?
My current work around: before connecting i use vba-open to test if the file is in use. Any other suggestions?

Thanks!

- Frans

(Windows Vista Ultimate, Office Ultimate 2007. I tried ADO 2.7 and ADO 6.0, same results.)

 

[xenou]

ADO (in the past) has been known to have problems (memory leaks) when reading from open excel files. I would avoid it, though I've not heard anything specific about whether this has changed in recent years with the advent of open office xml file formats.

 

[xmlfan]

Thanks xenou! A google on "excel ado open file leak" gave some interesting hits, including http://support.microsoft.com/kb/319998/en-us

Do you mean: avoid Ado. Or: avoid Ado using an open excel file?

Best regards,
- Frans

 

[xenou]

I only mean to avoid ADO on open Excel files - ADO as a whole is very reliable. I did try it once on an open Excel file (in fact, a self-query on the same Excel file that had the code in it). It was fine for a few weeks - but did end up crashing and as a rule I just try to stay away from those situations. Though in fact it's not terribly difficult to copy data to a temporary workbook to use as your data file, if you find you really want to use Excel data from a file that is otherwise open.