Docmd.Transferspreadsheet (1 Viewer)

[Kingz]

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"

 

[Gasman]

Not with that command. You could edit the sheet afterwards and rename?

 

[Kingz]

Ah, I've found it... It's "Range" oddly enough Thanks

 

[ebs17]

I guess I could rename my query table but I don't want to, die to organisational reasons

Saved under new name (CreateQueryDef, ...):

SELECT * FROM ExistingName

Can your organization cope with something like that?

 

[Gasman]

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

Range

Optional

Variant

A 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.

 

[theDBguy]

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!

 

[Pat Hartman]

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.

 

[Kingz]

No it is not.

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

Range

Optional

Variant

A 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.

 

[Pat Hartman]

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.

 

[Gasman]

Care to share how you did it?
I just tried with 2019
and get

and not even trying a range?

 

[Gasman]

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