The TransferSpreadsheet method carries out the TransferSpreadsheet action in Visual Basic.

expression.TransferSpreadsheet(TransferType, SpreadsheetType, TableName, FileName, HasFieldNames, Range, UseOA)

expression Required. An expression that returns one of the objects in the Applies To list.

 AcDataTransferType

AcDataTransferType can be one of these AcDataTransferType constants.

acExport

acImport default

acLink If you leave this argument blank, the default constant (acImport) is assumed.

 AcSpreadSheetType

AcSpreadSheetType can be one of these AcSpreadSheetType constants.

acSpreadsheetTypeExcel3

acSpreadsheetTypeExcel4

acSpreadsheetTypeExcel5

acSpreadsheetTypeExcel7

acSpreadsheetTypeExcel8 default

acSpreadsheetTypeExcel9 default

acSpreadsheetTypeLotusWJ2 - Japanese version only

acSpreadsheetTypeLotusWK1

acSpreadsheetTypeLotusWK3

acSpreadsheetTypeLotusWK4 Note  You can link to data in a Lotus 1-2-3 spreadsheet file, but this data is read-only in Microsoft Access. You can import from and link (read-only) to Lotus .WK4 files, but you can't export Microsoft Access data to this spreadsheet format. Microsoft Access also no longer supports importing, exporting, or linking data from Lotus .WKS or Microsoft Excel version 2.0 spreadsheets by using this method. If you leave this argument blank, the default constant (acSpreadsheetTypeExcel8) is assumed.

TableName  Optional Variant. A string expression that's the name of the Microsoft Access table you want to import spreadsheet data into, export spreadsheet data from, or link spreadsheet data to, or the Microsoft Access select query whose results you want to export to a spreadsheet.

FileName  Optional Variant. A string expression that's the file name and path of the spreadsheet you want to import from, export to, or link to.

HasFieldNames  Optional Variant. Use True (–1) to use the first row of the spreadsheet as field names when importing or linking. Use False (0) to treat the first row of the spreadsheet as normal data. If you leave this argument blank, the default (False) is assumed. 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 no matter what you enter for this argument.

Range  Optional Variant. A string expression that's a valid range of cells or the name of a range in the spreadsheet. This argument applies only to importing. Leave this argument blank to import the entire spreadsheet. When you export to a spreadsheet, you must leave this argument blank. If you enter a range, the export will fail.

UseOA  Optional Variant. This argument is not supported.

Remarks

For more information on how the action and its arguments work, see the action topic.

You can leave an optional argument blank in the middle of the syntax, but you must include the argument's comma. If you leave a trailing argument blank, don't use a comma following the last argument you specify.

Note  You can also use ActiveX Data Objects (ADO) to create a link by using the ActiveConnection property for the Recordset object.

Example

The following example imports the data from the specified range of the Lotus spreadsheet Newemps.wk3 into the Microsoft Access Employees table. It uses the first row of the spreadsheet as field names.

DoCmd.TransferSpreadsheet acImport, 3, _
    "Employees","C:\Lotus\Newemps.wk3", True, "A1:G12"