Find and Replace in Excel

@priyanka1991

use Build Datatable to create a DT where declare the columns you want to create
In Add Data Row just in Array row pass values as below
{row(“colname”),…,strNo…}

Regards,
A Manohar

do i need to create all coulmns in build data table which are present in excel sheet??

If you want to print only a specific column then you can build table with that specific column as per your requirement.

yes ,just want to modify that particular column with 0 to no and 1 to yes
want to keep all columns and data as it is.
that’s it.

@priyanka1991

use append range activity and maintain the specific range of column you want to change. and declare data table in it
it will change automatically with the new values

many thanks @Manohar1
it worked as u suggested…
but couldn’t change the value 0 to no and 1 to yes
as i attached the screenshot above for replacing the value…
i m doing wrong somewhere.
pls guide…

@priyanka1991

unable to find screenshot

for your reference i will do an example and will share xaml file

sure manohar

Hi,
First read your excel sheet using the read range activity and then assign a datatable variable for it.Then create a for each row activity for that data table and inside that for each activity put an assign statement as. row(“column name”)=row(“column name”).toString.replace(“0”,“no”).trim
and another assign activity as row(“column name”)=row(“column name”).toString.replace(“1”,“yes”).trim
Then you can write that data table into an excel using write range activity.:slight_smile:

hi vishal

after putting assign activity in for each row.
what data table u r talking about??
sud i create build data table and then add data row…
bcz as manohar suggested that procedure worked except yes /no conversion…
m attaching xaml file…
pls have a look…
demoProcesses.xaml (19.4 KB)
thanks for help…

@priyanka1991

ref.xls (17 KB)

Main.xaml (10.7 KB)

1 Like

The data table which you performed for each activity.
use the same data table in the write range.

1 Like

cool
got it

Thank you so much!

Hi @aspin13,

Here is an activity called “Find Replace” in the below package. It may be useful solve the issue.

Regards
Balamurugan.S

Hii Priyanka,

If your problem resolved, can you share your xaml file.

1 Like

@nimin

Hi Nimin,
Thank you for sharing this VBA file. I wanted to know is it possible to then count and display how many times the VBA replaced the value?

Hie Sahil
i have a data in excel in which particular values have value x i want to replace all those with 1 and null or blank value with 0 using uipath can you please help

1 Like

Hie Thasnia_Rob
i have a data in excel in which particular values have value x i want to replace all those with 1 and null or blank value with 0 using uipath can you please help

1 Like

Hello , I really hope I get your reply on this. I just used your find and replace method using VB code and it worked . but then I am only trying to do this find and replace in a particular column in one sheet. I.e in column J find ''yes ‘’ and replace with ‘‘No’’ of sheet1. Please help .thanks a lot

Hi Use the following vb.Net code in an invoke code activity

Dim xlApp As Microsoft.Office.Interop.Excel.Application
Dim wb As Microsoft.Office.Interop.Excel.Workbook
xlApp = New Microsoft.Office.Interop.Excel.Application
wb = xlApp.Workbooks.Open(Path)
TryCast(wb.sheets(SheetName),Microsoft.Office.Interop.Excel.Worksheet).Range(“A1:X3000”).Replace(Value1,Value2)
xlApp.DisplayAlerts = True
wb.Save()
wb.Close()

Arguments
Path = is the file path to the excel sheet
SheetName = The sheet name
Value 1 = lookup value
Value2 = replace value1 with this value