Export multiple reports to excel/retain formatting

[JillianM]

Hi guys,

I have a database that has several reports that need to be exported to Excel on a regular basis. I understand that you can write code to make this happen automatically for all of the reports at once, and even retain formatting. I would like the reports to save to a desktop folder called HR Reports. This is waaay outside of my knowledge base and I am hoping someone might give me a hand writing the code, or at least be able to point me in a reference direction where I can learn to do this myself. The current code for the command button, for the little it's worth is:

Private Sub CmdReportstoExcel_Click()
On Error GoTo Err_CmdReportstoExcel_Click

    Dim stDocName As String

    stDocName = "RptNewHireList"
    DoCmd.OutputTo acReport, stDocName

Exit_CmdReportstoExcel_Click:
    Exit Sub

Err_CmdReportstoExcel_Click:
    MsgBox Err.Description
    Resume Exit_CmdReportstoExcel_Click
    
End Sub

My report names are:

rptAnniversaryList
rptTSAnniversaryList
rptJPInternalExtentionList
rptJPExternalExtentionList
rptBirthdayList
rptTSBirthdayList
rptInternalExtentionList
rptExternalExtentionList
rptTerminationList
rptNewHireList
rptTSandPOList

Any help is greatly appreciated!!!