ACC: How Reports Are Output to Microsoft Excel (129467)
The information in this article applies to:
- Microsoft Access 2.0
- Microsoft Access for Windows 95 7.0
- Microsoft Access 97
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. ColumnsTo 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.
Modification Type: | Major | Last Reviewed: | 5/6/2003 |
---|
Keywords: | kbinfo KB129467 |
---|
|