You can use the TransferSpreadsheet action to import or export data between the current Microsoft Access database and a spreadsheet file. You can also link the data in a Microsoft Excel spreadsheet to the current Microsoft Access database. With a linked spreadsheet, you can view and edit the spreadsheet data with Microsoft Access while still allowing complete access to the data from your Microsoft Excel spreadsheet program.
The TransferSpreadsheet action uses 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 argument in the Action Arguments section of the Macro window. The default is Import. |
|
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 version 3.0. |
|
Note You can import, export, and link spreadsheet data from Microsoft Excel spreadsheets, but you can only import and export data from Lotus® spreadsheets. Microsoft Access also no longer supports importing, exporting, or linking data from Lotus (WKS) or Microsoft Excel version 2.0 spreadsheets. | |
|
Table Name |
The name of the Microsoft Access table to import spreadsheet data to, export spreadsheet data from, or link spreadsheet data to. You can also type the name of the Microsoft Access select query you want to export data from. This is a required argument. If you select Import in the Transfer Type argument, Microsoft Access appends the spreadsheet data to this table if the table already exists. Otherwise, Microsoft Access creates a new table containing the spreadsheet data. |
|
Filename |
The name of the spreadsheet file to import from, export to, or link to. Include the full path. This is a required argument. Microsoft Access creates a new spreadsheet when you export data from Microsoft Access. If the filename is the same as the name of an existing spreadsheet, Microsoft Access replaces the existing spreadsheet, unless you’re exporting to a Microsoft Excel version 5.0 or 7.0 workbook. In that case, Microsoft Access copies the exported data to the next available worksheet in the workbook. |
|
Has Field Names |
Specifies whether the first row of the spreadsheet contains the names of the fields. If you click Yes, Microsoft Access uses the names in this row as field names in the Microsoft Access table when you import or link the spreadsheet data. If you click No, Microsoft Access treats the first row as a normal row of data. The default is No. When you export Microsoft Access table or select query data to a spreadsheet, the field names are inserted into the first row of the spreadsheet if you have selected Yes for this argument. If you want to import or link the Database named range from a Microsoft Excel spreadsheet, you must click Yes in this box. |
|
Action argument |
Description |
|
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 or A1..E25. If you are importing from or linking to a Microsoft Excel version 5.0 or 7.0 worksheet, you can prefix the range with the name of the worksheet and an exclamation point; for example, Budget!A1:C7. Microsoft Access ignores this argument when you export. If you are importing from or linking to a Microsoft Excel spreadsheet, you can specify the Database named range by typing DATABASE in the Range box. This name is reserved by Microsoft Access for this special named range in Microsoft Excel. For a description of specifying a range of cells as the Database named range in Microsoft Excel, see your Microsoft Excel documentation. |
You can export the data in Microsoft Access select queries to spreadsheets, as long as these select queries don’t contain parameters. Microsoft Access exports the result set of the query, treating it just like a table.
Spreadsheet data that you append to an existing Microsoft 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, and 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).
This action is similar to clicking the Import or Link Tables subcommand of the Get External Data command or the Save As/Export command on the File menu of the Database window. You can use these commands to select a source of data, such as Microsoft Access or a type of database, spreadsheet, or text file. If you select a spreadsheet, a series of dialog boxes appear, or a Microsoft 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.
To run the TransferSpreadsheet action in Visual Basic, use the TransferSpreadsheet method of the DoCmd object.
OutputTo Action, SendObject Action, TransferDatabase Action, TransferSpreadsheet Method, TransferText Action.