Export A Range of Excel Data to a Database

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