HowTo save Access report as Excel 97-2002 file using VBA

[Twan Kennis]

It seems to be impossible to save an Access report to the Excel 97-2002
format using VBA without user-interaction.

I tried several Formatting-parameter-values:
DoCmd.OutputTo(acReport, ..., acFormatXLS, ...)
DoCmd.OutputTo(acReport, ..., "Microsoft Excel 97-10 (*.xls)", ...)
DoCmd.OutputTo(acReport, ..., "Microsoft Excel 97-2002 (*.xls)",
....)

but all result in the same old/standard Excel format, instead of the new
Excel format.

Only a blanc formatting-parameter within the VBA-command
DoCmd.OutputTo(acReport, ..., "", ...)

does show a picklist with export-formats from which the user can select
the new Excel-format with results into a succesful Excel file.

Does somebody know the "magic" parameter-value to save an Access report
to the Excel 97-2002 without user-interaction?

Thanx in advance.

Twan Kennis
SKB Vragenlijst Services, The Netherlands

 

[Douglas J. Steele]

What about using TransferSpreadsheet instead of OutputTo?
TransferSpreadsheet has a parameter acSpreadsheetTypeExcel97 (which equals
8) that should do what you're looking for.

 

[Twan Kennis]

Thank you for your answer.

According to the Help-file TransferSpreadsheet exports an Access Table
instead of an Access Report.
Anyway, I tried but got an error which refers to an unknown table
"rpt..." (and I do need the lay-out as well).

I find out, I'm not anlone with this problem:
http://www.accessmonster.com/Uwe/Forum.aspx/access-externaldata/3328/Sending-report-to-Excel-2002
But he answer is still unknown.

B.t.w. already got a half-way workaround by excuting the "Analyze It
with Microsoft Excel"-command (Id: 566).
CommandBars.FindControl(ID:=566).Execute
But this really starts Excel and needs a Save-command to the needed
filename.

Thanx anyway for your help.

Twan Kennis
SKB Vragenlijst Services, The Netherlands

 

[Twan Kennis]

For those who wanna know the solution....

Create a new Registry Key:

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\10.0\Access\Report
Formats]
"MyOwnFormat"="xls,SOA_RptToBIFF, Biff8,Microsoft Excel Biff8
(*.xls),0"

In Access output your Report with your own formatting:

DoCmd.OutputTo(acReport, <ReportName>, "MyOwnFormat", <Output File
Name>, False)

That's all.

Twan Kennis
SKB Vragenlijst Services, The Netherlands

 

[Computer Wizard]

I ran into the same problem I had to do 2 things

1) Make sure the Access file is a 2002-2003 file (Tools - Database Utilities - Convert Database)
2) use the following code DoCmd.OutputTo acReport, [Name of Rep], "Biff8"

The key is the last "Biff8"

I also tried various other strings.