Error when running Excel Buffer table UpdateBook Function

matthewtaing

Hi,

I'm encountering this error below when I tried to replicate the code from Report 29 to Update an excel worksheet. I've tried to debug it and it stops at the code

XlWrkBkWriter := XlWrkBkWriter.Open(FileNameServer);

The filename that was passed in to the function is using the same concept as Report 29 where I use the file management codeunit to do a silent upload and get the filenameserver back from the function and then pass it into the updatebook function. The following is some snippets of code :-

1. Getting filenameserver

IF UploadedFileName = '' THEN
  ClientFileName := FileMgt.OpenFileDialog(Text006,UploadedFileName,'')
ELSE
  ClientFileName := FileMgt.OpenFileDialog(Text006,'.xlsx','');

UploadedFileName := FileMgt.GetFileName(ClientFileName);
ServerFileName := FileMgt.UploadFileSilent(ClientFileName);
FileName := UploadedFileName;

ValidateServerFileName;

2. Code that runs the excel buffer updatebook function. The error prompts on the first line below

ExcelBufTmp.UpdateBook(ServerFileName,SheetName);
ExcelBufTmp.WriteSheet('',COMPANYNAME,USERID);
ExcelBufTmp.CloseBook;
ExcelBufTmp.OverwriteAndOpenExistingExcel(ClientFileName);
ExcelBufTmp.GiveUserControl;

Will post up the object file if required. Hope it can be resolved before that.

Thanks

David_Singleton

A ton of things can cause errors like this. Often uninstalling and reinstalling either NAV or Excel will fix it. DO you get the same error on a different computer.

matthewtaing

Yes, I did try to run this code in another computer and the same error happened.

ara3n

Create a new excel file that has .xlsx file ending and try that file.

matthewtaing

wow...it works after creating a new excel file and copy and pasting the content to the excel file. Thank you very much!. Never thought it is the file that's having problem.
#-o #-o

ara3n

you are welcome.

Andreas_Wenzel

I encounter the same error message and in the meantime I came to the conclusion that this is an error in the openXML implementation.

The error occours whenever you use user defined cell formats. There are several predefined formats in the OpenXML standard. User defined formats have to start with an internal number of 164 (magic number). You can review the list of predefined formats in Excel when you right click on the cell and select format.

One of the predefined currency formats (at the end of this list) is: _-* #.##0 €_-;-* #.##0 €_-;_-* "-" €_-;_-@_-

The internal ID is 42 so it's a 'standard' format. Nevertheless, whenever I try to use the UpdateBook function of T370 I get the same error, since OpenXML interprets the format as user defined and therefore expects its number to start with 164. Unbelievable!

I haven't found a solution for, except avoiding those formats, but this isn't always easy since the business requires such formatting.

rocatis

Andreas Wenzel wrote:

I haven't found a solution for, except avoiding those formats

Just a heads up: there now is a fix for this error: https://mbs2.microsoft.com/Knowledgebase/KBDisplay.aspx?scid=kb;EN-US;2934564