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…), 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

You may want to post this in the Excel VBA forum.

mmcdonal