Use a command button to import Excel data to an Access table

While Access 2003 and 2002 include a wizard for importing data from an Excel file into an Access table, if you have to perform that function periodically, you may want to create a command button that will perform all of the steps of the wizard for you.

For example, suppose you need to import a monthly report of hours worked for all employees in your branch office. The branch office entered this information in an Excel worksheet named Hours Worked. Follow these steps to create the command button:

1. Open a new form in Design view.
2. Click the Command Button tool in the Toolbox.
3. Click and drag in the form where you want the tool.
4. Click Cancel to close the Command Button wizard if necessary.
5. Right-click the Command button and select Properties.
6. Click the Event tab and click the On-Click property box.
7. Select [Event Procedure] from the drop-down list.
8. Click the Build button.
9. Enter the following code at the prompt:

On Error GoTo Err_CmdImportExcel_Click
DoCmd.TransferSpreadsheet transfertype:=acImport,

tablename:="Hours Worked", filename:="C:/Personnel/Hours Worked.xls",

HasFieldNames:=True
Exit_CmdImportExcel_Click:
Exit Sub
Err_Command13_Click:
MsgBox Err.Description
Resume Exit_Command13_Click

10. Press [Alt]Q.

When you press the command button, the Hours Worked table will receive the data in the Excel file. Each month, Excel will archive the worksheet and create a new worksheet with the name Hours Worked.xls to collect the new data for the next month. When you press the command button at the end of that month, you will automatically add the new data to the end of the Hours Worked table in the Access database.

Miss an Access tip?

Check out the Microsoft Access archive, and catch up on other Access tips.

Help users increase productivity by automatically signing up for TechRepublic’s free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.