Report to save as Excel file

[ashu.doc]

Hi Everyone


I want to save my report as excel file on the user computer with
a click of a button. I have tried this code:

Private Sub
Cmd_ReporttoExcel_Click()
DoCmd.OutputTo acOutputReport, "Gastrolog Report",
acFormatXLS, "C:\Users\XX\Documents" & Format(Date, "yyyymmdd") &
".xls"
End Sub

But it is giving error 2282 - The format in which you
are attempting to output the current object is not available.

The other
part of this question is :
This
is something similar when I didn't have "PDF add in " in access 2007, when I
added PDF add in then option to save file in PDF was available in "output to"
action of macro. There is nothing like \excel add in. However when I can export
the data in excel sheet by Export function in access why don't I have option to
save file as excel in "output to " action in macro? I want to have this option
so that user can click a button in the form rather than in the top ribbon try to
find out how to transfer and save the excel sheet.

many thanks
Ash

[June7]

Is Documents a folder? Need "\" between it and the file name.

[ashu.doc]

Do you mean this?

Private Sub
Cmd_ReporttoExcel_Click()
DoCmd.OutputTo acOutputReport, "Gastrolog Report", acFormatXLS, "C:\Users\XX\Documents\GastroLog" & Format(Date, "yyyymmdd") & ".xls"
End Sub

I have tried this but same error. Is it because the "save as Excel" add in is not available is access 2007?

[June7]

I have never had issue with OutputTo for Excel in 2007 or 2010. I have never installed any 'save as Excel add-in'. Do you have Excel application installed?

[ashu.doc]

Yes I have it installed, you are right, that it should not have an issue because both the access and excel are from Microsoft and if you see the data can be exported from access to excel by export option but the macros isn't having an option to export it as excel?
I have tried to change the code a little bit and instead of acFormatXLS I have used acFormatExcelWorkbook, which on click event opens a little window to choose several file format to save like html, rtf, pdf, xml etc. and when I click html as option it is saving the report in excel format with an error message saying the file you are saving is corrupt although it is saving ok in the end.

[ItsMe]

I did not have any trouble with either of the following using Office 2013. Make sure you do not have an existing Excel file in the directory and you are using the .xls extension, not the xlsx.

DoCmd.OutputTo acOutputReport, "ReportName", acFormatXLS, "C:\Test\ExcelFiles\Test.xls"

DoCmd.OutputTo acOutputReport, "ReportName", "Microsoft Excel (*.xls)", "C:\Test\ExcelFiles\Test2.xls"

[ashu.doc]

Hi Everyone
I have sorted this out in a different way, as we know access 2007 SP1 does not support saving report as excel, but it does support saving table data as excel!! So I just had to change that in Macro and works perfect.
Thanks all

[June7]

Apparently I never tried to export report with Access 2007, just table/query, and never became aware of that issue. Thanks for the followup.