batch export of reports

[barrhaven01]

Hi there,

I was wondering if anyone could suggest a method using only access ( ie no additional applications) to batch export reports to excel. Basically i have a report with a query for its source data, and the query is based on the selection of an employee from a drop down list. It would be terrific if i could batch export a report for each employee.

Hopefully someone out there has the answer

[ADezii]

I recently responded to a very similar Thread. and I'll try to find it for you. In the meantime, the process is:

1. Open the Report in Design View.
2. Dynamically set the Record Source of the Report based on Employee IDs contained within some kind of Looping Structure.
3. Close and Save the Report.
4. Export the Report.

[ADezii]

Well, I couldn't find the Thread that I was referring to, but I re-wrote it from scratch, so here goes:

ASSUMPTIONS:

1. Table Name: Employees
2. Report Name: rptEmployees
3. Primary Key in Employees Table: EmployeeID

Dim MyDB As DAO.Database
Dim rpt As Report
Dim rstEmployee As DAO.Recordset
 
Set MyDB = CurrentDb()
Set rstEmployee = MyDB.OpenRecordset("Employees", dbOpenForwardOnly)
 
With rstEmployee
  Do While Not .EOF
    DoCmd.OpenReport "rptEmployees", acViewDesign
    Reports![rptEmployees].RecordSource = "Select * From Employees Where Employees.[EmployeeID] = " & _
                                           ![EmployeeID]
    DoCmd.Close acReport, "rptEmployees", acSaveYes
    DoCmd.OutputTo acOutputReport, "rptEmployees", acFormatXLS, CurrentProject.path & "\Employee_" & _
                                   ![EmployeeID] & ".xls"
    .MoveNext
  Loop
End With

[barrhaven01]

thanks for the code.... just one question.... How do i implement this loop code as the record source, it seems to only want to let me select queries or tables, i cant get to code builder from there.

thanks again for all the help thus far

[ADezii]

The code will do it all, just set the RecordSource of rptEmployees to the Employees Table, set the Control Sources of all Bound Fields, and you are set to go. Execute the code from wherever you wish.

[PianoMan64]

@barrhaven01
Hello barrhaven01,

In order to do what Adezii is speaking of, you need to open up the code module and create a subroutine that will make the changes that he's speaking about to your report. You're not able to do set it on the RecordSource field within the report. It has to be done in CODE.

Hope that helps,

Joe P.

[ADezii]

Thanks Joe P. for the assist, sometimes I miss the obvious! (LOL).

[barrhaven01]

im incredibly stupid.... i cant seem to get this to run.

Also I was wondering how the code would change if rather than a report for each employee, if it summarrized each employee by office building.