[AccessD] How to Grab Report Subtotals and Push them to Excel

All,

We have a rather complicated Access 2007 report that has a “Group” in order to show sub-totals on about 10 fields.

Recently, the manager of our Accounting Department has asked to have the sub-total amounts placed in an Excel file as this is what an outside accounting firm is asking for.   

I have other Access applications that populate Excel via Windows Automation, so I understand this part.

However, I don’t understand how to correctly grab the sub-totals.

I have experimented with the “On Format” and “On Paint” events in the Footer that contains the sub-total fields.

When I look at these fields via a Msgbox, the Msgbox is shown multiple times, therefore I don’t think that I can grab the sub-totals on either the “On Format” or “On Paint” events.

Is there a good way to capture sub-total fields? (make them available in VBA code)

Thanks,
Brad