OutputTo Excel ... Date format question

[Bob Howard]

Hi all,

I have an application that currently performs an OutputTo referencing a
query, and the output goes to an Excel (XLS) file. Generally, everything is
fine. But I note that all date fields are winding up in the result as
"text" and not as dates. When I go to use the spreadsheet, I have to fiddle
around to set the format of the date columns.

What can I do, probably in the query, to set the appropriate date format?

Thanks in advance ...

Bob

 

[Ken Snell MVP]

You could try wrapping the date field with the CDate function and using that
calculated field in the query instead of the original field.

Or, probably a better approach, is to use TransferSpreadsheet action instead
of OutputTo.

 

[Bob Howard]

Thanks Ken ... the CDate worked. The displayed format (in the spreadsheet)
wasn't what I hoped for, but the cell's contents are correct. I'll use that
approach for now but look further into TransferSpreadsheet as well ... I'm
under a slight time constraint at this point. Bob

 

[Bob Howard]

I solved the "format" issue by setting a format in the column's property.
Thanks again.... bob

 

[Bob Howard]

Ken; I played around and seem to be able to get TransferSpreadsheet to
work. Still some kinks. The biggest is that I was also having OutputTo
open Excel ... but I don't see an option where TransferSpreadsheet will let
me do that. Any ideas?? Bob

 

[Ken Snell MVP]

TransferSpreadsheet does not give you the option to open the file after the
export is done. But that can be done with additional steps. Are you using
VBA or macro? Post info about the VBA code or macro so that we can see how
the filename is being set.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

 

[Bob Howard]

Hi. Since I can't use TransferSpreadsheet to open Excel, I'm going to stick
with OutputTo. Bob

 

[Douglas J. Steele]

It's just a single line of code to open the spreadsheet.

Application.FollowHyperlink "C:\Folder\File.xls"