Export Excel Data Range to MS Access

The following VB Code is used to export a Range of Cells from Excel to Access Database.

Prerequisites:

Add a reference to the Microsoft Active X object Library

Click on Tools | References... in the VB-editor. Browse For Microsoft Active X object Library and include the Reference.

This example uses the current opening Excel as Input File


Sub AddExcelRangeToAccessDatabase()


'Error Handler
On Error Go to Done

'Declaration of Variables
Dim AdoConn As ADODB.Connection
Dim strSQL As String

Dim wbBook As Workbook
Dim wsSheet As Worksheet
Dim DataRange As Range

Dim DBName As String = "c:\EmployeesDetails.mdb"

Dim ConnString As String


'Assign the Work Book Sheet
Set wbBook = ThisWorkbook
Set wsSheet = wbBook.Worksheets(1)

With wsSheet
'Range in the source worksheet
Set DataRange = .Range("B5:E5")
End With

'Connection String
ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & DBName & ";"

'Instantiate the ADODB-object.
Set AdoConn = New ADODB.Connection(ConnString)


'INSERT Sql statement Construction
strSQL = "INSERT INTO Employee (EmpID, [First Name], [Last Name], DOB) VALUES('" & _
DataRange(1, 1).Value & "','" & DataRange(1, 2).Value & "','" & DataRange(1, 3).Value & "','" & DataRange(1, 4).Value & "');"

'Execution of Insert Query

With AdoConn
'Open the connection

.Open AdoConn

'Insert the values
.Execute (strSQL)

'Close the connection
.Close
End With

:Done

'Release objects from memory
Set AdoConn = Nothing

'Clear the Range
DataRange.ClearContents

End Sub


The above code inserts Range of Records(B5:E5) to Employee Table in C:\EmployeeDetails.mdb