Export data from Excel to Access (ADO) using VBA in Microsoft Excel





We can export the data from Microsoft Excel to Microsoft Access by using VBA. Below is the VBA code and process which you need to paste in the code module of the file.

1. Open Excel
2. Press ALT + F11
3. VBA Editor will OPEN
4. Click anywhere in the Project Window
5. Click on Insert
6. Click on Module

 

image1

 

7. In the Code Window, Copy and Paste the below mentioned Code

 

Sub ADOFromExcelToAccess()
‘ exports data from the active worksheet to a table in an Access database
‘ this procedure must be edited before use
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
‘ connect to the Access database
Set cn = New ADODB.Connection
cn.Open “Provider=Microsoft.Jet.OLEDB.4.0; ” & _
“Data Source=C:\FolderName\DataBaseName.mdb;”
‘ open a recordset
Set rs = New ADODB.Recordset
rs.Open “TableName”, cn, adOpenKeyset, adLockOptimistic, adCmdTable
‘ all records in a table
r = 3 ‘ the start row in the worksheet
Do While Len(Range(“A” & r).Formula) > 0
‘ repeat until first empty cell in column A
With rs
.AddNew ‘ create a new record
‘ add values to each field in the record
.Fields(“FieldName1″) = Range(“A” & r).Value
.Fields(“FieldName2″) = Range(“B” & r).Value
.Fields(“FieldNameN”) = Range(“C” & r).Value
‘ add more fields if necessary…
.Update ‘ stores the new record
End With
r = r + 1 ‘ next row
Loop
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub

 

image2

 

8. Once this is pasted, go to the Excel file
9. Click on the VIEW Tab on the ribbon
10. Click on Macros
11. Click on View Macros
12. The Shortcut Key to View Macros is ALT + F8
13. A Window will popup

 

image3

 

14. Select the Macro
15. Here the Macro is named as “ADOFromExcelToAccess”
16. Select the Macro “ADOFromExcelToAccess”
17. Click on Run
18. Click OK to close the Box

This is how we can Export data from Excel to Access by using VBA in Microsoft Excel.



One thought on “Export data from Excel to Access (ADO) using VBA in Microsoft Excel

  1. i tried compiling this code but it ain’t works it debugs properly but does not work , and does not give any error ..

    i have a created a table in excel worksheet where i have take command button i have 6 columns filled with data , what i want is wen i click on the button my data should get inserted into my access db.. i tried using ur code but doesnt help….

    please help with this query

Leave a Reply

Your email address will not be published. Required fields are marked *


1 + = four

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>