Automate Exporting

[leighms]

Hi

I currently have a number of reports I export to excel each month. Currently I have them in saved exports and at the end of each month I go in and click on each one to export to excel.
Is there a way I can either get access to do this automatically for me or even create a button to make life a lot easier. (I export over a dozen reports each month)

Thanks

 

[noboffinme]

Hi leighms,

Here's some code I use to export from Access to Excel & saves the files to the 'C' Drive.

Depending on the version of Excel, you may want to change the extension to .xlsx for the 'DoCmd.OutputTo ... ' part.

You may want to add a Loop that select the various report names from a list on one of your tables to export all of them.

Then it should be a one click process.

Private Sub clsd_projects_Click()
'This is the Closed_Projects Summary Report code
'This code sends this report to excel output
Set XLApp = New Excel.Application
DoCmd.OutputTo acOutputReport, "All Closed Projects", acFormatXLS, "C:\TEMP\Closed_Projects.xls", True
    Set XLwkbk = XLApp.Workbooks.Open("C:\TEMP\Closed_Projects.xls")
    XLwkbk.Sheets(1).Range("A1:IV65536").Select
     
   'Add some formatting
    With XLwkbk.Sheets(1)
        .rows("1:1").Font.Bold = True
        .columns("G:G").VerticalAlignment = xlTop
        .columns("G:G").HorizontalAlignment = xlCenter
        .columns("J:J").VerticalAlignment = xlTop
        .columns("J:J").HorizontalAlignment = xlCenter
                
    End With
    
End Sub

 

[leighms]

This works great, thanks for your help