A client is trying to print a GL Detail Listing to Excel by printing to file, and the report cuts off because the Excel format is .XLS, not .XLSX, and there are tens of thousands of records.

Is there any other way to get the Detail Listing sent to .XLSX format? They need the opening balances, details, and ending balances for each account for the fiscal year (ie a standard detail listing).

Thanks

Steve

Hi Steve,

Why not do this as two financial statements? One with the opening and closing balances, the other exporting all the details.

What do they need this for?

For the auditors.

If necessary, I will suggest this. Although there is no control over which fields appear on the drilldown, for example Description 2 doesn't. Or ability to suppress fields or arrange the columns. And the drilldown sorts by acct/dept/period, but I have a feeling they want it by dept/acct/period.

But it really doesn't answer the question.

Hi Steve,

I wondered if this was "for the auditors". Could you check to see if the auditors use a product called "Caseware"? It's used in virtually 100% of Canadian accounting offices for year end work, and completely supports Adagio (File | Retrieve accounting). You would just have to send them a backup of the GL.

You do have control of the fields in the transaction drill down in FR. Both what is displayed and the order of the fields. Choose View | Customize | Settings | (Tx) Details and select the fields you want and their order.

Have you tried sending the report to the Application, rather than to a file?

The answer is no, Crystal Reports writes directly to an XLS file and does not go thru Excel to write the file, therefore there is no way to get the report in an XLSX format to remove the 32 or 64K row restriction.

GridView, the Financial Reporter and ExcelDirect all use the installed version of Excel to write the XLS or XLSX file.

Steve:

A comma separated value file has no limit. It can then be imported into XLSX.

Andrew: "Caseware" is used in 0% of the US accounting offices. Canada is not a foreign country to most Americans, it's a foreign planet that just happens to speak English (mostly). I am not including myself in that generalization.

Andrew: Thanks for teaching me about View | Customize | Settings | (Tx) Details. That is the missing piece of the puzzle, and I'm satisfied using the FR drilldown as an alternate solution. I'm wondering why the non-standard grid functionality though.

Douglas: That's generally a good idea, but not here, since the formatting is an essential part of the Detail Listing.

Steve

OK - so I'm curious...

Just exactly how do the accountants expect the Excel spreadsheet to be laid out when they are asking for items that have a one (Ledger opening and closing balances) to many (GL Posted transactions) relationship?

I'd REALLY like to know. Nothing that is readable can be manipulated, and nothing that can be manipulated (imported, sorted, filered) will be readable.

Steve,

I too get the request from clients or their auditors to provide the GL detailed listing at year end. One group acutally has a custom program written for their company to import this mess rather than using caseware.

My main problem has been limits on the number of rows allowed in the excel export. I get around this by providing an excel of Balance sheet accounts and then one for the income statment accounts. The auditors seem fine with this.


Printing to excel does not work, but what I do is preview the range then use the Export button to send the file to excel. The settings I use are:

- Format: "Excel 7.0 (xls)"
- Destiniation: "Application"

Then simply let the export do its thing and then go to the excel spreadsheet and save as an XLSX file and all is good.

Andrew: Auditors don't like change. They have a system that has worked in the past. Many don't care that we can give them a beautiful gridview table in excel. They just want their systems to work like they did "last year". I have made some auditors very happy by providing and excel spreadsheet from Gridview, others want what they always had.

Hi Ken,

I'm not asking auditors to change. I'm asking what they've always had! (They've certainly never "had it" from us!)

What would an export would look like that would include the opening and closing balances and all the transactions in between. Can someone send me a screen shot or an Excel file of what their auditors are expecting? Wouldn't it be easier if you could just push the ExcelDirect button and get that file?