Question Status

Verified
Michael Mo asked a question on 26 Mar 2014 3:50 AM
Firstly I am not a developer or programmer... We are currently upgrading to Nav 2013, and I want to update my bespoke report to Nav 2013 r2. The report opens an excel template which used to be stored on the users pc (good for security limiting users that can run this). Now it appears as though I have to let the server to run the report. My question is... if the code was ... ExcelBuf.OpenBook(ExcelFileName, 'Data'); ExcelBuf.CreateSheet('Data','xxx',COMPANYNAME,USERID); ExcelBuf.GiveUserControl; And the CreateSheet changes to WriteSheet ExcelBuf.OpenBook(ExcelFileName, 'Data'); ExcelBuf.WriteSheet (PADSTR(STRSUBSTNO('%1 %2','Data','xxx'),30), COMPANYNAME, USERID); ExcelBuf.GiveUserControl; I then find that on the nav server that the file format is not allowed to be xlt, so changed to xlxs. Final straw is "A call to Microsoft.Dynamics.Nav.OpenXml.Spreadsheet.WorkbookReader.Open failed with the message: \\servername\folder\filename.xlxs" As I say I am no developer, but having made a perfectly good report I just want to upgrade it. Any help appreciated.
Reply
Saurav Dhyani responded on 26 Mar 2014 4:35 AM

Dear Michael.

Please go through the link below -

saurav-nav.blogspot.in/.../nav-2013-nav-2013-r2-save-export-to.html

and let me know if any issues.

Regards,

Saurav Dhyani

saurav-nav.blogspot.com

 

Reply
Michael Mo responded on 31 Mar 2014 4:51 AM

Hi Suarav

firstly thank you for the reply. I have now got the excel file producing, but....  we had a template file we opened, and we filled it with equations such as

=IF(D19="CHF",sum(11671*$J$5),IF(D19="USD",sum(11671*$J$6),IF(D19="EUR",sum(11671*$J$7),IF(D19="LCY",11671))))

The excel buffer doesn't seem to like this and puts it in as text. I looked at the code of table 370 (Excel Buffer) and the options don't seem to allow for this.

So in summary there are two issues:

1. I can't find a function to open an existing template.

2. Our complex formulas do not convert when opening the excel file.

Any comments appreciated.

Reply
Suggested Answer
Daniele Rebussi responded on 31 Mar 2014 6:38 AM

Hi, for update an existing file you can refer to report 29 "Export Acc. Sched. to Excel", when running "Update Workbook" option.

Regarding the equation, you need to enter it in Formula field of Excel Buffer table, using a numeric number format (r.g. "'#,##0.00"): since it contains some SUM and IF statements, are you using Excel in English to make it properly been parsed?

Reply
Michael Mo responded on 1 Apr 2014 3:50 AM

Hi Daniele, firstly many thanks, after some "trial and error" testing, I can now update workbooks, the code is working. So many thanks.

One last design issue compared to 4.0 is.....the report does not seems to like the fact that my existing file has text and cell formatting. If I run the report into a blank saved file, the report work fine, otherwise it throws an error " A call to Microsoft.Dynamics.Nav.OpenXml.Spreadsheet.WorkbookWriter.Open failed with this message: The custom numbering ID format should start at 164 and should be listed in consecutive order.

Any ideas on this would be helpful.

Thanks

Michael

ps. Still not a developer!

Reply
Verified Answer
Michael Mo responded on 1 Apr 2014 4:57 AM

Ok now I get it!  I have added the format to the entercell function, and added the number format, and every thing work well.

The issue was in the original excel template some of the cells were formatted to show "as thousands" ie 1,000, now the entercell function takes care of this.

Once again many thanks. I now have an Nav 2013 working excel cost sheet to keep my users happy.

Let's hope the actual system works as well as the report when we go live !

Reply
Verified Answer
Michael Mo responded on 1 Apr 2014 4:57 AM

Ok now I get it!  I have added the format to the entercell function, and added the number format, and every thing work well.

The issue was in the original excel template some of the cells were formatted to show "as thousands" ie 1,000, now the entercell function takes care of this.

Once again many thanks. I now have an Nav 2013 working excel cost sheet to keep my users happy.

Let's hope the actual system works as well as the report when we go live !

Reply
Suggested Answer
Daniele Rebussi responded on 31 Mar 2014 6:38 AM

Hi, for update an existing file you can refer to report 29 "Export Acc. Sched. to Excel", when running "Update Workbook" option.

Regarding the equation, you need to enter it in Formula field of Excel Buffer table, using a numeric number format (r.g. "'#,##0.00"): since it contains some SUM and IF statements, are you using Excel in English to make it properly been parsed?

Reply