Exporting Multipage Report From Access to Excel

[cbeltrante]

Hello,

I'm using code to export a multiple page MS ACCESS report to Excel. The problem I'm having is that it exports into 2 or 3 or more different workbooks because of the multiple pages.

Is there any work around to resolve this problem???

Thanks,
Chris

[OBP]

Is there a special reason for using the Report to output to Excel, if you just need the data you can output a Query in to 1 Excel Sheet?
When you exported the Report did you specify the Worksheet Name?
If you did you should only get one Workbook

[cbeltrante]

Looking at the code, they're using the "OutputTo Method" in MS Access with a template created.

The reason is that most of the calculations are being done on the report with grouping and total sums.

[cbeltrante]

Does anyone know of a fix to my problem?? I still haven't found the answer yet. Thanks!!!

[OBP]

cbeltrante, is a single worksheet Name specified in the Docmd.OutputTo?

[cbeltrante]

Yes, here's the code:

We're using a table that has the report names and their suffixes in it.



strDocName = rs("ReportName")
strExportName = rs("ExportName") & "." & rs("Format")
strFileName = Me.txtReportFolder & "\" & strExportName

DoCmd.OutputTo acReport, strDocName, acFormatHTML, strFileName, , strTemplate

[OBP]

cbeltrante, please note that the output Format is acFormatHTML and for Excel it should be acFormatXLS.
Also the Export name should be
strExportName = rs("ExportName") & ".xls"

The extension that you currently have looks as if it is "setable" by the User, using rs.("Format")

[cbeltrante]

The extension is set in the table and it has an .xls extension.

They want to have it as HTML report within an xls spreadsheet.