Export to Excel range

Ken Snell, Access MVP, has made some investigations on exporting data from Access to Excel spreadsheet, specifying range argument. He was very kind and allowed me to post results here. Thank you Ken!

BTW – Access help states that this Range argument is only for importing, and for export it will fail. Thanks to Bill Mosca, Access MVP, who mentioned this in our discussin!

Ok, here a story from Ken:

Having read some posts in newsgroups about using the Range argument for exporting queries/tables to EXCEL file, I decided to do some testing today to figure out what actually works and what doesn't work when using this argument (note that this use is an undocumented feature in ACCESS).
Here are the results of my tests for others' info/entertainment.

EXCEL FILE DOES NOT ALREADY EXIST
If the EXCEL file will be created by TransferSpreadsheet, the Range argument can be used to create a range in the new file that describes the cells that contain the exported data on the worksheet. This Range argument also is used to name the worksheet onto which the exported data are written. This overrides the normal operation of TransferSpreadsheet, which is to name the worksheet using the name of the table or query being exported. For example, this action:
DoCmd.TransferSpreadsheet acExport, _
acSpreadsheetTypeExcel9, "QueryName", _
"C:\Test.xls",, "MyName"

will create the file "C:\Test.xls" and the data will be written onto the first worksheet, which will be named MyName (the Worksheet.Name property, not the Worksheet.CodeName property); and the cells into which the data are written will be a Range named MyName in the new file. This range will include the field names that are exported as the first row of data, and the range will begin in cell A1.

EXCEL FILE ALREADY EXISTS
The Range argument can be used to identify the actual Range into which the exported data are written. TransferSpreadsheet ignores worksheet names when looking for the Range in the workbook file. It looks specifically for a defined Range of cells.
However, it is in this situation where I believe many posters have run into problems with this undocumented feature.
If the Range exists (cell range, that is) AND if that range encompasses more than a single cell (at least two cells), the data are exported to that range of cells. If the number of records and/or fields are more or fewer than the "size" of the range (number of rows and columns), the data are correctly exported and the Range is redefined to match the size of the exported data in terms of width and depth of the range (number of rows and number of columns). Note that any formatting in the cells within this range is retained (e.g., Bold, Highlight color, font color, etc.).

1. If the Range does not exist in the workbook file, TransferSpreadsheet creates a new worksheet, names it with the Range argument value, writes the data onto that worksheet, and creates a new Range (also named with the Range argument value)to define the cells that contain the exported data. If a worksheet with the same name as what is in the Range argument already exists in the workbook file, the new worksheet that is created is named using standard EXCEL process, namely, the Range argument name followed by a 1. Thus, if I use MyName as the Range argument and export to an existing file, I can get one of the following results:

• File already contains a worksheet named MyName but does not contain a Range named MyName: A new worksheet named MyName1 is created, the data are written onto that worksheet, and a new Range named MyName is defined for the cells that received those exported data.


• File does not contain a worksheet named MyName and does not contain a Range named MyName: A new worksheet named MyName is created, the data are written onto that worksheet, and a new Range named MyName is defined for the cells that received those exported data.

2. If the Range exists (cell range, that is) AND if the Range consists of a single cell (e.g., A1), then strange things happen -- note that it doesn't matter if the Range starts in cell A1 or not. And because of these strange things, this is where the feature is unusable for exporting. I haven't defined exact "rules" to describe what happens (although it appears that how far the range is moved appears to be "the original row number plus 93" columns (if the Range was originally in column A), but here are my observations in this situation (I won't guarantee that you won't see different behaviors):

• If the worksheet name is the same name as the Range name, and the Range begins in cell A1, the exported data are written to the worksheet that contains the Range specified in the TransferSpreadsheet action, and these data begin at cell A1 (with the field names row) -- BUT the existing range is moved to cell CQ1 (94 columns to the right), and there is no Range created for the cells that contain the exported data. Any further attempt to export to this worksheet using the same Range argument generates an error because the "move" of the range will extend beyond the column limit of the worksheet.


• If the worksheet name is the same name as the Range name, and the Range begins in cell A5, the exported data are written to the worksheet that contains the Range specified in the TransferSpreadsheet action, and these data begin at cell E5 (with the field names row) -- BUT the existing range is moved to cell CU5 (98 columns to the right), and there is no Range created for the cells that contain the exported data. Any further attempt to export to this worksheet using the same Range argument generates an error because the "move" of the range will extend beyond the column limit of the worksheet.


• If the worksheet name is not the same as the Range name, and the Range begins in cell A1, the exported data are written to a new worksheet that is named the same as the Range argument value, and the existing Range is then moved to cell IV1 (the last column in the sheet) on that new worksheet, and there is no Range created for the cells that contain the exported data.


• If the worksheet name is not the same as the Range name, and the Range begins in cell A30, the exported data are written to the existing worksheet that contains the named Range but are written into the cell block where the left top anchor is cell AD150 (29 columns to the right), the existing Range is then moved to cell DT30 on that worksheet (123 columns to the right), and there is no Range created for the cells that contain the exported data.


• If the worksheet name is not the same as the Range name, and the Range begins in cell A150, the exported data are written to the existing worksheet that contains the named Range but are written into the cell block where the left top anchor is cell ET150 (149 columns to the right), the existing Range is then moved to cell IJ150 on that worksheet (243 columns to the right, and there is no Range created for the cells that contain the exported data.


• If the worksheet name is not the same as the Range name, and the Range begins in any column except column A (e.g., the Range is defined as cell B1), an error occurs because the "move" of the range will extend beyond the column limit of the worksheet, and no data are exported.

Update: This article on Japanese, thanks to Yu Tang!