| Columns are not aligning when I export my report to Excel. |
Description | If you have problems with blank lines in the text file where there are page breaks, try the following options: - Change the unprintable area setting on your printer to zero
- Change the margins in your positive pay report to zero (File, Page Setup, Margins)
- Verify that the Use form feeds check box is cleared (File, Page Setup, Print Options)
- Set up a printer object that uses the Generic/Text Only Windows printer driver. Print to file with that printer object
- Import the text file into Excel, sort by date, and then save it. This moves blank lines to the bottom of the spreadsheet
|
Cause | Reports may not align properly when you export them to Excel due to their formatting. |
Resolution | [BCB:5:Third-party support:ECB]
When designing reports and financial statements for export to Excel, follow these guidelines: - Reports and financial statements must be in a columnar format with adequate space for each column
- Reports and financial statements should not contain headers and footers
- If you want Excel to recognize numbers, include decimals and represent negative numbers with a negative sign to the left of the number
- Reports and financial statements cannot print dollar signs and asterisks next to numbers
- Key changes: remove headers, define print options and set single line spacing
Formatting reports for export to Excel: - Open the report design in Report Designer by going to File, Open Design.
- From the File menu, select Save Design As and in the pop up window under File Name - add "to xls" at the end before the ".rpt". For Example "Job List to xls.rpt".
- Click Save.
- Remove Margins:
- From the File menu, select Page Setup, Margins.
- Clear the Use system default check box, and then click OK.
- From the File menu select Page Setup, Page Headings.
- Clear the Use page heading check box, and then click OK.
- Using the mouse, point to in the page header.
- Locate the dashed line across the header with two black triangular handles on either side of it.
- Click on one of the black triangular handles and drag the line to the top of the page.
- From the View menu, click Header to clear the check mark and remove the Header from the report design.
- Remove Column Headers:
- From the Format menu, confirm that Use Column Headings is not selected. Optional: If any column headers remain, highlight and click delete. Column headers can cause columns to merge or add additional columns.
Note: If setting up design to print labels, select the fields you want to appear on the labels, each separated by a semicolon (;). All the fields should appear on one line and there should be no column headings. - Set number format:
- From the File menu, select Page Setup, Print Options.
- In the Print Negative Numbers As area, select -999, and then click OK.
- Set line spacing:
- From the Design menu, select Line Spacing, Single, and then click OK.
- Remove Totals:
- From the Design menu, select Totals and clear any total check boxes, and then click OK.
- Save the modified design.
- Add the new report to the application report menu. See How do I save, add, and print a report design?
Notes: - If columns merge, you may need to add space between those two fields in the report design
- In some cases, the report will need to be modified so all fields are on one line on the design
- Print Controls may also need to be modified
- Column headers can cause columns to merge or add additional columns. Removing the column headers from the report removes this issue
- Total lines will not recalculate but are stagnant numbers. Removing the total lines will then allow for easy addition of total lines in Excel
|
|