ACC: How Reports Are Output to Microsoft Excel

Article translations Article translations
Article ID: 129467 - View products that this article applies to.
This article was previously published under Q129467
This article has been archived. It is offered "as is" and will no longer be updated.
Moderate: Requires basic macro, coding, and interoperability skills.
Expand all | Collapse all

On This Page

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.

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.

Properties

Article ID: 129467 - Last Review: October 26, 2013 - Revision: 4.0
Applies to
  • Microsoft Access 2.0 Standard Edition
  • Microsoft Access 97 Standard Edition
Keywords: 
kbnosurvey kbarchive kbinfo KB129467

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com