Query Excel Worksheet in Ms-Access Vba (Using Adodb Recordset)

Query Excel worksheet in MS-Access VBA (using ADODB recordset)

In your connection string you say

 Excel 8.0;HDR=Yes

Which means that the first row will be treated as the header, no matter what it contains. If you want to use F1, F2 etc, say

Excel 8.0;HDR=No

VBA - Create ADODB.Recordset from the contents of a spreadsheet

I had to install the MDAC to get the msado15.dll and once I had it I added a reference to it from (on Win7 64bit):

C:\Program Files (x86)\Common Files\System\ado\msado15.dll

Then I created a function to return an ADODB.Recordset object by passing in a sheet name that exists in the currently active workbook. Here's the code for any others if they need it, including a Test() Sub to see if it works:

Public Function RecordSetFromSheet(sheetName As String)

Dim rst As New ADODB.Recordset
Dim cnx As New ADODB.Connection
Dim cmd As New ADODB.Command

    'setup the connection
    '[HDR=Yes] means the Field names are in the first row
    With cnx
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .ConnectionString = "Data Source='" & ThisWorkbook.FullName & "'; " & "Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"
        .Open
    End With

    'setup the command
    Set cmd.ActiveConnection = cnx
    cmd.CommandType = adCmdText
    cmd.CommandText = "SELECT * FROM [" & sheetName & "$]"
    rst.CursorLocation = adUseClient
    rst.CursorType = adOpenDynamic
    rst.LockType = adLockOptimistic

    'open the connection
    rst.Open cmd

    'disconnect the recordset
    Set rst.ActiveConnection = Nothing

    'cleanup
    If CBool(cmd.State And adStateOpen) = True Then
        Set cmd = Nothing
    End If

    If CBool(cnx.State And adStateOpen) = True Then cnx.Close
    Set cnx = Nothing

    '"return" the recordset object
    Set RecordSetFromSheet = rst

End Function

Public Sub Test()

Dim rstData As ADODB.Recordset
Set rstData = RecordSetFromSheet("Sheet1")

Sheets("Sheet2").Range("A1").CopyFromRecordset rstData

End Sub

The Sheet1 data:
Field1 Field2 Field3
Red A 1
Blue B 2
Green C 3

What should be copied to Sheet2:
Red A 1
Blue B 2
Green C 3

This is saving me a HUGE amount of time from querying against SQL every time I want to make a change and test it out...

--Robert

Using ADODB to print recordset to an Excel sheet

Assuming your SQL is correctly specifying worksheet range, consider adjusting some items in and outside of With...End With block.

• OpenRecordset is a DAO method. Use Recordset.Open for ADO
• Remove the second .Open call
• Remove the recordset name inside With
• Loop through worksheet down the rows instead of reassign same cell
• Use error handling for more informative error message to capture runtime exceptions

VBA

Sub Subroutine()
On Error Goto ErrHandle

    '...same as above...

    objRecordset.Open "Select * From TABLE Where FieldNm = NAME", objConnection

    With objRecordset
        .MoveLast
        .MoveFirst

        i = 0
        Do Until .EOF
            Worksheets("WORKSHEET").Cells(14 + i, 1) = .Fields("FieldNm")

            i = i + 1
            .MoveNext
        Loop    

        .Close
    End With

    objConnection.Close

ExitHandle:
    Set objRecordset = Nothing
    Set objConnection = Nothing
    Exit Sub

ErrHandle:
    Msgbox Err.Number & " - " & Err.Description, vbCritical, "RUNTIME ERROR"
    Resume ExitHandle
End Sub

How to retrieve data from Excel with ADODB connection if the first line of the worksheet does not have the column name?

See this Microsoft page. You can use something like:

strSQL = "SELECT [Field1], [Field2] FROM [Worksheet$$A10:B43] WHERE [Thing1] > 1"

---

Related Topics