Exporting Data from Excel to Access (ADO) using VBA in Microsoft Excel

Exporting Data from Excel to Access (ADO) using VBA in Microsoft Excel. To export data from Microsoft Excel to Microsoft Access, you can utilize VBA (Visual Basic for Applications). Follow the steps below to implement the process by pasting the provided VBA code into the code module of your Excel file:

Exporting Data from Excel to Access (ADO) using VBA in Microsoft Excel

• Open Microsoft Excel.
• Press ALT + F11 to open the VBA Editor.
• Click anywhere in the Project Window.
• Go to Insert and select Module.
• In the Code Window, copy and paste the following code:
  

  Exporting Data from Excel to Access (ADO) using VBA in Microsoft Excel

Sub ADOFromExcelToAccess()

    ‘ Export 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

    ‘ Loop through all records in the table

    r = 3 ‘ Start row in the worksheet

    Do While Len(Range(“A” & r).Formula) > 0 ‘ Repeat until the 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 ‘ Store the new record

        End With

        r = r + 1 ‘ Move to the next row

    Loop

    rs.Close

    Set rs = Nothing

    cn.Close

    Set cn = Nothing

End Sub

• Once you have pasted the code, go back to the Excel file.
• Click on the VIEW tab on the ribbon.
• Click on Macros.
• Select View Macros.
• The shortcut key to view macros is ALT + F8.
• A window will appear.
• Select the macro. In this case, the macro is named “ADOFromExcelToAccess”.
• Click on Run.
• Click OK to close the dialog box.

By following these steps, you will be able to export data from Excel to Access using VBA in Microsoft Excel.