Access VBA auto generate mass report by group to Excel

This Access tutorial explains how to auto generate mass report by group, for example, auto generate reports by different department and then export to Excel.

You may also want to read:

Access VBA DoCmd.OutputTo Method

Access VBA DoCmd.TransferSpreadSheet Method

Access Export all Queries to Excel

Access VBA auto generate mass report by group to Excel

Assume that we have a staff list of 1000 employees in 20 departments, our goal is to export 20 staff list to Excel, one department for each staff list. There are several ways to achieve this task:

Approach 1

In Excel, apply Filter in Excel, then do 20 times of filtering of department and then copy the data to a new worksheet.

This is the most common method but is the slowest. It can be speed up by writing a Macro to do that but it can be complicated if different department requires different fields.

If you really want to do that you can read my previous posts how to use Autofilter and Copy Range in VBA.

Excel VBA Add Autofilter Cancel AutoFilter sorting in worksheet

Excel VBA Copy Range and Paste Method

Approach 2

In Access, create 20 Query for different departments and then export each Query as Excel.

If you need to customize each report for each department, I recommend you to take this approach but write a Macro to specify which 20 Query to output using DoCmd.OutputTo Method. I used this approach in my previous company where they have 70 department/team, and they have no generic rule to define the department and team grouping and even worse some reports require specific fields.

Anyway what I needed to do is to write 70 Query plus one Macro in the initial setup, then I only needed to rerun the Macro to generate all reports.

Approach 3

In Access, create a mapping table to define the employee grouping and then inner join the staff_list. It does not matter whether you group by Department ID, by Department Name or even by Employee ID, the purpose is to assign each individual employee an ascending Group Number. Finally export the report automatically as Excel for each group.

I recommend you to use this approach assume that the number of fields are the same across all reports. The only thing you need to do is to maintain the mapping table if there is any change.

Example of Approach 3

Assume that you have a table called [staff_list] as below

generate_mass_report

Create another mapping table [Dept_grouping]

generate_mass_report_02

Create a Query called [staff_list_with_grouping], inner join [staff_list] with [Dept_grouping] so that each employee has a Group Number.

generate_mass_report_03

Insert the below code in Module

Public Sub exportQuery()
    Dim qdf As QueryDef
    For i = 1 To DMax("[Group Number]", "[staff_list_with_grouping]")
        strSQL = "Select * FROM [staff_list_with_grouping] WHERE [Group Number]=" & i
        deptName = DLookup("[Department]", "[staff_list_with_grouping]", "[Group Number]=" & i)
        On Error Resume Next
        DoCmd.DeleteObject acQuery, "tempQry"
        On Error GoTo 0
        Set qdf = CurrentDb.CreateQueryDef("tempQry", strSQL)
        DoCmd.OutputTo acOutputQuery, "tempQry", acFormatXLSX, "C:\test\" & deptName & ".xlsx", False
    Next
    DoCmd.DeleteObject acQuery, "tempQry"
End Sub

What I am trying to do above is to create a loop from 1 to the maximum Group Number in [staff_list_with_grouping]. For each loop, I use that Group Number as a Query criteria for [staff_list_with_grouping] and then export the Query out to the folder.

Now three staff lists are generated in the test folder.

generate_mass_report_04

 

 

 

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist
- Microsoft Specialist
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *