VBA code to export data with formatting and layout

[blackduck]

Access07 has wizard to export to excel, and you can check the box for "export data with formatting and layout" and this exports perfectly.

But I need to be able to code it. Currently I can export using the following code:

Private Sub cmdExcel_Click()
'Export query to excel
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "Percentage_Crosstab", _
Application.CurrentProject.Path & "\" & cboPeriod & " Percentage" & ".xls", True
DoCmd.Close acForm, "frmPercent"
End Sub

This code doesn't export formatting. Surely there is some code now that can do this if the wizard can?

Please help, its driving me insane.

 

[GKChesterton]

I don't have 2007, and furthermore I haven't done this myself ever, but can you ...

DoCmd.RunCommand(acCmdExport)

[purple]If we knew what it was we were doing, it would not be called
research [blue]database development[/blue], would it? [tab]-- Albert Einstein[/purple]​