• Export A Range Of Excel Data To A Database

    Macro Purpose:
    • Exports a table of data from Excel into a database, using an ADO connection to pass SQL strings.

    Examples of where this function shines:

    • Works well for archiving data from Excel to an Access database.
    • Does not insert rows if all cells in the row are blank (avoids entering completely Null records in the database).
    • Code is robust enough to support varying amounts of columns or rows.
    • The entire database table structure does not have to be reproduced in Excel, providing that the Database Management System (DBMS) has default values, or can accept NULL values, for any omitted fields.
    • Uses transaction processing which will roll back all transactions if the full update is not successful.

    Macro Weakness(es):

    • Care must be taken at the Excel end to ensure that all data will be valid for the following reasons: This procedure does not validate any of the Excel data against the table structure of the database.
    • Passes all values from Excel to the database as text strings, (not dates, values, etc...). This does not seem to cause a problem in Access, as it converts them to the proper format, but it is unknown if this is true of other DBMSs.
    • Not truly an issue with the code, but more with SQL in Access: you can not pass a variable into a field having a name which conflicts with a reserved name, even if fully qualified. i.e. A column heading of Date will cause an SQL INSERT statement to fail.

    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.

    Workbook Setup Requirements:

    • The structure of the workbook must be set up as shown in the following illustration

    • Please Note:
      • Cells A3:F3 are named "tblHeadings"
      • Cells A4:F11 are named "tblRecords"

    • The tables can be expanded or constricted, but the tblHeadings and tblRecords references must be changed to represent the appropriate rows for the procedure to work correctly

    VBA Code Required:

    • A reference must be set to the Microsoft ActiveX Data Objects Libary
    • The code following should be placed in a standard code module:

    Sub DB_Insert_via_ADOSQL()
    'Author       : Ken Puls (www.excelguru.ca)
    'Macro purpose: To add record to Access database using ADO and SQL
    'NOTE:  Reference to Microsoft ActiveX Data Objects Libary required
        Dim cnt As New ADODB.Connection, _
                rst As New ADODB.Recordset, _
                dbPath As String, _
                tblName As String, _
                rngColHeads As Range, _
                rngTblRcds As Range, _
                colHead As String, _
                rcdDetail As String, _
                ch As Integer, _
                cl As Integer, _
                notNull As Boolean, _
                sConnect As String
        'Set the string to the path of your database as defined on the worksheet
        dbPath = ActiveSheet.Range("B1").Value
        tblName = ActiveSheet.Range("B2").Value
        Set rngColHeads = ActiveSheet.Range("tblHeadings")
        Set rngTblRcds = ActiveSheet.Range("tblRecords")
        'Set the database connection string here
        'Private sConnect = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & dbPath & "';"     'For use with *.accdb files
        sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath & ";"     'For use with *.mdb files
        'Concatenate a string with the names of the column headings
        colHead = " ("
        For ch = 1 To rngColHeads.Count
            colHead = colHead & rngColHeads.Columns(ch).Value
            Select Case ch
                Case Is = rngColHeads.Count
                    colHead = colHead & ")"
                Case Else
                    colHead = colHead & ","
            End Select
        Next ch
        'Open connection to the database
        cnt.Open sConnect
        'Begin transaction processing
        On Error GoTo EndUpdate
        'Insert records into database from worksheet table
        For cl = 1 To rngTblRcds.Rows.Count
            'Assume record is completely Null, and open record string for concatenation
            notNull = False
            rcdDetail = "('"
            'Evaluate field in the record
            For ch = 1 To rngColHeads.Count
                Select Case rngTblRcds.Rows(cl).Columns(ch).Value
                        'if empty, append value of null to string
                    Case Is = Empty
                        Select Case ch
                            Case Is = rngColHeads.Count
                                rcdDetail = Left(rcdDetail, Len(rcdDetail) - 1) & "NULL)"
                            Case Else
                                rcdDetail = Left(rcdDetail, Len(rcdDetail) - 1) & "NULL,'"
                        End Select
                        'if not empty, set notNull to true, and append value to string
                    Case Else
                        notNull = True
                        Select Case ch
                            Case Is = rngColHeads.Count
                                rcdDetail = rcdDetail & rngTblRcds.Rows(cl).Columns(ch).Value & "')"
                            Case Else
                                rcdDetail = rcdDetail & rngTblRcds.Rows(cl).Columns(ch).Value & "','"
                        End Select
                End Select
            Next ch
            'If record consists of only Null values, do not insert it to table, otherwise
            'insert the record
            Select Case notNull
                Case Is = True
                    rst.Open "INSERT INTO " & tblName & colHead & " VALUES " & rcdDetail, cnt
                Case Is = False
                    'do not insert record
            End Select
        Next cl
        'Check if error was encounted
        If Err.Number <> 0 Then
            'Error encountered.  Rollback transaction and inform user
            On Error Resume Next
            MsgBox "There was an error.  Update was not succesful!", vbCritical, "Error!"
            On Error Resume Next
        End If
        'Close the ADO objects
        Set rst = Nothing
        Set cnt = Nothing
        On Error GoTo 0
    End Sub

    How it works:
    • The database path, table name, column headings range and detail ranges are all assigned to variables for later use in the procedure
    • The table's column headings are all joined into a string to be used later in the procedure.
    • In the case of the example, the string is " (CustID,Type,DatePaid,DateStart,DateEnd,Amount)"
    • A connection to the database is established
    • For each row in the table, each field is evaluated, and joined into a string to be used in the procedure. If the field has a value, the value is added, otherwise the value of NULL is added. Three of the records from the example would be:

    • Each row is inserted into the database, one row at a time, unless it consists purely of Null values, in which case it is ignored
    • The connection to the database is closed

    The End Result:

    • The following image shows the result of the procedure, where the Access table held no data (but did exist in the database) prior to the procedure being executed:

    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:
                  'Open connection to the database
                       cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                       "Data Source=" & 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.

    Example Files:

    A zipped file containing both an Access database and the Excel file are attached. You will need to update the file path in Excel file before running the example file.

    This article was also published as the January 2007 Office Newsletter at Cimaware.com, makers of ExcelFIX. (Unfortunately the history of these newsletters is no longer available on their site.)
    A portugese translation of this article can also be found at MS Office Gurus - Brazil.


    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 5 Comments
    1. renanwrosauro's Avatar
      renanwrosauro -
      Dear colleagues,

      I'm trying to use mentioned vba, but it's not working for file. Could any of you check what I am doing wrong? I'm truly desperate for help.

      Attachment 1632Attachment 1633
      Thanks Renan
    1. WizzardOfOz's Avatar
      WizzardOfOz -
      Two possible errors.
      • A reference must be set to the Microsoft ActiveX Data Objects Libary
      • What version are you using, connect string above uses oledb.12. Open explorer, browse to office and I'm guessing yours is 10. If so change 12 to 10 and should work fine
    1. renanwrosauro's Avatar
      renanwrosauro -
      Thanks, error was at string set.
      Simple tip to solve it.
    1. eissoaimesmo's Avatar
      eissoaimesmo -
      Hi Friend I have a question I'm using Office 2007, has a line border to send? I did a test and only sends 8 lines.
    1. eissoaimesmo's Avatar
      eissoaimesmo -
      [QUOTE = WizzardOfOz; 9139] Dois erros possíveis.
      • A referência deve ser definido para o Microsoft ActiveX Data Objects Libary
      • Qual versão você está usando, conecte corda acima usa oledb.12. Abrir Explorer, navegue até o escritório e eu estou supondo que o seu caso é 10. Se assim for mudar 12-10 e deve funcionar bem

      [/ QUOTE]

      Hi Friend I have a question I'm using Office 2007, has a line border to send? I did a test and only sends 8 lines.