ACC: How Reports Are Output to Microsoft Excel

ID: Q129467

The information in this article applies to:

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)

The Sum formula for Beta is:


   =Sum(C7:C10)

To get the total, the formula is:


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

With a large number of groups, this formula may be long. Because the Sum formula includes only values that are numeric, blank cells and cells containing text are ignored. The formula


   =Sum(C1:C10)

gives the same result as:


   =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

For more information about the Output to command in Microsoft Access 97, search the Help Index for "OutputTo Action," or ask the Microsoft Access 97 Office Assistant.

Keywords : OtpExl
Version : 2.0 7.0 97
Platform : WINDOWS
Issue type : kbinfo