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

[juliana28]

Hello,
------------------------------------------------------------------

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

 

[SkipVought]

hi,

keying in all the information one person at a time

Keying into what?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MazeWorX]

Loops are relativly easy. You could base the loop on a recordset of the tbl_Distribution.DeptID.

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

'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

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work

 

[juliana28]

@SkipVought
Necessity is the mother to all invention… ?
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.

 

[juliana28]

@MazeWorX

Hi,
Thanks.

I tried the below and I am getting a compile error. What am I doing wrong.

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

 

[MazeWorX]

DoCmd.OutputTo acOutputReport, timesheets, acFormatXLS, , -1

The report name should be in quotes "timesheets" also if you leave the outputfile argument blank then access will prompt you for a file name.

another possibility(probability) is you have a missing reference. I was able to compile without errors

Also the loop doesn't really do anything but run the same report for as many records as there are in the tbl_Distribution. what you want to do is to run only the report for that dept(current record of the loop)

Another option below: compiles but hasnt been tested

Private Sub ExportTimesheets()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
    Dim strSQL2 As String
    Dim strFileName As String

    On Error GoTo ExportTimesheets_Error

    strSQL = "Select Distinct tbl_Distribution.* From tbl_Distribution;"

    Set db = CurrentDb()
    Set rs = db.OpenRecordset(strSQL)

    rs.MoveLast
    rs.MoveFirst

    Do Until rs.EOF
        strSQL2 = "SELECT tblDetail.* " _
                & "FROM tblDetail " _
                & "WHERE [tblDetal].[Dept ID]=" & rs!DeptID
        strFileName = [rs]![Department Name] & " Payroll"

        DoCmd.TransferSpreadsheet acExport, , strSQL2, strFileName, True
        rs.MoveNext
        strFileName = ""
        strSQL2 = ""
    Loop

    On Error GoTo 0
    Exit Sub

ExportTimesheets_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ExportTimesheets"
End Sub

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work

 

[MazeWorX]

Sorry missed something

strSQL2 = "SELECT tblDetail.* " _
              & "FROM tblDetail " _
              & "WHERE [tblDetal].[Dept ID]=" & rs!DeptID

Should be

strSQL2 = "SELECT tblDetail.* " _
              & "FROM tblDetail " _
              & "WHERE [tblDetal].[Dept ID]=" & rs!DeptID & ";"

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work

 

[juliana28]

Thanks so much for your help.

I tried the below and I get eoor 3265. I am not sure what I am doing wrong???

Private Sub ExportTimesheets()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strSQL2 As String
Dim strFileName As String

On Error GoTo ExportTimesheets_Error

strSQL = "Select Distinct tbl_Distribution.* From tbl_Distribution;"

Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL)

rs.MoveLast
rs.MoveFirst

Do Until rs.EOF
strSQL2 = "SELECT tblDetail.* " _
& "FROM tblDetail " _
& "WHERE [tblDetal].[Dept ID]=" & rs!DeptID & ";"
strFileName = [rs]![Department Name]

'DoCmd.TransferSpreadsheet acExport, "timesheets", strSQL2, strFileName, True
DoCmd.OutputTo acOutputReport, "Timesheets", acFormatXLS, , -1



rs.MoveNext
strFileName = ""
strSQL2 = ""
Loop

On Error GoTo 0
Exit Sub

ExportTimesheets_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ExportTimesheets"
End Sub

 

[juliana28]

I put the database out there.

http://www.mediafire.com/?3gxim6xen1ifa

Thanks again....

 

[Gammachaser]

Well, one thing I see is a typo where tblDetail is spelled tblDetal:

strSQL2 = "SELECT tblDetail.* " _
           & "FROM tblDetail " _
           & "WHERE [b][tblDetal][/b].[Dept ID]=" & rs!DeptID & ";"

You could eliminate that problem by just using

"Select * FROM tblDetail WHERE [Dept ID] = " & rs!DeptID

It is not necessary to qualify your field with the table name in a single table query. It may be a good habit to get into for multiple tables, but I am not that much a creature of habit. Your mileage may vary.

Also, I never put the ";" at the end of a SQL statement and Access never complains (up through 2003, anyway.) And I recommend building tables with single word field names (e.g. avoid fields like 'Dept Id' and use 'DeptID' instead) that way you don't need the brackets.

 

[avayaman]

I am much more comfortable with macros in excel than Access, why not create a macro in excel to sort the data the way you want once its all in there?

Paul Beddows

Consulting, Avaya/EAS implementation, Training
Vancouver, Canada
E-mail paul at natcoa.com