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?
Try to delete . from that string and then convert to Integer
This is not an option, because the text/code field could contain a legal dot, which should not dissappear.
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
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 [:)]
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