Browse » Home
Saturday, February 12, 2011
How to Transfer 2010 Excel Spreadsheet to Access 2010 Using VBA
1. Start Access. Click 'Blank Database' and click the 'Create' button to create a new database. Click the 'Database Tools' tab and click 'Visual Basic' to open the Microsoft Visual Basic Window. Click the 'Insert' menu and click 'Module' to insert a new code module.
2. Copy and paste the following code to create a new sub procedure:Private Sub importExcelSpreadsheet()
3. Press 'Enter' on your keyboard to create the 'End Sub' for the procedure.
4. 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
5. 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.
6. Press 'F5' to run your procedure and import the Excel spreadsheet into Access.