Expert Zone
XLSX + XLS repair tool

Export A Range of Excel Data to a Database

By

Ken Puls

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

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](http://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
    
    '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")
    
    '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 "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & dbPath & ";"
    
    'Begin transaction processing
    On Error GoTo EndUpdate
    cnt.BeginTrans
    
    '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
    EndUpdate:
    
    'Check if error was encounted
    If Err.Number <> 0 Then
    'Error encountered. Rollback transaction and inform user
    On Error Resume Next
    cnt.RollbackTrans
    MsgBox "There was an error. Update was not succesful!", vbCritical, "Error!"
    Else
    On Error Resume Next
    cnt.CommitTrans
    End If
    
    'Close the ADO objects
    cnt.Close
    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:

    Worksheet Row: String Returned
    4 ('A-01','Annual','09/04/04','09/04/04','09/03/05','1240')
    9 ('B-03','prorate',NULL,'08/17/04','05/31/05','975')
    10 (NULL,NULL,NULL,NULL,NULL,NULL)
  • 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.

Sample File