ACC: How Reports Are Output to Microsoft Excel

• Microsoft Access versions 2.0, 7.0, 97

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

This article describes what happens to Microsoft Access groups, calculations, and labels when you export a report to Microsoft Excel.

MORE INFORMATION

In Microsoft Excel, the fields are laid out in columns with each row representing a separate record. The Microsoft Access Output To command uses the Outline feature of Microsoft Excel to format the report's groups.

Group Headers and Footers

Unlike groups in Microsoft Access, Microsoft Excel outlines have only a header or a footer, not both. For this reason, all information in the report's group header is included in the output. Only sums are output from a report's group footer. To include group information in the output, place the information in the group header instead of the group footer.

Sums

Sums are the only expressions included in the output. When you output sums, the Output To command uses the Sum formula of Microsoft Excel to represent the sums. Only sums located in the group footers and the report footers are included in the output.

In laying out the Microsoft Excel worksheet, the placement of the sum depends on the number of sums referencing a field. If there is only one sum referencing a field, the sum is placed in the same column as the field.

If there are multiple sums referencing the same field (for example, a sum in the group footer and in the report footer), all sums are placed in the column next to the field.

The Sum formula uses a range formula as part of the calculation. In order to keep all sums in the same column, the Sum needs to add each group's range rather than summing the entire range. For example, cells C1 through C5 are region Alpha and cells C7 through C10 are region Beta. The Sum formula for Alpha is:

   =Sum(C1:C5)

   =Sum(C7:C10)

   =Sum(C1:C5)+Sum(C7:C10)

   =Sum(C1:C10)

   =Sum(C1:C5)+Sum(C7:C10)

Labels

Because the Output To command cannot determine if a text box has a label associated with it, all text boxes have labels when output. The only exception are sums. Sums do not have labels unless there are multiple sums referring to the same field.

The label's caption is set to the name of the control. To control the caption of the label, set the text box's name to the caption's name.

REFERENCES