[I posted this on the Access VBA forum by mistake]

Hello,

The code and page references that I give here are from "Excel 2003 VBA, Wrox" - very useful book for the non-professional programmer that I am.

My question is the following :

- I use VBA in Excel, and would like to use Excel itself as a data source ("Using ADO with Non-Standard Data Sources, Querying Microsoft Excel Workbooks", p239). I'd like to keep a table in excel and use SQL expression power to manipulate the data in the table (selection, grouping, filteringE, rather than using the usual Excel lookups.

- The table to query is located in the active workbook, as opposed to being saved in another, inactive, file. I use a range name "TESTRNG" to define this table in the Sheet1.

- I then dump the SQL result in a range located in the Sheet1, cell C10, with the CopyFromRecordset statement.

- This does work, but I have an annoying secondary effect with the following environment :

1) I have an (unrelated) excel session already open (Session 1) on my Windows desktop.

2) I open a new, fresh, Excel session (Session2) and open my file, with the table, and the VBA code.

3) I run the code in my file, Session2 : it does work, but the unwanted effect is that my file opens itself again, as Read-Only, in the other Session1!

I do not know what went wrong. It could be great if you could put me on the right tracks.

Regards
Alex.

Using a very slightly modified version of the code provided p241 :

================================================== ========

Sub QueryWorkSheet()
    Dim Recordset As ADODB.Recordset
    Dim ConnectionString As String

    ConnectionString = _
    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & ThisWorkbook.FullName & ";" & _
    "Extended Properties=Excel 8.0;"

    Dim SQL As String

'TESTRNG is an excel range name, which defines the table to query, with field names in the first, header row, and records in other rows.

    SQL = "SELECT * FROM TESTRNG;"

    Set Recordset = New ADODB.Recordset

    On Error GoTo Cleanup

    Call Recordset.Open(SQL, ConnectionString, CursorTypeEnum.adOpenForwardOnly, LockTypeEnum.adLockReadOnly, CommandTypeEnum.adCmdText)

    Call Range("C10").CopyFromRecordset(Recordset)

Cleanup:
    Debug.Print Err.Description
    If (Recordset.State = ObjectStateEnum.adStateOpen) Then
        Recordset.Close
    End If

    Set Recordset = Nothing

End Sub

================================================== ========

Alex

OK, I came on the problem recently and even if I do not know how to handle it, I know where it comes from : the file seems to open in readOnly when the file is already opened by another user (or so it seems by you too). If the file isn't opened, everything seems to work fine ... and much faster