ACC2000: How Reports Are Output to Microsoft Excel (208838)


The information in this article applies to:

  • Microsoft Access 2000
This article was previously published under Q208838
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).

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, only information in the report's group header is included in the output. To include group information in the output, place the information in the group header instead of the group footer.

NOTE: In earlier versions of Microsoft Access and in Microsoft Access 2002, sums are able to be exported from a group footer. This is not the case for Access 2000. For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

253639 ACC2000: Microsoft Access Report Totals Are Missing When You Export Report to Microsoft Excel

Calculations

All calculations in the detail or group header sections are included in the output. Excel functions are not used to calculate expressions in the Access Report. Instead, the results of an expression calculated in Access, are stored in the appropriate cell according to the location of the text box.

Labels

Columns are labeled according the Name property of the text boxes they represent.

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.

REFERENCES

For more information about the OutputTo command, click Microsoft Access Help on the Help menu, type OutputTo Action in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
Modification Type:MajorLast Reviewed:4/25/2003
Keywords:kbdta kbinfo KB208838
©2002 Microsoft Corporation. All rights reserved.