TransferSpreadsheet

[Chad]

I am using the DoCmd.TransferSpreadsheet functionality in VBA to export a
query into a specified range in excel as is outlined in Ken Snell's write-up
found here:

http://www.accessmvp.com/KDSnell/EXCEL_Export.htm#WriteRstFile

I ran into an additional quirk and wanted to verify that this was actually
the case:

When exporting to a named range within excel, everything works fine as long
as the export is not overwriting anything. If, however, a user is sending
information to overwrite data that is already in the spreadsheet, then the
named range must be greater than the range of the export, otherwise, an error
message will appear stating that access cannot expand the named range.

Can someone please verify this and let me know if this is the case?

Thanks!

 

[Ken Snell]

That code example that you cite does not directly use any EXCEL ranges that
might exist in the workbook file, so I'm curious about what your code looks
like because you get such an error?

Also, see this article:
Using the Range Argument of TransferSpreadsheet when Exporting Data to an
EXCEL File (VBA)
http://www.accessmvp.com/KDSnell/EXCEL_Export.htm#ExpRange

It gives examples of what happens when you use TransferSpreadsheet to export
to existing ranges in EXCEL workbooks; perhaps you might find your situation
described there.