CAPITAL Office Business Software

After upgrading to CAPITAL 7.4 FINANCIAL STATEMENTS are skewed when using Office Links. It is OK under preview and from direct printout.

Unfortunately you will be unable to automatically export existing reports using OfficeLink to Excel and have them come out neatly formatted.

You have two options here:

1. If your financial statements are unchanged from the originals that come with CAPITAL Office, you can take the newer versions of these reports which have been set-up to work with OfficeLink and copy them over your existing reports. To do this you must copy the reports in \CAPITAL\GLREPORTS to \CAPITAL. Make sure you keep backups of any reports you are going to copy over, as a precaution.

2. Your second option, if you have customised your financial statements, is to tell the system how OfficeLink is to export the report into Excel. You do this by adding the function @OfficeLink() to your existing reports. Please read the CAPITAL GL Controller help topic: "Report Functions" for information on what is required to do this.

I have just tried to use OfficeLink on a customised Financial Report. I have specified 14 columns but only the first 2 appear in Excel. I have used the following as the first line in the Body

@OfficeLink(L35, R11, R11, R11 ...) etc for the 13 numeric columns which are the months and a year total at the end.

Any suggestions?

Please take a look at the General Ledger topic: Reporting Functions which reads:

@OfficeLink()

The @OfficeLink() function is used to assist in formatting financial statements when exported to Excel. Without using the @OfficeLink() function the contents of financial statements are exported to Excel in a single column. This makes financial statements exported to Excel difficult to work with.

The syntax of the @OfficeLink() function is:

@OfficeLink(L|Rn, ...)

The first parameter or value of the function should begin with either an 'L' or 'R' code followed by the width of the Excel column, to designate the justification. If not specified, 'L' (left-justification) is assumed. Up to an additional 24 columns may be specified, by separating additional parameters with a comma. The following instruction formats the output of the financial statement into two Excel columns. The first is 40 characters wide and left justified. The second is 30 characters wide and right justified:

@OfficeLink(L40, R30)

A financial statement exported to Excel may have a maximum of 25 columns. The @OfficeLink() function should be placed in the body section of the report and may appear in more than once place within the report, if you need to vary the formatting of the data within the financial statement, in order to produce a consistent result for Excel.

If no parameters are specified, all data is placed in the first column of the spreadsheet.

While the @OfficeLink() function can be included in a report multiple times, you may only define the left or right justification of a column once. Once a column has been defined as left justified, for example, it will always be formatted as left justified, even if subsequent @OfficeLink() commands specify right justification.

You cannot specify a column width wider than 999 characters and less than 6 characters in width. Regardless of the width of the column you specify, a spreadsheet column will be no larger than the largest data element found in any column of the spreadsheet.

***

If you have upgraded from an older version of CAPITAL and have also made modifications to your standard financial reports, you will need to add the @OfficeLink() function to them. Output formatting in Excel will not automatically be correct.

If you have not modified your standard financial statements it may be quicker for you to replace your old reports with the current standard financial reports, found in your \CAPITAL\MFC folder. Financial statements have the file extension *.GPT. Please sure to back-up your old reports in case you intend to replace them with the newer version as a precaution.

Thanks for the reply but I have already done everything suggested in your reply and it still does not work.

If you're not able to do this yourself you're welcome to email your report to our helpdesk and we will make the changes for you, however a charge will apply for us to do that.

Thanks for the offer. I believe I have used the function correctly but clearly something is wrong. So, I am happy for you guys to take a look and advise me on what the problem is.

What is your email address and how much do you charge?

Please email your details to sales@capitaloffice.com.au and we'll forward this information to you. Attach a report that is giving you problems in case it is something very simple we can sort out in a few minutes at no charge.