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

рдУрд╡рд░рд╡реНрдпреВ
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) тАФ setsmsg.fixtures_dirandmsg.sample_xlsx(тАж/fixtures/sample.xlsx). - Four Input Dialogs (
Core.Dialog.InputBox, all titledSearch and replace Excel values):Text to find:(defaultIstanbul) тЖТmsg.text_to_find.Text to replace with:(defaultIzmir) тЖТmsg.new_text.- File path (default
msg.sample_xlsx) тЖТmsg.selected_file. - Mode
all/first(defaultall) тЖТmsg.rename_function.
- Validate (
Core.Programming.Function,outputs: 2) тАФ requires a non-emptymsg.text_to_findand an.xls*/.csvfile; normalisesmsg.rename_functiontofirstorall; otherwiseCore.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_replaceis the full array or just the first match based onmsg.rename_function. - Loop via
Core.Flow.Label+Core.Programming.ForEachovermsg.cells_to_replaceтЖТmsg.current_cell:- Extract Cell Address (
Core.Programming.Function) normalises the item (string,.address,.Cell, or{column,row}) intomsg.cell_address. - Replace Value (
Robomotion.MicrosoftExcel.SetCellValue,mod1: string,continueOnError: true) writesmsg.new_textatmsg.cell_address. Core.Flow.GoToback to the loop label.
- Extract Cell Address (
- On loop exit: Save (
Robomotion.MicrosoftExcel.SaveExcel), Close (Robomotion.MicrosoftExcel.CloseExcel), then aDone!info dialog andCore.Flow.Stop.
Behind the scenes
SearchSheetruns withsheetSelection: Active-Sheet, so the search is limited to whichever sheet is active when Excel opens the file.Replace ValueusescontinueOnError: trueso a single protected/locked cell doesn't abort the whole loop.Extract Cell Addresshandles multiple shapes ofSearchSheetoutput because the package has returned different formats across versions; normalising to a plainA1-style string keepsSetCellValuehappy.- Replacement writes the whole
msg.new_textas a string (mod1: string) тАФ partial, case-sensitive matching is not performed; each hit cell is fully overwritten.
Excel Automation рдХреЗ рдФрд░ рдЯреЗрдореНрдкрд▓реЗрдЯ
рд╕рднреА 5 рджреЗрдЦреЗрдВ тЖТ- Consolidate Excel ReportsCombines rows from multiple Excel workbooks in a folder into one consolidated sheet. A reporting helper that removes repetitive copy-paste work.
- Launch ExcelOpens Microsoft Excel and creates a new empty workbook. Starter template for any Excel automation.
- Launch Excel and Extract TableOpens an Excel workbook and reads its first table into a structured message variable. Ideal starting point for data-driven flows.
- Manipulate Excel Data Using SQLLoads Excel data into an in-memory SQL engine and runs a SELECT / UPDATE statement against it. Gives you database-level expressiveness over spreadsheet data.