• 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 heirarchical 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

    Code:
    Option Explicit
    'Constant for Database connection string
    Private Const glob_DBPath = "C:\Temp\Examples.mdb"
    
    'Private Const glob_sConnect = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & glob_DBPath & "';"     'For use with *.accdb files
    Private Const glob_sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & glob_sdbPath & ";"     'For use with *.mdb files
    
    
    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:

    Code:
    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:
    • 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
      • Specifically, this section of the above code:
        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 from Microsoft.Jet.

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

    Syndication:
    This article has also been published at Professional Office Developers Association.

     

    I'm afraid that you must be logged in to comment or leave a testimonial. I wish it could be otherwise, but I'm trying to keep my site spam free for everyone's benefit. If you don't yet have an account it's completely free to sign up, and a very quick process. Simply click here to Register. Not only can you post a comment here, but it gives you full access to posts questions in our forum as well!

     

    If you already have an account, and just haven't logged in yet, what are you waiting for? Login Now!

    Comments 1 Comment
    1. DitoPower's Avatar
      DitoPower -
      Dear Ken,

      I'm having problems with the above code.
      After executing 'GetRecords', I'm getting Run-time error '-2147217865(80040e37)': Method 'Open' of object '_Recordset' failed.
      The debug highlight is set on:
      PHP Code:
      'Open recordset based on Orders table    rst.Open strSQL, cnt 
      'glob_DBPath' is directing to your Marina.mdb of your previous post (also of 7years ago
      ).
      Thank you for your posts,
      And regards my regards,
      Dito
  • MVP Logo
  • Recent Forum Posts

    abbasi

    Clearing formats doesn't clear the background color

    Hi all,

    This is quote from the "Microsoft Excel 2010 by Jahn Walkenbach" book:

    "Clearing formats doesn't clear...

    abbasi Today, 03:31 PM Go to last post
    abbasi

    Label

    Hi all,

    I have heard much about labels but I don't know what those mean or how to make a cell as label.
    Is there anyone give some info...

    abbasi Today, 02:39 PM Go to last post
    dhazi

    modelling cycles

    The exercise is to model a recurring cost cycle. In this example there are some light bulbs need replacing periodically based on their usage. I cannot...

    dhazi Today, 01:38 PM Go to last post
    JOONA

    Hyper Link

    Hi Here is the sperad sheet if anyone needs it...

    JOONA Today, 09:31 AM Go to last post
    JOONA

    Advise

    Here is the spreadsheet....

    JOONA Today, 09:30 AM Go to last post