Hi All,
There is a search word,
so,i need to search that word in all sheets of excel.if word found then get the sheetname of that.
Kindly guide me any solution.
For each Excel Sheet is used to iterate all the Sheets Aviable in the Excel File
The Find or Replace activity is used to find the searched word what you want to find
Output is of String Var
If Condition is used to check whether the Output is of Empty or not
If It contains data
then We are getting that Particular Sheet Name By using currentsheet.Name
Excel Process Scope
Use Excel
For each Excel Sheet
Find or Replace activity to find the word
if
Not String.IsNullOrEmpty(FoundAt) And Not String.IsNullOrWhiteSpace(FoundAt)
Then
Message box or log Message
CurrentSheet.Name
Hope it helps!!
Excel Application Scope
Workbook Get Sheets
Output: allSheets
For Each sheet In allSheets
Excel Application Scope (same Excel file)
Read Range
SheetName: sheet
Output: dtCurrentSheet
For Each row In dtCurrentSheet
For Each cell In row.ItemArray
If cell.ToString.Contains(wordToSearch)
Add To Collection
Collection: sheetNames
Item: sheet
Break
→ Use the for each excel sheet activity to iterate the each sheet in the excel.
→ Inside for each insert the Find/Replace activity and select the find option and give the value you want to search for.
→ Create a variable in the Output of Find/Replace activity. let’s call the variable name as FindValue
→ after that insert an if condition then give the below condition,
- Condition -> Not(String.isNullorEmpty(FindValue.toString))
→ Inside then block insert the assign activity and create a variable to store the sheet name.
- Assign -> SheetName = CurrentSheet.Name.toString
→ After assign activity insert break activity to break the loop.
By following above steps it will check the word in every sheet if it found then it will save the sheet name in the SheetName variable.
Hope it helps!!
