How do I export a macro from Access?

Tom’s Tutorials For Excel: Export Excel Spreadsheet Into Access Database Table

Here’s the latest of the Tom’s Tutorials for Excel series in which Tom Urtis walks you through a solution step-by-step. If you have any questions please ask them here in comments. Tom takes the time to be sure readers of every level of expertise feel confident using Excel. Are you an Excel professional? Tom welcomes your feedback and the opportunity to network.

Among the more common actions you’ll do when interacting with Access from Excel is to transfer records from an Excel worksheet to an Access database table. Suppose you have an Access database named Database1.accdb that contains a table named Table1 with 8 fields. In Sheet2 of your Excel workbook, you amass records during the day that are added to Table1 at the end of the workday.

You are probably aware that beginning with the release of Office 2007, extensions changed for Microsoft’s applications. For example, Excel workbooks that had the extension .xls now are either .xlsx or .xlsm. Access extensions also changed, from .mdb to .accdb, as shown in the below macro.

Take note of the version(s) of Excel and Access when the time comes to implement this code. Especially, the Provider line in the code is
.Provider = "Microsoft.ACE.OLEDB.12.0;Data Source=" & dbFileName & _
";Persist Security Info=False;".

Had this been a version of Office prior to 2007 to accommodate the JET database engine, that same line might have been
.Provider = "Microsoft.Jet.OLEDB.4.0"
or
.Provider = "Microsoft.Jet.OLEDB.4.0;" & "Data Source=" _
& dbFileName & ";" & "Extended Properties=Excel 8.0;".

To automate the daily task of transferring the day’s records from Excel to Access, you would maintain the Excel table with the fields in the same order as they are found in Table1 of the Access database. The following Excel macro would accomplish this task.

A reference to the Microsoft ActiveX Data Objects 2.8 Library is required for the macro to run. Press Alt+F11 to get into the VBE and from the menu, click Tools > References. Navigate to the reference for Microsoft ActiveX Data Objects 2.8 Library (or the highest Objects Library number you see), select it and click OK.

Sub ExcelToAccess()

'Declare variables
Dim dbConnection As ADODB.Connection
Dim dbFileName As String
Dim dbRecordset As ADODB.Recordset
Dim xRow As Long, xColumn As Long
Dim LastRow As Long

'Go to the worksheet containing the records you want to transfer.
Worksheets("Sheet2").Activate

'Determine the last row of data based on column A.
LastRow = Cells(Rows.Count, 1).End(xlUp).Row

'Create the connection to the database.
Set dbConnection = New ADODB.Connection

'Define the database file name
dbFileName = "C:\YourFilePath\Database1.accdb"

'Define the Provider and open the connection.
With dbConnection
.Provider = "Microsoft.ACE.OLEDB.12.0;Data Source=" & dbFileName & _
";Persist Security Info=False;"
.Open dbFileName
End With

'Create the recordset
Set dbRecordset = New ADODB.Recordset
dbRecordset.CursorLocation = adUseServer
dbRecordset.Open Source:="Table1", _
ActiveConnection:=dbConnection, _
CursorType:=adOpenDynamic, _
LockType:=adLockOptimistic, _
Options:=adCmdTable

'Loop thru rows & columns to load records from Excel to Access.
'Assume row 1 is the header row, so start at row 2.
For xRow = 2 To LastRow
dbRecordset.AddNew
'Assume this is an 8-column (field) table starting with column A.
For xColumn = 1 To 8
dbRecordset(Cells(1, xColumn).Value) = Cells(xRow, xColumn).Value
Next xColumn
dbRecordset.Update
Next xRow

'Close the connections.
dbRecordset.Close
dbConnection.Close

'Release Object variable memory.
Set dbRecordset = Nothing
Set dbConnection = Nothing

'Optional:
'Clear the range of data (the records) you just transferred.
'Range("A2:H" & LastRow).ClearContents

End Sub

How do I export macro data?

How do I export a macro from Access to Excel?

How do I export VBA code from Access?

Can macros be imported from Access into Excel?