Writing Excel Formula from UiPath to Excel not working

Help
, ,
1

Hello everyone,

I tried several ways to fill in excel formula “=VERWEIS(2;1/(A1:O1<>”“);A1:O1)” in excel cells via uipath but excel does not recognize it as formula showing #NAME? in the excel cell (tried building datatable and write range (error “range does not exist”), tried via excel application scope and write cell, tried write cell (workbook)…)

Did anyone had a similar problem getting working excel formel into excel via uipath?

Many Thanks!

2

Hi
Welcome to uipath community
use EXCEL APPLICATION SCOPE and use READ RANGE ACTIVITY and get the output with a variable of type datatable named dt
–now use FOR EACH ROW activity and pass the dt as input
–inside the loop use WRITE CELL activity and mention the range as
“C”+(dt.Rows.IndexOf(row)+2).ToString
it can C or any column where you want to use this formula
and mention the value like this
“=VERWEIS(2;1/(A1:O1<>“+chr(34)+”“+chr(34)+”);A1:O1)"

Cheers @bijo153

1 Like
3

Well,

any text that you use to write cell or write range does not write formulas into excel.

You might have to use a custom snippet.

See below a snippet which you can run in a loop to set cells with formulae

Try
app = New Microsoft.Office.Interop.Excel.ApplicationClass
workbook= app.Workbooks.Open(FilePath)
worksheet = CType(workbook.Worksheets(“Sheetname”), Microsoft.Office.Interop.Excel.Worksheet)
Dim rows_Count As Integer = worksheet.UsedRange.Rows.Count
For i As Integer = 0 To rows_count
worksheet.Range(“Column to be entered” + i.ToString).FormulaLocal =“Formula to be entered here with = sign”
Next
app.DisplayAlerts = False
workbook.Save
range = Nothing
worksheet = Nothing
workbook.Close
workbook = Nothing
app.Quit
app = Nothing
Catch ex As Exception
End Try

Hope this helps :slight_smile:

1 Like
4

Can you check the format once again @bijo153? I hope the formula itself is wrong. Make sure the formula which is working in excel is assigned to a string variable and then pass it to the activity write range

5

I am reading the formula from one cell and writing it though out the column however uipath is writing the formula as value, thus it is not generating the correct output.
designer view

image
image822×372 9.64 KB

output

image
image1020×486 11.8 KB

6

@Raghavendraprasad, did the VB code need office installed in the machine ?

how we use the code? using invoke code ?

7

You can use the activity invoke code for c# or vb.net, and use start process for VB Script or call the script file directly, or if it is VBA (Macro) you can use Invoke VBA or Execute macro (using Excel app scope)

Regards

8

@Raghavendraprasad , for the code you are typed above, and if i use invoke VBA code, did i need Office installed ??

9

Yes it uses the Excel App object.