Please go through the link below -
and let me know if any issues.
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
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.
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?
* Daniele Rebussi * | * Rebu NAV diary *
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.
ps. Still not a developer!
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 !
I am getting exactly the last error you reported in the blog: " 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.
Could you explain or give me a more detailed descriptions of the changes you made when you refered to: "I have added the format to the entercell function, and added the number format, and every thing work well". What change did you do to the function?
Thanks in advance. Neither I am a developer ;)
I faced this problem when I developed Query and Excel Report tool.
It is due to bug, Platform Hotfix solved this issue:
Ok, thanks for such a fast answer. I will try to update the plattform and check it is solved.