Excel Recorder

Excel recorder is a powerful excel automation tool that captures everything that the Excel itself can offer through its own Marco recorder. It records the steps and operations performed in Microsoft Excel application such as working across different excel files or worksheets and performing a repetitive task over the excel file.

Prerequisite

Ensure in the Trust Centre settings options of the Excel application, the Enable all macros and Trust access to the VBA project object model check boxes are selected.

Using Excel Recorder

  1. In the Canvas Tools pane, click Excel Recorder to launch the tool. The ScriptStore.xlsm excel file along with the Excel Recorder interface appears.

 

 

NOTE:  
  • You can save the opened .xlsm file to perform the required operation or open an existing excel file by navigating through File option of the opened excel file.
  • You can open an existing file once the excel file and the Excel Recorder interface appears.
  • Before you open a different file, you must start the recording.
  • When you select an entire Excel sheet by using Ctrl+A+A and copy paste the selection in another sheet, workbook, or application, you may observe the System.Exception:ScriptStoreException: Out of memory error in the Automation Studio log file. To fix this error, see RPA Troubleshooting Guide.

 

  1. Click the (Start Recording) icon to start the recording and perform the steps to record for automation. For example, you can input the formula in a cell or access the formula from the Formulas section. You can use the formatting options in the similar way. The recording icon turns grey.
  2. Click the   (Stop Recording) icon to stop the recording. The Open in Studio button appears.

 

 

  1. Click Open in Studio button to return to the Canvas. An Excel Script activity is created with a link to the script of the steps recorded. It automatically creates arguments related to the operations performed in the excel file with default values. You can edit the name of the created argument. Below is sample activity and the related arguments that got created automatically:

 

 

The Excel Recorder activity is created with a default name.

Editing Script

Click the Edit Script link to edit the scripts of the steps recorded. The scripts captured opens in the Notepad application. Below is a sample script:

 

 

You can configure the dynamic part of a static recording using the Edit Script option.

 

While recording the excel operations, if the static fields are captured by the cell value and not by action, you can change those steps into generic dynamic functions by editing the scripts. Alternatively, capture the static fields using activate cells, rather than selecting them to make it dynamic during the recording itself. For more information, search about Excel VBA on the web.

Usage Tips

  • Find last row of the column:
    Offset the cell value and make the selection of the call as comment using the Edit Script options. Below is an example of finding the last row:

 

 

  • Input large script formulas:
    If large text-based formulas like VLOOKUP are not captured during recording, then input them in the edit script area.
  • Build a complete use case
    Record small steps using the Excel Recorder activity, and then combine them using the Reuse Process activity to form an automation process workflow.

Automation using Excel Recorder

The following list of operations have been tested while the list is not restricted to the same:

 

Excel 2010

Test for Excel 2010

Extract
  • Extract a cell value and provide as input to other activity or automation process workflow
  • One-time extraction of the entire column, deletion of columns and insertion of columns
  • Extract cell value for a column till blank cell

Copy
  • Copy a cell value from one sheet to another sheet
  • Copy a column from one sheet to another sheet
  • Copy entire sheet and paste it in a new sheet or new excel file
  • Copy a selected range of cells (column  and rows)

Formulas
  • VLOOKUP: Paste the VLOOKUP formula  in a cell
  • Concatenate: Two columns can be concatenated, and the value can be pasted in a new column
  • Sum: For entire column or selected range of cells inside the column
  • Count: For entire column or selected range of cells inside the column
  • Paste formula in a blank cell or selected range of cells

Macro

Invoke an existing Macro

Sort

Sort the entire column date wise

New Sheet

Create of a new sheet (tab)

Filter
  • Enable filter for all columns
  • Filter, select labels (one or multiple) and then extract the entire range
  • Clear all filters

Create New Excel
  • Create a new excel file and name it
  • Naming convention where the name is appended with the date

Save
  • Save option of the excel file
  • Save As option after downloading the excel file from the SAP application

Paste
  • Paste the text
  • Paste Special : Right click Paste Special and paste Values (V)
  • Paste in the selected range

 

Excel Scripts Properties

The properties of Excel Scriptsactivity are listed in the following table and can be edited in the Property grid on the right pane.

 

Property Name

Usage

Control Execution

Ignore Error

When this option is set to Yes, the application ignores any error while executing the activity.

If set to NA, it bypasses the exception (if any) to let the automation flow continue; however, it marks the automation status as failure, in case of an exception.

By default, this option is set to No.

Delay

Wait After

Specify the time delay that must occur after the activity is executed. The value must be in milliseconds.

Wait Before

Specify the time delay that must occur before the activity is executed. The value must be in milliseconds.

Misc

Breakpoint

Select this option to mark this activity as the pause point while debugging the process. At this point, the process freezes during execution allowing you to examine if the process is functioning as expected.

In large or complex processes, breakpoints help in identifying the error, if any.

Commented

Select this option to mark this activity as inactive in the entire process. When an activity is commented, it is ignored during the process execution.

DisplayName

The display name of the activity in the flowchart designer. By default, the name is set as Excel Script. You can change the name as required.

Name

The default name with which the script gets saved. You can change the name as required.

Path

The default file path where the script file gets saved. You can change the location as required.