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. I need to keep my
Excel session since I get real-time feeds, make calulations, and they
manipulate the calculated data with SQL.

- 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

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