Excel export problem

[Jeff Schneider]

I'm trying to use using the following code to export data to an existing
spreadsheet:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qryAccountCrosstab", _
"C:\Documents and Settings\jeff\Desktop\AccountExport.xls", ,
"Accounts"

When I run the code, it exports the data, but it creates a 2nd tab in Excel
called "Accounts1". Obviously, Access is finding the "Accounts" tab, but why
doesn't it use it? I've tried using different tab names, and every change
results in the same thing... it creates a new tab using the existing tab name
+ "1".

Is this a feature or bug?

 

[Jeanette Cunningham]

Jeff,
this a feature!
The export process thinks that Accounts is a named range in the workbook.
You put Accounts in the argument that tells the export process the name of
the range to export to.
It finds that the sheet called Accounts is already in use, so it creates a
new range and calls it Accounts1.

If you want to put data on an existing sheet, create a named range on that
sheet and use the name of the range in place of "Accounts"

The easiest way to understand it, is to set up some test exports using named
ranges and find out how it works.
To set up a named range in a worksheet, select the cells you want in the
range, then go Insert | Name | Define |
type in the name for your range, click OK.

Here is a sample. I set up a range called "Fridge" in a workbook called
RangeExport.xls
To export to this range with transfer spreadsheet use code like this:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qryExportRange", _
"C:\Documents and Settings\jeanette\Desktop\RangeExport.xls", ,
"Fridge"

Jeanette Cunningham

 

[Jeanette Cunningham]

ps

oops forgot to mention
Exporting to a range in a worksheet is undocumented - which means that you
use it at your own peril.
It works to a degree, but has some gotchas.
To read about the bugs, go to Alex Dybenko's web site and find the blog on
this topic.

Jeanette Cunningham