Microsoft KB Archive/129467

= ACC: How Reports Are Output to Microsoft Excel =

Article ID: 129467

Article Last Modified on 1/19/2007

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q129467



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



SUMMARY
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.

Columns
To avoid differences in column order between the report and the exported spreadsheet, Top align the controls in the Detail section of the Access report. The columns in the exported Excel spreadsheet appear in order of the highest text box control in the Detail section of the report. For example, if the Top property of the first text box on the left is .001 and the Top property of the second text box is 0, the values for the second text box on the Access report appear in the first column of the exported Excel spreadsheet.

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.

