Docmd.Transferspreadsheet

Kingz

Member
Local time
Today, 02:15
Joined
Mar 19, 2024
Messages
63
Hi, I was wondering if I could rename an excel export. I'm actually surprised there isn't a "sheetname". I guess I could rename my query table but I don't want to, die to organisational reasons. Here is my statement:

StrTable="3_boxphase"
Docmd.TransferSpreadsheet Transfertype:=acexport, spreadsheettype=acspreadtypeexcel12xml, table name:= strTable filename:=strdestpath, hasfieldnames:=True

So strTable is a query name, but I would like to call the sheet "Box"
 
Not with that command. You could edit the sheet afterwards and rename?
 
Ah, I've found it... It's "Range" oddly enough ;) Thanks
 
I guess I could rename my query table but I don't want to, die to organisational reasons
Saved under new name (CreateQueryDef, ...):
Code:
SELECT * FROM ExistingName
Can your organization cope with something like that?
 
Ah, I've found it... It's "Range" oddly enough ;) Thanks
No it is not. :(

From https://learn.microsoft.com/en-us/office/vba/api/access.docmd.transferspreadsheet


RangeOptionalVariantA string expression that's a valid range of cells or the name of a range in the spreadsheet. This argument applies only to importing. Leave this argument blank to import the entire spreadsheet. When you export to a spreadsheet, you must leave this argument blank. If you enter a range, the export will fail.
 
Ah, I've found it... It's "Range" oddly enough ;) Thanks
It's one of those things you'll find out only by accident. But as the documentation shows, it's not guaranteed to work forever. Cheers!
 
A safer solution is to create a query that has the name you want for the Worksheet. That query then selects the data query and is only used for exporting.
 
No it is not. :(

From https://learn.microsoft.com/en-us/office/vba/api/access.docmd.transferspreadsheet


RangeOptionalVariantA string expression that's a valid range of cells or the name of a range in the spreadsheet. This argument applies only to importing. Leave this argument blank to import the entire spreadsheet. When you export to a spreadsheet, you must leave this argument blank. If you enter a range, the export will fail.
It works for me! I read it somewhere and tried it. I think the main purpose is give you a chance to say exactly where, say, "Main!B2:c7", but just the name seems to change the sheet name from the table table to that.
 
It works for me!
When the documentation says that it doesn't work, you should be worried, very worried rather than think you've put something over on Access.
 
Care to share how you did it?
I just tried with 2019
and get
1713645214235.png

and not even trying a range?


1713645163605.png
 
Best I can see is that the range parameter will at least allow a sheetname on export, but not an actual range?
 

Users who are viewing this thread

Back
Top Bottom