рд╕рднреА рдЯреЗрдореНрдкрд▓реЗрдЯ

Search and Replace Excel Values

RobomotionтАврдкрд┐рдЫрд▓рд╛ рд╡рд░реНрд╖ рдЕрдкрдбреЗрдЯ рдХрд┐рдпрд╛ рдЧрдпрд╛
Search and Replace Excel Values

рдУрд╡рд░рд╡реНрдпреВ

Performs a find-and-replace across an Excel worksheet and saves the result. Handy for bulk corrections or masking sensitive fields.

Search and Replace Excel Values

Finding and replacing values while working with big Excel spreadsheets can be a time-consuming and tiring task. Simplify Excel handling and decrease searching time using Robomotion's Excel nodes.

What Search and Replace Excel Values can do

  • Build Default Path (Core.Programming.Function) тАФ sets msg.fixtures_dir and msg.sample_xlsx (тАж/fixtures/sample.xlsx).
  • Four Input Dialogs (Core.Dialog.InputBox, all titled Search and replace Excel values):
    • Text to find: (default Istanbul) тЖТ msg.text_to_find.
    • Text to replace with: (default Izmir) тЖТ msg.new_text.
    • File path (default msg.sample_xlsx) тЖТ msg.selected_file.
    • Mode all / first (default all) тЖТ msg.rename_function.
  • Validate (Core.Programming.Function, outputs: 2) тАФ requires a non-empty msg.text_to_find and an .xls*/.csv file; normalises msg.rename_function to first or all; otherwise Core.Flow.Stop.
  • Open Excel (Robomotion.MicrosoftExcel.OpenExcel, optVisible: true) тЖТ msg.excel_app_id.
  • Search (Robomotion.MicrosoftExcel.SearchSheet, sheetSelection: Active-Sheet, searchTerm: msg.text_to_find) тЖТ msg.found_cells.
  • Pick Cells To Replace (Core.Programming.Function) тАФ msg.cells_to_replace is the full array or just the first match based on msg.rename_function.
  • Loop via Core.Flow.Label + Core.Programming.ForEach over msg.cells_to_replace тЖТ msg.current_cell:
    • Extract Cell Address (Core.Programming.Function) normalises the item (string, .address, .Cell, or {column,row}) into msg.cell_address.
    • Replace Value (Robomotion.MicrosoftExcel.SetCellValue, mod1: string, continueOnError: true) writes msg.new_text at msg.cell_address.
    • Core.Flow.GoTo back to the loop label.
  • On loop exit: Save (Robomotion.MicrosoftExcel.SaveExcel), Close (Robomotion.MicrosoftExcel.CloseExcel), then a Done! info dialog and Core.Flow.Stop.

Behind the scenes

  • SearchSheet runs with sheetSelection: Active-Sheet, so the search is limited to whichever sheet is active when Excel opens the file.
  • Replace Value uses continueOnError: true so a single protected/locked cell doesn't abort the whole loop.
  • Extract Cell Address handles multiple shapes of SearchSheet output because the package has returned different formats across versions; normalising to a plain A1-style string keeps SetCellValue happy.
  • Replacement writes the whole msg.new_text as a string (mod1: string) тАФ partial, case-sensitive matching is not performed; each hit cell is fully overwritten.

Excel Automation рдХреЗ рдФрд░ рдЯреЗрдореНрдкрд▓реЗрдЯ

рд╕рднреА 5 рджреЗрдЦреЗрдВ тЖТ