You can use the TransferSpreadsheet action to import or export data between the current Microsoft Access database (.mdb) or Access project (.adp) and a spreadsheet file. You can also link the data in a Microsoft Excel spreadsheet to the current Access database. With a linked spreadsheet, you can view and edit the spreadsheet data with Access while still allowing complete access to the data from your Excel spreadsheet program. You can also link to data in a Lotus 1-2-3 spreadsheet file, but this data is read-only in Access.
This action is similar to pointing to Get External Data or Export on the File menu of the Database window and clicking Import or Link Tables.
I attach an snapshot example below.
The TransferSpreadsheet action has the following arguments.
| Action argument | Description |
|---|---|
| Transfer Type | The type of transfer you want to
make. Select Import, Export,
or Link in the Transfer Type
box in the Action Arguments section of the
Macro window. The default is Import.
Note The Link transfer type is not supported for Access projects (.adp). |
| Spreadsheet Type | The type of spreadsheet to
import from, export to, or link to. You can select one of a
number of spreadsheet types in the box. The default is
Microsoft Excel 8-10.
Note You can import from and link (read-only) to Lotus .WK4 files, but you can't export Access data to this spreadsheet format. Access also no longer supports importing, exporting, or linking data from Lotus .WKS or Excel version 2.0 spreadsheets with this action. If you want to import from or link to spreadsheet data in Excel version 2.0 or Lotus .WKS format, convert the spreadsheet data to a later version of Excel or Lotus 1-2-3 before importing or linking the data into Access. |
| Table Name | The name of the Access table to
import spreadsheet data to, export spreadsheet data from, or
link spreadsheet data to. You can also type the name of the
Access select query you want to export data from. This is a
required argument.
If you select Import in the Transfer Type argument, Access appends the spreadsheet data to this table if the table already exists. Otherwise, Access creates a new table containing the spreadsheet data. In Access, you can't use an SQL statement to specify data to export when you are using the TransferSpreadsheet action. Instead of using an SQL statement, you must first create a query and then specify the name of the query in the Table Name argument. |
| File Name | The name of the spreadsheet file
to import from, export to, or link to. Include the full path.
This is a required argument. (refer to above snapshot) Access creates a new spreadsheet when you export data from Access. If the file name is the same as the name of an existing spreadsheet, Access replaces the existing spreadsheet, unless you're exporting to an Excel version 5.0 or later workbook. In that case, Access copies the exported data to the next available new worksheet in the workbook. If you are importing from or linking to an Excel version 5.0 or later spreadsheet, you can specify a particular worksheet by using the Range argument. |
| Has Field Names | Specifies whether the first row
of the spreadsheet contains the names of the fields. If you
select Yes, Access uses the names in this row
as field names in the Access table when you import or link the
spreadsheet data. If you select No, Access
treats the first row as a normal row of data. The default is
No.
When you export an Access table or select query to a spreadsheet, the field names are inserted into the first row of the spreadsheet no matter what you select in this argument. |
| Range | The range of cells to import or
link. Leave this argument blank to import or link the entire
spreadsheet. You can type the name of a range in the spreadsheet
or specify the range of cells to import or link, such as A1:E25
(note that the A1..E25 syntax does not work in Access 97 or
later). If you are importing from or linking to an Excel version
5.0 or later spreadsheet, you can prefix the range with the name
of the worksheet and an exclamation point; for example,
Budget!A1:C7.
Note When you export to a spreadsheet, you must leave this argument blank. If you enter a range, the export will fail. |
Remarks: You can export the data in Access select queries to spreadsheets. Access exports the result set of the query, treating it just like a table.
Spreadsheet data that you append to an existing Access table must be compatible with the table's structure.
- Each field in the spreadsheet must be of the same data type as the corresponding field in the table.
- The fields must be in the same order (unless you set the Has Field Names argument to Yes, in which case the field names in the spreadsheet must match the field names in the table).