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.



5 thoughts 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

    • if you are using excel 2010 then please enable below reference’s from VB editor ——–>Tools——->References
      Microsoft ActiveX Data Objects 6.1 Library
      Microsoft Office 14.0 Access database engine objects

      and change below command

      from
      cn.Open “Provider=Microsoft.Jet.OLEDB.4.0; ” & _

      to
      cn.open “provider = microsoft.ace.oledb.12.0;” & _

  2. hi , i have issue with compiling this code, the comment texts appear in red font, even i just copy and paste your code.. :S why is it doing this?

    • hi,

      I tried this with below code:
      Sub ADOFromExcelToAccess()
      Sheets(“Imported Data”).Select
      ‘ 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:\Master database\Master.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

      I got error 3706
      provider can not be found

Leave a Reply

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


seven + = 12

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>