I have an Excel file where column N (“Status”) contains the value “New”.
I want to iterate from row 2 onward and update the value in the Status column from “New” to “Under Review” for each row.
But when I am using write cell but I am getting type conversion error
“Argument ‘Cell’: BC30512: Option Strict On disallows implicit conversions from ‘IEnumerable(Of Object)’ to ‘IReadWriteCellRef’. The selected value is incompatible with the property type.”
I suggest you to use “Read Range Workbook” instead the column activity.
after that use a “for each row activity” using the table than you’ve created in the read range activity.
inside use an “Assign” activity and write “CurrentRow(0)” in the “To variable” property, and “Under Review” in “Set Value”. This will replace the values in the datatable than you keep saved.
finally outside of the for each row activity, add an “Write Range Workbook” activity, assign “N2” as your starting Cell and assign the datatable.
Be sure to uncheck the property Add headers in the “Read range Workbook” and “Write Range Workbook” activities
You can see an example in the image below:
easiest way would be to use find/replace activity which replaces all at once in excel..no loop is needed
also you are using worng combination of activities..excel process scope should not be used with read range workbook..you would see a read range as well..but with above method you need only one activity find/replace
Thanks @alejandro.tapia-hernandez
It worked.
I was able to still proceed and successfully achieve the expected with 'Write Cell" were the value “Under Review” was read from different sheet “Config” and replaced in all the cells of main sheet but once the execution was done , the excel was opened automatically due to which other activities was not able to access the file as the error message mentioned that the excel was in use.
@sobin_paul
It is because you are using “use excel file” activity and “Read Column” activity, they open Excel in two different ways.
I recommend you to better use “find/replace Value” activity, just as @Anil_G mentioned, is faster because the process doesn’t loop and it’s more simple:
I am using ‘Use Excel File’ activity without enabling check box “keep excel file open” but still when run the program the excel opens up automatically and the below activities cant use the excel
Ok, it’s because you can’t use a “Read Range Workbook” inside a “Use Excel File” activity, because they open excel in two diferent ways.
Here are two solutions than you can follow: Solution 1:
Open a “Excel process Scope” activity and add a “Use Excel File”, now add an “Find/Replace Value” and write the properties just as the image below:
Solution 2:
You can do it with the “Read Range” activity outside of the Excel Scope.
After that add and “For each Row” and inside add and “Assgin” activity and write “CurrentRow(0)” in the “To variable” property, and “Under Review” in “Set Value”. This will replace the values in the datatable than you keep saved.
Finally outside of the for each row activity, add an “Write Range Workbook” activity, assign “N2” as your starting Cell and assign the datatable.
Be sure to uncheck the property Add headers in the “Read range Workbook” and “Write Range Workbook” activities.
You can see an example in the image below:
Thanks @alejandro.tapia-hernandez once again for making the concepts clear to me .
I am new to Ui Path hence the knowledge gap .
I would go with solution 2 as I will be using workbook related activities for future processes
