Spreadsheets

The TransferSpreadsheet method is very similar to the TransferDatabase method in that it enables you to import, link, and export, only in this case it deals with spreadsheets. The syntax is shown in the following code.

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

Various parameters are used to specify how the method should execute. The following table explains the use of each parameter.

Parameter

Description

TransferType

Type of transfer to be performed. Valid choices are acImport (default), acLink, and acExport.

SpreadsheetType

Type of spreadsheet. The default is acSpreadsheet TypeExcel8. See the help documentation for a complete list and explanation.

TableName

String expression that contains a table name or a SQL statement if you want to export data based on a SQL statement.

FileName

Filename and path of your spreadsheet.

HasFieldNames

Use True to use the first row of the spreadsheet as field names and False to treat the first row as data. False is the default.

Range

Valid range of cells or named range in the spreadsheet that you want to import from. Leave blank to import an entire spreadsheet. Using with Export will cause an error.

UseOA

Optional variant.

Now we'll walk through an example of how you might use the TransferSpreadsheet method to export data to a spreadsheet. Suppose you want to export the contents of the Employees table you just imported from Northwind into a spreadsheet so you can e-mail or send it to a colleague. The following code will create a new spreadsheet called Employees.xls in the temp directory.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _ "tblEmployees", "C: \Temp\Employees.xls"

An example of the spreadsheet created from the preceding command might look like the one shown in Figure 7.2.

You can also use ADO to select, insert, update, and delete the underlying data in most of the data sources in this chapter by specifying the correct ADO provider (in this case Excel).

0 0

Post a comment