[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.

[si_the_geek]

To use Excel spreadsheets as tables, you need to enclose them inside [ $] , eg:

SQL = "SELECT * FROM [TESTRNG$]"

I don't think that you can use specific ranges tho, I think it has to be a whole sheet, eg:

SQL = "SELECT * FROM [" & ActiveSheet.name & "$]"

[cssriraman]

To use Excel spreadsheets as tables, you need to enclose them inside [ $] , eg:

SQL = "SELECT * FROM [TESTRNG$]"

Thanks for the information.

I tried using the sheet name. For the code which i mentioned earlier i got "-1" for:

Debug.Print RS.RecordCount

What is the problem?

I don't think that you can use specific ranges tho, I think it has to be a whole sheet, eg:

SQL = "SELECT * FROM [" & ActiveSheet.name & "$]"

I think we can use specific ranges. But, I am not sure how to do that.

For example, If you look at the form option in Data menu, we can use the specific range to create forms. So, it is possible to work with specific range in excel.

[si_the_geek]

Getting -1 for Recordcount means that the actual recordcount is unknown at that time, but it is greater than 0. You can either loop until EOF is true (quicker and easier), or use a client side cursor to get the recordcount (by specifying different parameters for Open).

For example, If you look at the form option in Data menu, we can use the specific range to create forms. So, it is possible to work with specific range in excel.

That is completely different.. here you are not dealing with Excel, you are dealing with ADO (which happens to be using an Excel file for the data).

It may be possible, but I dont know how you would do it, presumably something like this:

SQL = "SELECT * FROM [" & ActiveSheet.name & "$.A1:C3]"

[cssriraman]

Here with I attached an sample excel sheet. In sheet 1, there are four columns: date, Category, id, rate.

I want do some sql queries on that for example i need to calculate the total value of rate column where the date is "2/1/2006".

Somebody please look into that and help me out.

Thanks in advance.

[si_the_geek]

As you have headers in your sheet, you need to specify that in your connection string, ie:

ConnectionString = _
    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & ThisWorkbook.FullName & ";" & _
    "Extended Properties=[u]""Excel 8.0;HDR=Yes;""[/u]"

You can then use SQL to reference the fields, eg:

SQL = "SELECT Sum(Rate) " _
            & "FROM [Sheet1$] " _
            & "WHERE [date] = '2/1/2006' "

[cssriraman]

Si, Thanks for helping me.

I got the error: "Data type mismatch in criteria expression."

Here is the complete code with the changes:

Sub QueryXlSheet2()
    Dim RS As ADODB.Recordset
    Dim ConnectionString As String
 
    On Error GoTo ErrHandler
    ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & ActiveWorkbook.FullName & ";" & _
    "Extended Properties=""Excel 8.0;HDR=Yes;"""
    Dim SQL As String
    SQL = "SELECT Sum(Rate) " _
            & "FROM [Sheet1$] " _
            & "WHERE [date] = '2/1/2006' "
    Set RS = New ADODB.Recordset
    RS.Open SQL, ConnectionString, CursorTypeEnum.adOpenForwardOnly, LockTypeEnum.adLockReadOnly, CommandTypeEnum.adCmdText ' -> Error on this line
    Debug.Print RS.RecordCount
    Debug.Print RS(0)
 
ErrHandler:
    Debug.Print Err.Description
    If (RS.State = ObjectStateEnum.adStateOpen) Then
        RS.Close
    End If
    Set RS = Nothing
End Sub

Please help me.

Thanks,

[RobDog888]

If your column [date] is formatted as a Date data type in Excel then the query criteria will need to compare a like type, date.

& "WHERE [date] = #2/1/2006# "

[cssriraman]

Thanks Rob! it worked fine.

Here is the complete code:

Sub QueryXlSheet2()
    Dim RS As ADODB.Recordset
    Dim ConnectionString As String
 
    On Error GoTo ErrHandler
    ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                       "Data Source=" & ActiveWorkbook.FullName & ";" & _
                       "Extended Properties=""Excel 8.0;HDR=Yes;"""
    Dim SQL As String
    'SQL = "SELECT Sum(Rate) " _ ' using Sum function in query
    SQL = "SELECT date, Category, id, Rate " _
        & "FROM [Sheet1$] " _
        & "WHERE [Rate] = 12 "    'Number
    '& "WHERE [Category] = 'Fruits' " 'String
    '& "WHERE [date] = #2/1/2006# " 'Date
    Set RS = New ADODB.Recordset
    'RS.Open SQL, ConnectionString, CursorTypeEnum.adOpenForwardOnly, LockTypeEnum.adLockReadOnly, CommandTypeEnum.adCmdText
    RS.Open SQL, ConnectionString, 1, 1, 1
    'Debug.Print RS.RecordCount
    'Debug.Print RS(0)
    Do While Not RS.EOF
        'Debug.Print RS(0)
        MsgBox RS(0)
        'MsgBox RS(0) & " " & RS(1) & " " & RS(2) & " " & RS(3)
        RS.MoveNext
    Loop
 
ExitHere:
    If (RS.State = ObjectStateEnum.adStateOpen) Then
        RS.Close
    End If
    Set RS = Nothing
    Exit Sub
 
ErrHandler:
    Debug.Print Err.Description
    GoTo ExitHere
End Sub