Rivers,
there are known problems with using the range argument when exporting.
The help says that the range argument does not work for exporting, only for
importing.
Here are my notes from Ken Snell - I have copied it all because I don't have
a link to this.
Export to Excel range
Friday, July 07, 2006
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 discussion!
Ok, here's 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.
can anyone help