[RESOLVED] Using ADO and Querying Microsoft Excel Workbooks

[cssriraman]

Hello,

My questions are 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”). 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 then dump the SQL result in a range located in the Sheet1.

Here is the code I have:

Sub QueryXlSheet()
    Dim RS As ADODB.Recordset
    Dim ConnectionString As String
 
    On Error GoTo ErrHandler
    ConnectionString = _
    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & ThisWorkbook.FullName & ";" & _
    "Extended Properties=Excel 8.0;"
 
    Dim SQL As String
    Dim TESTRNG As Range
'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.
    Set TESTRNG = ActiveSheet.Range("A1:C6")
    SQL = "SELECT * FROM TESTRNG;"
    Set RS = New ADODB.Recordset
    Call RS.Open(SQL, ConnectionString, CursorTypeEnum.adOpenForwardOnly, LockTypeEnum.adLockReadOnly, CommandTypeEnum.adCmdText)
    Debug.Print RS.RecordCount
 
ErrHandler:
    Debug.Print Err.Description
    If (RS.State = ObjectStateEnum.adStateOpen) Then
        RS.Close
    End If
    Set RS = Nothing
End Sub

I am getting the following error:

The Microsoft Jet database engine could not find the object 'TESTRNG'. Make sure the object exists and that you spell its name and the path name correctly.

I do not know what went wrong. It could be great if you could put me on the right tracks.

Thanks in advance.