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. |
2. 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.
3. Click
the (Stop
Recording) icon to stop the recording. The Open
in Studio button appears.

4. 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. |
|