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

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

Using ADOR and ADO you can create a recordset in memory and do simple
manipulation like sort, filter and find.
The solution with ADOR and ADO will then involve the step of first reading
the tabular data in your workbook into the ADO recordset. As far as I know
you will not be able to give SQL commands to the ADO recordset that will be
created but you will need to examine if filter functionality along with the
Sort will work for you.

Alok Joshi

"alexis suzat via OfficeKB.com" wrote:

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

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

very annoying but a fact of life:

you cannot use ADO to query an OPEN workbook

see:
http://support.microsoft.com/default...9998&Product=x
lw

BUG: Memory leak occurs when you query an open Excel worksheet by using
ActiveX Data Objects (ADO)


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


alexis suzat via OfficeKB.com wrote :

The