Ms Access 2007 report export to excel 2007

[Stan2man]

Hi is there a way to export a list (record set) of report from access 2007 to excell, keeping the format. I would like to automate the process using VBA.

Note : if you need a copy of the database I can send it, thanks

[June7]

Not sure what you mean by 'keeping the format'. There is a lot out there on this topic. Google: VBA Access export query. Here is one http://www.ehow.com/how_7438797_acce...ort-query.html

[Stan2man]

Thanks June, but I would like to export reports from access to excell, not query. If you have any idea on how to do so. Just let me know

[June7]

You can use the Export wizard. The output will not look quite like the rendered Access report because the export does not take Report and Page header info (seems like it used to in 2003) and data is put into cells. The report must be open in ReportView not PrintPreview to make the wizard available.

[Stan2man]

Thanks June7 for your quick answer, but I would like to know is there a way to do the samething using VBA, because I need to run the export several time. And also the report that will be call with the command contains a query that use a parameter (project_num).

So exactly I will have to run through a recordset that contains the project number
and produce an excell file for each project number.

Is it possible to export a report using TransferSpreadsheet acExport??

Thanks

Stan

[Stan2man]

Heres the command I'm trying to use : DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "report_name", "C:\Book1.xls", False, "Sheet2$"

[June7]

I don't remember if that works. The report might have to be open first. Might require DoCmd.OutputTo acReport