How to Transfer 2010 Excel Spreadsheet to Access 2010 Using VBA

By Jaime Avelar

Excel 2010 and its Microsoft Office stablemate Access 2010 work hand-in-hand when you need to import Excel data into an Access database. The “DoCmd” object contains the “TransferSpreadsheet” method for importing a spreadsheet into Access from Excel using Visual Basic for Applications. This method also supports other file formats, such as Lotus. Similarly, you can use the same method to export data from Access to Excel by changing the “AcDataTransferType” enumeration to “acExport.”

Step 1

Start Access 2010. Click Blank Database and then click the Create button to create a new database. Click the Database Tools tab and select Visual Basic to open the Microsoft Visual Basic Window. Click the Insert menu and click Module to insert a new code module.

Step 2

Copy and paste the following code to create a new sub procedure:

Private Sub importExcelSpreadsheet()

Press Enter on your keyboard to create the End Sub for the procedure.

Step 3

Copy and paste the following code inside the Private Sub importExcelSpreadsheet() procedure to import an Excel spreadsheet to Access:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _ "Employees", "C:\Employees.xlsx", True

Step 4

Edit Employees and type the name of the table for the targeted spreadsheet. Edit C:\Employees.xlsx and type the path and the name of your Excel spreadsheet to be imported.

Step 5

Press F5 to run the procedure and import the Excel spreadsheet into Access.

References

Writer Bio