Using ADO to Query Excel

Hello,

The code and page references that I give here are from "Excel 2003 VBA, Wrox".

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

Alex wrote:
> Hello,
>
> The code and page references that I give here are from "Excel 2003 VBA, Wrox".
>
> 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
>
> ==========================================================
Hi,
I built macros using the MS ODBC Excel driver for doing query on the
same Excel Workbook the effects were: i) non secondary excel session,
but ii) memory exhausted (large amount of consumed memory as each query
is done). Now, I'm testing a version with QueryTables object it seems to
be more efficient in memory. Pls let me to know if you can resolve your
troubles.

Guillermo.