How to Import an Excel Spreadsheet Into Access With a Macro

From http://smallbusiness.chron.com/import-excel-spreadsheet-access-macro-42538.html
by Kevin Lee, studioD
Step 1

Launch Access and open one of your databases.

Step 2

Move to the Tables pane and double-click the table you wish to load. The table’s fields appear in columns running across the top of the update pane. You’ll probably find it easier to work with your data if the column headings in your Excel spreadsheet match the names of the fields in your Access database.

Step 3

Launch Excel and open the spreadsheet you wish to load into Access. Verify that the spreadsheet’s column names match the Access table’s field names. If the spreadsheet has no column headings, right-click the spreadsheet’s first row and select “Insert.” Excel adds a new empty row to the top of the spreadsheet. Type a column heading for each column in the spreadsheet. For example, if the field names in your Access table are Name and Age, use those names as column headings in the spreadsheet.

Create Macro

Step 1

Return to Access and click “Create.” Double-click the “Macro” button on the Ribbon to view the Macro Builder.

Step 2

Click “Show All Actions” and then click “Action Catalog” to display the Action Catalog pane if it is not visible. This pane displays a list of macro categories.

Step 3

Move to the Action Catalog Pane and double-click “Data Import/Export” to view the macros within that category. The ImportExportSpreadsheet form appears. This form allows you to set up the parameters Access needs to import your spreadsheet.

if you dont see this action, go to Macro Tools > Design > Show All Actions

Show All Actions

Step 4

Type the name of your table in the “Table name” text box. Type the path name of your spreadsheet in the “File name” text box. For example, if it is a file named “Sales.xlsx” residing in a folder named “Spreadsheets” on your “C” drive, type the following into the “File Name” text box: .. C:\Spreadsheets\Sales.xslx

Step 5

Type “Yes” in the Has Field Names text box and press “Ctrl-S.” Type a name for your macro in the “Macro Name” text box and click “OK.”

Run Macro

Step 1

Click the tool bar’s “Database Tools” button and then click “Run Macros” to view the Run Macro popup window.

Step 2

Click the “Macro Name” drop-down menu and select the macro you created.

Step 3

Click “OK” to run the macro. Excel imports the spreadsheet data into the table.

Tips

• If you don’t see the imported spreadsheet data immediately, move to the Tables pane, right-click your table and select “Open” to refresh its view.
• or move to the Tables pane, then switch to Design view and then back to Datasheet view.
• Double-check your spreadsheet and Access table to verify that the number of fields in the table match the number of columns in the spreadsheet.