Exporting to Excel: Formatting Question

[nico]

I created a macro (OutputTo Action) to export an active Report to Excel format. Everything works fine, except for a formatting issue. The Table that the Report is based on includes 3 combo box fields ("Hour", "Minute", and "AM/PM"). The Query for the Report includes a new column: "Time". This column combines the 3 fields into the following format: [Hour] & ":" & [Minute] & " " & [AM/PM]. In the Report View, the data displays correctly. But when I export to Excel, it appears in the default "General" format. If I format the cells to "Time" format, it works, but I was wondering if there was a way to have the data exported into the same Excel template file each time or if I could code in the proper formatting. Thanks for your help.

 

[Derek Brown]

I haven't tried it but I wonder if exporting using the 'formatted' option would give you the solution.
However, I tend to use the reverse approach: in Excel I use Data | Import External Data | New Database Query and navigate to my Access Query as the data source. After the first import I then use the Data Range Properties (on that same menu) to ensure that a prompt is given to offer a refresh of that data on opening the Excel workbook. The Excel worksheet formatting does not get changed by the import and you can add extra columns, to the imported dataset, in Excel with formulas etc.