Auto Report To Excel .. (1 Viewer)

[GUIDO22]

I need to generate an Excel report but NOT using the shortcut menu and Wizard in access... From report preview...

I want to be able to use vba to run the access report and automatically save the excel report to a designated folder on disk.
(I have another routine i can use to then email the file on...)

So one command button press will do the lot!

Any ideas please?
Thanks in advance.

 

[bastanu]

Look into adding Docmd.OutputTo to your button's Click event:

Access VBA DoCmd.OutputTo Method - Access-Excel.Tips

This Access tutorial explains how to use DoCmd.OutputTo Method to export different objects to specific file types, for example, export Query to xlsx

access-excel.tips

Cheers,

 

[Pat Hartman]

You would export the data using TransferSpreadsheet and then open the spreadsheet for automation and format it. Or you could create the file totally with code. Using the TransferSpreadsheet method allows you to create a template and just fill it with data. You might not need OLE code at all.