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
|
No responses found. Be the first to respond and make money from revenue sharing program.
|