Wednesday, January 2, 2008

TransferSpreadsheet: Specifiying the range or worksheet to export to

The help for the TransferSpreadhseet action says you can't do this, but that's not true. It says, in part:

TransferSpreadsheet Action

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 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. You can also link to data in a Lotus 1-2-3 spreadsheet file, but this data is read-only in Microsoft Access.

...

The TransferSpreadsheet action has the following arguments.

.....

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 Microsoft Access 97 or Microsoft Access 2000). If you are importing from or linking to a Microsoft Excel version 5.0, 7.0, Excel 8.0, or Excel 2000 worksheet, 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.

The last point is incorrect. You can specify a range when exporting. The following code will export the specified query to a specific worksheet within the workbook.

DoCmd.TransferSpreadsheet TransferType:=acExport, TableName:="Invoices", FileName:="c:\windows\temp\MyExcelWorkBook.xls", Range:="MyWorksheetName"

And this example exports it to a specfic named range withing the workbook.

DoCmd.TransferSpreadsheet TransferType:=acExport, TableName:="Invoices", FileName:="c:\windows\temp\MyExcelWorkBook.xls", Range:="MyRange"

1 comment:

Data Entry said...

Good blog Stephen - do you know if you can customize the worksheet name using TransferSpreadsheet or must it be the table name?