TransferSpreadsheet Macro Action

You can use the TransferSpreadsheet action to import or export data between the current Access database (Microsoft Access database: A collection of data and objects (such as tables, queries, or forms) that is related to a particular topic or purpose.) (.mdb or .accdb) or Access project (Microsoft Access project: An Access file that connects to a Microsoft SQL Server database and is used to create client/server applications. A project file doesn't contain any data or data-definition-based objects, such as tables and views.) (.adp) and a spreadsheet file. You can also link (link (tables): An action that establishes a connection to data from another application so that you can view and edit the data in both the original application and in Access.) the data in a Microsoft Office Excel 2007 spreadsheet to the current Microsoft Office Access 2007 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.

 Note   This action will not be allowed if the database is not trusted. For more information about enabling macros, see the links in the See Also section of this article.

Setting

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 Builder pane. 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 Excel Workbook.

 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 (select query: A query that asks a question about the data stored in your tables and returns a result set in the form of a datasheet, without changing the data.) 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 (SQL string/statement: An expression that defines an SQL command, such as SELECT, UPDATE, or DELETE, and includes clauses such as WHERE and ORDER BY. SQL strings/statements are typically used in queries and in aggregate functions.) 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.

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 (result set: The set of records that results from running a query or applying a filter.) 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.

This action is similar to clicking the External Data tab and clicking Excel in the Import or Export group, or clicking More in the Import or Export group and clicking Lotus 1-2-3 File. You can use these commands to select a source of data, such as Access or a type of database, spreadsheet, or text file. If you select a spreadsheet, a series of dialog boxes appear, or an Access wizard runs, in which you select the name of the spreadsheet and other options. The arguments of the TransferSpreadsheet action reflect the options in these dialog boxes or in the wizards.

 Note   If you query or filter a linked spreadsheet, the query or filter is case-sensitive.

If you link to an Excel spreadsheet that is open in Edit mode, Access will wait until the Excel spreadsheet is out of Edit mode before completing the link; there's no time-out.

To run the TransferSpreadsheet action in a Visual Basic for Applications (VBA) module, use the TransferSpreadsheet method of the DoCmd object.

 
 
Applies to:
Access 2007