Excel Buffer field formatting

Business Central/NAV Developers Forum
1

Hi all, I’m using table 370 Excel Buffer for exporting and importing different kinds of fields, which works quite fine. But if I’m importing a field of type code or text which contains a number it will be formatted with dots, like a number. For example the item-code: Navision → Excel → Navision 1000 ----> 1000 → 1.000 How can this be avoided?

2

Try to delete . from that string and then convert to Integer

3

This is not an option, because the text/code field could contain a legal dot, which should not dissappear.

4

Hi Alex Try to modify function ReadSheet with these lines. You can receive the Numberformat of the cell and the original formula. In the field formula an integer cell with value 1 000 will be displayed as 1000. I’m sure you can make something with this. **ReadSheet()** Window.OPEN( Text007 + '@1@@@@@@@@@@@@@@@@@@@@@@@@\'); Window.UPDATE(1,0); DELETEALL; XlRange := XlWorkSheet.Range(GetExcelReference(5)).SpecialCells(11); Maxi := XlRange.Row; Maxj := XlRange.Column; i := 1; REPEAT j := 1; VALIDATE("Row No.",i); REPEAT VALIDATE("Column No.",j); // NEW CODE BEGIN Formula := FORMAT(XlWorkSheet.Range(xlColID + xlRowID).Formula); Bold := XlWorkSheet.Range(xlColID + xlRowID).Font.Bold; Italic := XlWorkSheet.Range(xlColID + xlRowID).Font.Italic; Underline := XlWorkSheet.Range(xlColID + xlRowID).Font.Underline; NumberFormat := FORMAT(XlWorkSheet.Range(xlColID + xlRowID).NumberFormat); // NEW CODE END "Cell Value as Text" := DELCHR(FORMAT(XlWorkSheet.Range(xlColID + xlRowID).Value),'<',' '); IF "Cell Value as Text" <> '' THEN BEGIN INSERT; END; j := j + 1; UNTIL j > Maxj; i := i + 1; Window.UPDATE(1,ROUND(i / Maxi * 10000,1)); UNTIL i > Maxi; XlWorkBook.Close(FALSE); XlApplication.Quit; CLEAR(XlApplication); Window.CLOSE; /Magnus

5

quote:

Originally posted by AK
Hi all, For example the item-code: Navision → Excel → Navision 1000 ----> 1000 → 1.000 How can this be avoided?

I’ve got similar problem but with space not dot. I tried to run my code on another PC and it worked fine. But also I found a solution that worked on all PCs. In Regional Setting I changed Digit grouping symbol for Numbers and Currencies to letter “a”. In code I searched imported text for this letter and deleted it. Worked fine [:)]

6

Hi all, sorry for the late answer, this topic slipped my mind while working on another project. Magnus, your mod looks interesting, I think this will be the key to a solution! Thanks, Alex