microsoft access report export to excel

Question

I have a table with the following structure:

MainCAT    SubCAT  Name    Description
MAIN CAT 1  SUB CAT 1   NAME 1  DESCRIPTION 1
MAIN CAT 1  SUB CAT 1   NAME 2  DESCRIPTION 2
MAIN CAT 1  SUB CAT 2   NAME 3  DESCRIPTION 3
MAIN CAT 1  SUB CAT 2   NAME 4  DESCRIPTION 4
MAIN CAT 2  SUB CAT 3   NAME 5  DESCRIPTION 5
MAIN CAT 2  SUB CAT 3   NAME 6  DESCRIPTION 6

I have created a report with works fine where I use "Group on" at column MainCAT and SubCAT. But I would like to have the possibility to export contend to excel, and it should be structured like this:

MAIN CAT 1  
SUB CAT 1   
NAME 1  DESCRIPTION 1
NAME 2  DESCRIPTION 2
SUB CAT 2   
NAME 3  DESCRIPTION 3
NAME 4  DESCRIPTION 4
MAIN CAT 2  
SUB CAT 3   
NAME 5  DESCRIPTION 5
NAME 6  DESCRIPTION 6

Any idea about how to create a report or anything else there can do that in MS access?

Answer 1

Maybe I missed something but reporting your desired structure shouldn't be a big problem. Put NAME and DESCRIPTION on your detail-layer and configure the two grouping-layers as already mentioned in your post.

Exporting reports from Access to Excel is a little tricky. Although the build-in-function for exporting to excel is a nightmare (compared to other reporting frameworks) and does not export formatting at all, there are two useful things to notice:

1. the export can be invoked by code ('DoCmd.OutputTo')
2. it exports the grouping structure into the spreadsheet

By collapsing/hiding those groups from bottom to top and using 'Range.SpecialCells(xlCellTypeVisible)' you can easily manage to apply different formatting styles to cells of individual layers. I was tasked with creating a formatted excel-export a few years ago and hit two significant limits with this approach:

1. .SpecialCells is a rather slow command as it internally simply loops over your sheet
2. the number of groups within one grouping-layer that can be created in excel is/was limited to 8000-something (Office2k3 back then)

In practice we had a huge A3-financial-report with up to six grouping-layers whose export did perform well on up to about 50.000 records in a 20- to 25 field-query.