Hi I am new, when I use read range to read a data table and write range to anther excel files, it changes the text format into general format.
For example, in the original one it is “0123321”, after I read and write to a new workbook, it becomes “123321”. Therefore I need to add a 0 before the number. Because I have another case like “0032121” and it will become “32121”. In this case I need to add two zero before the number.
Make sure the ‘PreserveFormat’ property is clicked in your read range activity. This will preserve it so the text value “0123321” will remain text, instead of converting to general, which is then converted to a number in excel (thus removing the leading 0’s)
One thing I’ve noticed is that Excel write range and workbook write range handle the use of Excel formats differently. One keeps it text form (I think workbook write range) while the other pastes it in the general format. Try using whichever one your worklow is not using to see if that fixes it
Hi it works after I add cchar in it.
However I would like to add 2 char because in the excel it won’t display 0 so I have to add ‘ before the integer to change it to text format. However I can’t add ‘0 for following
Cell = cell.tostring.padleft(7, Cchar(“‘0”))
The result becomes ‘’12345.
Also, my 2 cents on this… if you are dealing with CSV files, they are text-based, so they have all their values already in the zero-filled format that you are looking for. However, if you open the CSV file in Excel, Excel will by Default change the format to a number rather than text.
So, you can use Write CSV instead of Write Range, if that’s an approach you would like to take.
(open the CSV file in Notepad, and you will see how the format is correct)
Note: if you do Write Range to Excel and lose format, you can have UiPath format the columns, then use SaveAs to save to CSV and it will keep the zero-filling when it creates the CSV file.
I normally try to avoid using workarounds like adding the single quote, which also require you to change every value that you have in your table. The other option is to format the Excel file with 0000000 formatting for a visual but the value won’t have the zeros, so that option may not be what you want either.
So, I guess it depends on what the end goal really is, and using CSVs are usually better for keeping zero-filled numbers and date formats intact.
Thx for your advice and works finally it works fine!! Yes coz I have other works on the worksheet therefore I choose to use write range. Btw such a simple work but I miss the simple steps, thank you very much!