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.

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


About the Author

Jaime Avelar is a professional writer whose programming articles appear on various websites. He has been a software programmer since 2000. Avelar holds a Master of Science in information systems from the University of Texas at Arlington.

More Articles