create a loop that exports a report to excel based on a field

[julianahonorio]

I have a database that produces timesheets for employees based on employee information and grouped by department information in tblDetail. I have a macro that exports the report Timesheets into excel. I need one Timesheet report to for each department to be exported to excel. So I think I need to create a module that contains a loop that looks in the tblDistribution for the Department id the exports each report to excel. I am stuck on how to do this. Please help. I need to make this as easy as possible for the payroll person. At the moment they are printing reports and manually keying in all the information one person at a time. I have the two table names below...


tblDetail.EmpliD, Pay Group, fileNbr, Period End Date, Pay Date, Day, Hours, Code, Shift, Amt, Temp Dept, Dept ID, Department Name, Department Mgr, Deptsupv, Std Hours, Name, Mgr Name, Mgr Email

tbl_Distribution.Dept ID, Dept Timesheet Manager, Department TimesheetManager Email, Department Name

Thanks,
Juliana

 

[julianahonorio]

Sorry I should have added background and what I am trying to accomplish.

Necessity is the mother to all invention… J

They produce a timesheet report from Reportsmith that is connected to the HR system and fax to each department. The departments fax back the time sheets and Payroll manually keys time into the HR System from faxed Time Sheets. While I think designing a time keeping system is beyond me I thought I could help by trying to automate some of the functions. This would include producing timesheets from information downloaded from the HR system into Access. Exporting them into excel spreadsheets so that the Payroll person can email them to each department so each department can fill in the time and email it back. Then compiling the data in the spreadsheets back in Access, prepare audits to catch anything funky as well as create 1 file to upload to the HR system.

 

[gemma-the-husky]

what are you actually exporting into excel at the moment. The report, or the reports underlying query?

and how are you actually generating the excel sheet? manually? or with code? If with code, which command are you using?

 

[julianahonorio]

I am exporting the report into an excel spreasheet.

What I am having trouble is exporting that report into Excel by department.

I tried this but it is not working.

Private Sub EportTimesheets()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
strSQL = "Select Distinct tbl_Distribution.DeptID From tbl_Distribution;"
Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL)
rs.MoveLast
rs.MoveFirst
Do Until rs.EOF
docmd.OutputTo acOutputReport, timesheets, acFormatXLS, , -1
'This is where you run your report
'here you could run the report with vba instead of the macro 'based on the rs(DeptID) that is current
rs.MoveNext
Loop

End Sub