0 truncated in excel

Help Studio
, ,
1

Hi Community!

I capture a number from a website and then write it to Excel. The number is captured perfectly in Studio. The problem is when its written to Excel. If the number starts with 0 then the 0 is removed when its written to Excel.

I saw a post saying to use system >> file >> workbook activity but then I receive an error the file is being used by another process.

Please help. Thank you!

2

Hi Before entering the value in excel convert the column type to text so that it will keep the leading zeros in the excel. we can use vb code to format the entire column to text format.

Range(“A:A”).NumberFormat = “@”

use invoke code activity to format the column with VB code. please refer below.

Dim excel As Microsoft.Office.Interop.Excel.Application
Dim wb1 As Microsoft.Office.Interop.Excel.Workbook
Dim ws1 As Microsoft.Office.Interop.Excel.Worksheet
excel = New Microsoft.Office.Interop.Excel.ApplicationClass
wb1 = excel.Workbooks.Open(“your excel file path”)
ws1 = CType(wb1.Sheets(“Your Sheet Name”), Microsoft.Office.Interop.Excel.Worksheet)
'given sample column you can change accordingly.
ws1.Range(“A:A”).NumberFormat = “@”
wb1.Save
wb1.Close
excel.Quit
ws1 = Nothing
wb1 = Nothing
excel = Nothing
GC.Collect

Regards,
Kirankumar.

3

In my workspace we are very restricted in executing code, such as the VB solution in previous post. (Our IT guys are quite protective. A good thing of course, but sometimes a bit restrictive.)

An alternative solution is to set up your exceldocument manually in the correct format in advance.

The usual shortcut using UIPath is to export your data in excel scopes, which nicely creates the blank document for you. Cool feature, but unfortunaltely our friends at Microsoft decided that Excel should be in the drivers seat, and not you, and it starts applying its own interpretation of the data format.

By having a preformatted excel template stored somewhere accessible, writing your data is a small sequence of:

  • copy your pre formatted excel to your document destination
  • use the copied document in your excel scope
  • write and save the data

If this also isn’t an option, because you need to append in other peoples documents, you can force excel to write any data as text by appending a ' to it (single quote).
So write cell: 001 would become write cell '001 instead. This does of course require extra some data manipulation on your side.

Hope this helps.

1 Like
4

Hi @Jessica_Moseley

if you want to remove a zero when it was start as zero

use if activity use this syntax Extracted_var.Startswith(“0”)
inside the if activity
Extracted_var.Replace(“0”,“”).ToString.Substring("1,Extarcted_var.length-1)

if the error is showed like file is being used by another process i think you opened the excel & close the excel run the process again

Thanks,

5

I want to keep the 0 when its written to excel, not remove the 0.

6

Brilliant, thank you!

Putting the single quote before writing the value worked perfectly.

7

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.