Using ADO to Query Excel

  • Thread starter alexis suzat via OfficeKB.com
  • Start date
A

alexis suzat via OfficeKB.com

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

==========================================================
 
A

Alok

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top