Writing Excel Formula from UiPath to Excel not working

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!

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

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:

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