Question Regarding TransferSpreadsheet w/ Formats

[Brent E]

Good day,

I currently am using macros to automatically export queries to Excel using
the transfer spreadsheet function, but there appears to be no way to state
whether to maintain the formatting.

I notice if I do a manual export, I get the option to check the box to
maintain formatting; however, can't do so w/ the TransferSpreadsheet.

Is there a way to set the macro to do this, or to save the macro as VBA and
add some code to apply the formatting to the export using the TransferSpread
function?

Thanks.

 

[Douglas J. Steele]

You need to use the Format function in the query you're exporting, rather
than relying on the Format property of the fields.

 

[Brent E]

I was not able locate a format function in the query design view or query
data view.

I have the table field formatted as hyperlink. The links seems to appear
fine when I run / view the query. The breakdown appears to be some place
between the query execution and the macro export.

Please advise where to find the query format function you mentioned.
Thanks,

 

[Douglas J. Steele]

I was talking about the VBA Format function, nothing specific to queries.

However, that won't make a difference with hyperlinks.

Afraid I don't know of any way to preserve them using macros.