Automating Export to Excel from VB code

[nickfatool]

CR8.5Dev, VB6, ODBC

I have a report that is always exported to Excel, always "7.0 Extended" and Column width always based on Page Header, plus I'd also like to control the resulting file name via code as well.
This would save users a few extra keystrokes, plus ensure it ends up in the format originally intended.

I found I can access most of this stuff -

Report.ExportOptions.DestinationType = crEDTDiskFile
Report.ExportOptions.FormatType = crEFTExcel70
Report.ExportOptions.DiskFileName = "c:\ExportedCR.xls"

But I cannot find the option to use column widths based on Page Header, are they available?

Any clues would be appreciated.

Thanks,

Rob Dexter.

 

[nickfatool]

Any Clues?
Anyone?
Bueller?

 

[mwardle]

The option you want is ExportOptions.ExcelAreaType. This has options like Details and Page Header to base your column widths on

 

[nickfatool]

Hi & tanks,

I tried -

Report.ExportOptions.DestinationType = crEDTDiskFile
Report.ExportOptions.FormatType = crEFTExcel70
Report.ExportOptions.DiskFileName = "c:\ExportedCR.xls"
Report.ExportOptions.ExcelAreaType = crPageHeader

It didn't have the same result as previewing then manually exporting the report to Excel using "Page Header"

It (ExcelAreaType = crPageHeader) doesn't appear to make any difference to the resultant Excel file at all.

Hmmmm