Export Access 97 Report to Excel

[misssaraliz]

I am exporting a report from MS Access 97 into Excel. The same report with different data will be exported daily using a command button that runs a visual basic command to export the report into Excel. The visual basic command also automatically assigns a new name to the report daily using the date function. Then this report will be emailed daily to several users. I need to find a way to set up a macro that will automatically run when the user opens the report. This macro will apply formatting such as changing page orientation, margins, field size, etc.

Do I run this macro from the command button in the VB?

Also, is there a command in the macro that I can include to tell it to delete columns if there is no data in them?

Thanks for any help you can provide. I will be cross-posting this since I'm not sure of the best place to put this.

 

[GIA]

What you describe does sound like Report_Open material rather than command button code. The button can open the report with DoCmd and do the SaveAs, rather than the formatting type operations which make sense being in the Open Event.

If you are using an aggregate query recordsource -- and if your data fits this suggestion -- you can to an extent kill 2 birds - the field suppressions and the field widths - by adding Max() fields to the report record source. E.g. your query source could be
[GaSales], [FlSales], [SCSales], [GaSalesMax], [FlSalesMax], [SCSalesMax]
where you could roughly use iif([GaSalesMax]>0... to suppress Georgia sales if zero (etc.).

AAR, looking at all of the functionality you are aiming for ... if you're unfamiliar with the Open Event of a Report, I suspect you need to spend a day or so learning and playing with a Que text or something regarding Report Event processing I "Outside of a dog, a book is probably man's best friend; and inside a dog, it's too dark to read" - G. Marx