Retrieve Data From A Database To Excel Using SQL

This article shows how to retrieve a recordset from a database and place it in an Excel worksheet, using an ADO connection to pass SQL strings.

Examples of where this function shines:

  • Works well for retrieving data from Access (or other database management system) to Excel.
  • Allows working with data in Excel, and making use of its rich features.
  • Code is robust enough to support varying amounts of columns or rows in the recordset.
  • You can supply your own SQL, allowing you to pull back only the data you need or want to work with.

Macro Weakness(es):

  • You need to work out your own SQL queries.
  • This macro does not support OLE Objects or arrays (such as hierarchical recordsets).
  • The macro does not pull column headings from the database.

Versions Tested:

This function has been tested with Access & Excel 97, and Access & Excel 2003, and should also work with Access and Excel 2000 and 2002 (XP) without any modifications. To use it with an Access 2007 or higher format (*.accdb files), you must update the connection strings (at the very top of the code -- just uncomment the *.accdb line and remove the *.mdb line). If you wish to use another Database Management System (DBMS), see "Adapting this to a DBMS other than Access" below.

VBA Code Required:

  • A reference must be set to the Microsoft ActiveX Data Objects Libary (ADO)
  • The code following should be placed in a standard code module:
  • NOTE: If you are calling the RetrieveRecordset routine from another module, you will need to remove the "Private" keyword from the first line

Option Explicit
'Constant for Database connection string
Private Const glob_DBPath = "C:TempExamples.mdb"

'For use with *.accdb files
Private Const glob_sConnect = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & glob_DBPath & "';"
'For use with *.mdb files
'Private Const glob_sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & glob_sdbPath & ";"


Private Sub RetrieveRecordset(strSQL As String, clTrgt As Range)
'Author       : Ken Puls (www.excelguru.ca)
'Macro Purpose: To retrieve a recordset from a database (via an SQL query) and place
'               it in the supplied worksheet range
'NOTE         : Requires a reference to "Microsoft ActiveX Data Objects 2.x Library"
'               (Developed with reference to version 2.0 of the above)

    Dim cnt As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim rcArray As Variant
    Dim lFields As Long
    Dim lRecrds As Long
    Dim lCol As Long
    Dim lRow As Long

    'Open connection to the database
    cnt.Open glob_sConnect

    'Open recordset based on Orders table
    rst.Open strSQL, cnt

    'Count the number of fields to place in the worksheet
    lFields = rst.Fields.Count

    'Check version of Excel
    If Val(Mid(Application.Version, 1, InStr(1, Application.Version, ".") - 1)) > 8 Then
        'EXCEL 2000 or 2002: Use CopyFromRecordset
        'Copy the recordset from the database
        On Error Resume Next
        clTrgt.CopyFromRecordset rst
        
        'CopyFromRecordset will fail if the recordset contains an OLE
        'object field or array data such as hierarchical recordsets
        If Err.Number <> 0 Then GoTo EarlyExit
    
    Else
        'EXCEL 97 or earlier: Use GetRows then copy array to Excel
        'Copy recordset to an array
        rcArray = rst.GetRows

        'Determine number of records (adds 1 since 0 based array)
        lRecrds = UBound(rcArray, 2) + 1

        'Check the array for contents that are not valid when
        'copying the array to an Excel worksheet
        For lCol = 0 To lFields - 1
            For lRow = 0 To lRecrds - 1
                'Take care of Date fields
                If IsDate(rcArray(lCol, lRow)) Then
                    rcArray(lCol, lRow) = Format(rcArray(lCol, lRow))
                    'Take care of OLE object fields or array fields
                ElseIf IsArray(rcArray(lCol, lRow)) Then
                    rcArray(lCol, lRow) = "Array Field"
                End If
            Next lRow
        Next lCol

        'Transpose and place the array in the worksheet
        clTrgt.Resize(lRecrds, lFields).Value = TransposeDim(rcArray)
    End If

EarlyExit:
    'Close and release the ADO objects
    rst.Close
    cnt.Close
    Set rst = Nothing
    Set cnt = Nothing
    On Error GoTo 0
End Sub

Private Function TransposeDim(v As Variant) As Variant
'Function Purpose:  Transpose a 0-based array (v)
    Dim x As Long, Y As Long, Xupper As Long, Yupper As Long
    Dim tempArray As Variant

    Xupper = UBound(v, 2)
    Yupper = UBound(v, 1)

    ReDim tempArray(Xupper, Yupper)
    For x = 0 To Xupper
        For Y = 0 To Yupper
            tempArray(x, Y) = v(Y, x)
        Next Y
    Next x

    TransposeDim = tempArray
End Function

  • The routine is called by code such as the following:

Sub GetRecords()
'Macro Purpose: To retrieve a recordset to an Excel worksheet
    Dim sSQLQry As String
    Dim rngTarget As Range

    'Generate the SQL query and set the range to place the data in
    sSQLQry = "SELECT tblMoorages.CustID, tblMoorages.Type, " & _
            "tblMoorages.DatePaid, tblMoorages.Amount FROM tblMoorages;"
    ActiveSheet.Cells.ClearContents
    Set rngTarget = ActiveSheet.Range("A2")

    'Retrieve the records
    Call RetrieveRecordset(sSQLQry, rngTarget)
End Sub

How it works:

  • The database path is stored in a constant at the top of the code module. You will need to update that to the path of your database.
  • The SQL query and the target range are passed from the calling subroutine to the RetrieveRecordset routine.
  • A connection to the database is established.
  • The SQL query is passed to the database and the resulting records are returned into a recordset.
  • The code evaluates if it is running in an Excel 97 environment. If it is, it transposes the recordset using the custom transposition function.
  • The data is placed into the supplied Excel worksheet range.
  • The connection to the database is closed.

Adapting this to a DBMS other than Access:

  • Specifically, this section of the above code:
Private Const glob_sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & glob_DBPath & ";"

  • needs to be updated to reflect the proper OLE Database Provider engine instead of Microsoft.Jet.
  • In order to use this routine with a DBMS other than Microsoft Access, the Provider must be changed to match the DBMS that you want to use
  • More information on a huge variety of OLE Database Providers can be found here.

A Final Note on the Routines:

The above routines were originally written by someone else, although I have long since lost their source. The RetrieveRecordset has been through many variations, but may still hold some resemblance to the original work. The TransposeDim function, however, I have modified little, if at all. If you know who did originally create this code, please leave a comment so that we can acknowledge the fine work done by the original author.

Share:

Facebook
Twitter
LinkedIn

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Latest Posts