Automation Toolkit Overview - Excel Off The Grid

The Automation Toolkit is a Low Code / No Code tool to help you automate your tasks you don’t want to do.

This article provides details of the main features and techniques to operate the tool.

What is the Automation Toolkit for?
How to use the Automation Toolkit
Working with objects
Using dynamic values
Logic and looping
Other features
General error messages
Known limitations
Tips and tricks

What is the Automation Toolkit for?

Excel has three main automation features.

Formulas: At a basic level, formulas are an automation feature; they recalculate automatically whenever values change.
PivotTables: PivotTables recalculate and resize outputs whenever a user clicks refresh. Often PivotTables are used instead of formulas.
Power Query: Extracts data from a source and transforms it into the optimal shape for use with formulas or PivotTables

These three features, when used together, give us huge power to connect to a source and prepare reports. However, in the reporting and analysis process, there are many more needs that Excel does not natively automate. For example:

Rolling forward reports for a new month / week – this involves manually creating folders, copying files, and changing cell values.
Creating or updating PowerPoint presentations – this involves either (a) manually pasting images into slides, (b) or managing the complexity of linked objects.
Saving and sending different views of reports to many people – this involves manually saving multiple versions of a report as either hardcoded values or as PDFs, then manually emailing those reports.

These are all massively time-consuming activities. This is where the Automation Toolkit comes in. The Automation Toolkit automates the start and end of the reporting and analysis process, which Excel cannot handle.

Traditionally, Excel experts would write complex macros to automate the reporting and analysis process. That is no longer required. The Automation Toolkit gives all the automation power which macros provide but without writing any code.

How to use the Automation Toolkit

The Automation Toolkit is filled with pre-set actions that a user can choose from and customize to their needs. Actions are placed one after another to build entire processes, known as an automation recipe.

Finding and adding actions

Automation recipes contain all the individual actions necessary for automating a process.

To add an action to an automation recipe

Select an action from the list
Click Add Action to add the action to the bottom of the recipe, or Insert Action to insert the action into the selected cell of an existing recipe.

Understanding action parameters

Users customize actions by changing the action parameters.

Parameters are pre-populated with example values that are relevant for the action. For example, as shown in the screenshot below, the Excel Workbook Close action has a single parameter called File Name, the example value is File Name.xlsx

The example values need only be replaced with the user’s values. In the screenshot below File Name.xlsx has been replaced with Reporting.xlsx

Parameters with limited values

Some parameters have an infinite number values. For example, there are an infinite number of possible file names.

However, other parameters have a restricted list of items. For these, the list of acceptable values is displayed, with the | symbol separating each value. For example, in the Excel Workbook Open action, the Read Only and Update Link parameters can only be True or False. Providing any other values results in an error.

Where the list of acceptable parameters is long, three dots (…) are displayed in the example text. This indicates that a full list of acceptable values is available on the Actions Reference Guide.

For example, the PowerPoint Slide From Excel Paste action displays 4 acceptable values, followed by 3 dots (…). There are 10 values possible for this parameter; looking at the Action Reference Guide provides full details of all 10.

Optional parameters

Some actions do not require values for every parameter. While the parameter name is always required, the value after the colon can be blank.

For example, the Excel Workbook Save action only requires the Save As Path parameter when the Save Type is set as Save As or Save Copy. Save by default retains the existing file path, therefore the Save As Path can be blank.

If users are unsure if a parameter value is required, full details are available in the Action Reference Guide.

Building automation recipes

By adding multiple actions, users create automation recipes.

The following is a simple Automation Recipe.

The workbook in the C:\Examples\Reporting.xlsx file path opens. It is not set as Read Only, and any links are not updated. There is no password to open or modify the workbook.
The value in cell D5 in the Settings tab of the Reporting.xlsx workbook changes to 6.
The Reporting.xlsx workbook saves.
The Reporting.xlsx workbook closes.

Running an automation recipe

Clicking the Run button executes the steps in the automation recipe.

After the successful completion of an automation:

The run time is displayed. NOTE: This can be turned off using the Automation Settings Change action.
The Run column displays green for each successful action completed.

Action errors

When running an action, there are many errors that may occur due to errors in invalid parameters.

In the screenshot below, the Excel Cell Value Change action has used a File Name of MyWorkbook.xlsx. However, there is no open workbook with that name. Therefore an error occurs.

The Run column displays amber for the action causing the error. All subsequent actions in the recipe are not executed.
An error message provides detail about the specific error.

Editing automation recipes

The elements of automation that can be edited are:

Adding, deleting, and moving actions
Action parameters
Run toggle
Comments and descriptions

Adding, deleting and moving actions

The buttons in the user interface provide the ability to add, delete and move actions within a recipe.

Add action: Adds the action to the bottom of the recipe
Insert action: Inserts the action into the selected row. Any actions below the selection are moved down.
Delete action: Removes the selected actions from the recipe
Move up/down: Moves the selected actions earlier or later in the recipe

Action parameters

Automation parameters are created using text within cells. To edit any existing action step, users need only change the text values or formulas used within those cells.

Further detail is provided in the section above.

Run toggle

Each action step contains a toggle option to turn specific actions on or off. Only actions where the run toggle contains Y are executed.

NOTE: Turning an action off, may cause errors with subsequent actions. For example, in the screenshot above, if Excel Workbook Open is turned off, the subsequent step of Excel Cell Value Change fails as the workbook Reporting.xlsx is not open.

Descriptions

The Descriptions field provides space to add comments and descriptions for each action step.

As automation recipes can become complex, this provides a way to help users understand what is happening in their automation recipies.

Saving and sharing

Automation recipes are created inside Excel workbooks; therefore, they can be saved, copied and shared like other Excel workbooks.

Working with objects

The Automation Toolkit is designed to work with objects. Knowing the names of those objects is essential for many actions. The names of objects are available in the following locations:

Sheets: Sheet names are visible as tabs at the bottom. Names of hidden tabs are visible in the VBA editor.
Named Ranges: Named ranges are visible in the Name Manager (Formulas > Name Manager), or by pressing F3 to display the Paste Names dialog box.
PivotTables: Select a cell in the PivotTable, then click PivotTable Analyze > PivotTable Name
Slicers: Select the slicer, then click Slicer > Slicer Settings. In the Slicer Settings dialog box find the section entitled Name to use in formulas
Queries: Query names are generally Query – followed by the name of the query. For example, a Sales Data query would be named Query – Sales Data
PowerPoint Shapes: In PowerPoint, select the object. Then, click Shape Format > Selection Pane to display the names of the shapes on the slide.

Using dynamic values

This section covers the use of variables and formulas to make parameters flexible, and easy to update.

Using variables within parameters

A specific area of the user interface is designed for holding variables. Variables are the values that might change inside our recipe. Using these variables inside our parameter text provides more flexibility when building recipes.

To create variables we only need to enter the name in the Variable Name column, then the value in Variable Values. In the screenshot below a variable called My File Path has been created with the value C:\Examples\Automation Toolkit\Reporting Example.xlsx

Variables are used within parameters by using the Variable Name surrounded by curly brackets.

When the automation recipe runs, the variable name is replaced with the variable value.

Using the cell reference as the variable

Many actions include parameters that require cell references. In the example below, we are referencing cell H11 of the recipe.

If the cell reference relates to a variable, we can use the variable name, followed by \a as an alternative to providing the cell reference. In the example below {Cell Value}\a is equivalent to entering H11, but maintain the variable name.

Using variables to reference their cells

Pre-populated variables

Two variables exist in the default template: This Workbook (cell H9) and This Worksheet cell H10). These are not essential for the tool to oparate but are provided as useful variables. They can be edited or removed if required.

This Workbook: Provides the name of the workbook containing the current recipe. This is calculated using the following formula:

=MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]", CELL("filename",A1))-FIND("[",CELL("filename",A1))-1)

This Worksheet: Provides the name of the worksheet containing the current recipe. The is calculated using on the following formula

=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))

NOTE: These parameters display as #VALUE! if the workbook has not yet been saved.

Using formulas/functions within parameters or variables

The parameters and variables are standard cell values. Therefore, we can use formulas and functions within those cell values.

In the screenshot below:

The open =” and closing “ indicate this is a standard Excel formula.
The functions TODAY() and MONTH() have been used to calculate the current month number.
“& and &” are used to concatenate the values into one text string.

At the time of writing, it is 4rd March, therefore MONTH(TODAY()) calculates as 3. The result of this is shown in the Values parameter below.

When the date changes to April, the Value parameter automatically updates to 4.

Using formulas in this way creates the ability to build dynamic action parameters and dynamic variables.

Essential elements of parameters

Parameters are text, with various elements required for the Automation Toolkit to operate correctly. These include:

Line breaks: Used to identify the end of each parameter. For advanced users, this can be modified using the Automation Settings Change action.
First colon in each parameter: Used to separate the parameter name from the value. The first colon ( : ) is essential for the reciple to know where a value begins.
Parameter order: The order of parameters for each action must remain as provided. Changing parameter order, or removing parameters will prevent actions from running.

Invalid text formulas

When working with Excel formulas to generate text, it is possible to generate errors, details of these are listed below.

Excel function errors

Users may try to create parameter formulas that do not calculate correctly. Where this occurs, the standard Excel errors of #VALUE!, #NAME!, etc., may appear. All formulas must calculate as valid text strings; otherwise, they will cause errors when running the automation.

Invalid text syntax

Excel may display the following error message:

This is a standard Excel error that occurs when a formula cannot be evaluated due to incorrect syntax. This often occurs when double quotes to open/close text strings have not been used in the correct locations.

256 character limit

Excel allows up to 32,767 characters in one cell. However, within formulas, any individual piece of text (i.e. the characters contained between a pair of double quotes) must be less than 256 characters.

Exceeding the 256-character limit triggers the following error:

Text values in formulas are limited to 255 characters. To create longer text values in a formula, use the CONCATENATE function or the concatenation operator (&).

Where this error occurs, the text can be divided into smaller sections using “&”.

Using double quote characters within parameters

Some parameter formulas may require the double quote character to be used inside the string. In Excel, the double quote is used to denote the start or end of a text string, therefore users must either use an escape character or the CHAR function to force a double quote into the text.

As an example, in the action below, the parameter formula contains the TEXTJOIN function. This function requires a character placed in double quotes.

There are two ways to create the required double quotes for this scenario.

Escape character

To force a double-quote character inside a string we use two double-quote characters together.

In the screenshot below, “” creates a single “ in the calculated result. Therefore, “”,”” renders as “,” within the final paramter text.

CHAR function

The CHAR function in Excel creates characters based on the ASCII character codes. 34 is the ASCII code for a double quote character.

In the screenshot below CHAR(34)&”,”&CHAR(34) calculates as “,” within the final text string.

Logic and looping

Within the Automation Toolkit, there are actions specifically designed to create logic and allow different paths depending on the result of a cell.

This logic also enables looping functionality in a recipe.

Logic actions

The actions used for logic are:

Automation If True GoTo – This tests a cell value:
- Where the value is TRUE, the automation jumps to the point named in the Automation Reference.
- Where the value is FALSE, the automation continues on to the next step.
Automation If False GoTo – The same as Automation If True GoTo, but tests for a FALSE value.
Automation Reference – This creates a reference point for the Automation If True GoTo and Automation If False GoTo to jump to.
Automation Stop – Stops the automation running.

In the Screenshot below:

The File Existence Get action executes and checks for the existence of a file in C:\Examples\Missing File.xlsx and returns TRUE or FALSE to cell H13 of the Logic sheet of the Automation Toolkit v1.2.xlsm workbook.
The Automation If False GoTo action checks the value in cell H13. If the value is FALSE, it jumps to the Automation Reference action where the reference is JumpHere (4), otherwise the action continues to the next step (3).
If the C:\Examples\Missing File.xlsx exists the recipe continues into the Excel Workbook Open, Excel Value Change, Excel Workbook Save, Excel Workbook Close actions. The Automation Stop action ends the automation.
If the C:\Examples\Missing File.xlsx does not exist, the recipe jumps to the Automation Reference action. The recipe continues to the subsequent action. The actions starting at (3) are jumped over and never executed.

Building loops

Logic actions combined with the Excel Cell Value Change action create looping functionality. An example of this is included in the Getting Started guide.

Other features

The following describes the other features of the Automation Toolkit interface.

Pencil icon / renaming the automation [1]

Click the pencil icon to edit the heading in Cell B1

Search [2]

There are a lot of actions contained within the Automation Toolkit. Use the search box to find actions faster.

After entering text into the search box, the actions list displays only the actions containing that text.

Parameters [3]

When using variables within parameter text, click the Parameters icon to display the full text with the variables evaluated.

The following is an example where the {Full File Path} variable has been evaluated to a full file path.

Clear [4]

Clears the automation recipe and variables sections; reinstating the pre-populated formulas in H9 and H10.

Wrap mode [5]

Control how actions are displayed with are three wrap modes:

Wrap: The automation recipe and variables are always wrapped
Responsive: Only the action step and variable for the selected rows are wrapped
None: No text wrapping occurs

NOTE: Text wrapping is a visual format. When not wrapped, the line break character is still the default parameter separator, but this may not be visible to users.

Help button [6]

The Help button provides a link to the Automation Toolkit help pages (https://exceloffthegrid.com/automation-toolkit-help/)

Excel Off The Grid website [7]

Click the Excel Off The Grid logo to visit the website and discover the latest blog posts and information (https://exceloffthegrid.com)

Common error messages

As the Automation Toolkit uses customized parameters, there is a high chance of user error while building recipes.

. The following are common error messages and their meaning:

Text values in formulas are limited to 255 characters. To create longer text values in a formula use the CONCATENATE function or the concatenation operator (&).
- Excel only permits individual text strings of less than 255 characters within a formula. Add “&” within the string to create individual strings less than 255 characters.
The [xxxxxx] action does not exist. Please select another action and try again
- The named action does not exist, check no additional characters have been added into the action name
Error Number: 450 – Wrong number of arguments or invalid property assignment
- The number of parameters required for the action differs from the number of parameters used. You may have accidentally deleted some parameters.
- The parameter separator (i.e. a line break by default) has been used inside a parameter. Look at the Automation Settings Change to change the default separator for the automation.
Error Number: 1004 – Cannot run the macro [xxxxx]. The macro may not be available in this workbook or all macros may be disabled
- The named action may be unable to run because Excel is in edit mode. If using Keyboard Press or Mouse Click actions, ensure Excel is not in edit mode before undertaking additional actions (e.g. use the {Esc} keyboard pgress}.
Error Number: 1004 – Sorry, we couldn’t find [workbook file path]. Is it possible it was moved, renamed or deleted?
- The File Path parameter is referencing a workbook that cannot be found
Error Number: 1004 – Method ‘Range’ of object ‘_Worksheet’ failed
- A cell reference or named range is invalid
Error Number: 1004 – Application-defined or object-defined error
- The file path for the PDF does not contain a valid folder path
Error Number: 13 – Type mismatch
- A parameter requiring a True/False value does not contain True or False
Error Number: 9 – Subscript out of range
- A workbook or sheet name refers to an item that does not exist, cannot be found, or is not open (check for typos, and remove any extra spaces)
- In the Excel Cell Value Change step, there are more cells listed than values

Each action has its own specific errors; refer to the action in the Actions Reference guide for more information.

Known limitations

Excel is a vast tool, with many features not compatible with the Automation Toolkit.

Sufficient testing should always be undertaken to ensure recipes work as expected.

The following are known limitations.

SharePoint/OneDrive syncing

The Automation Toolkit works well with files saved locally and on network drives. Files saved on SharePoint or OneDrive can cause issues. The Automation Toolkit executes faster than file syncing to OneDrive or SharePoint. For example, when a file name changes, the previous file name may persist for seconds or minutes until the file is synced with the SharePoint/OneDrive server.

SharePoint/OneDrive auto save

Files synced with OneDrive and SharePoint are likely to have the AutoSave feature enabled. This may cause changes to be saved inadvertently. To prevent changes impacting existing workbooks, either:

Open Workbooks as read-only if changes are not expected to be saved
Use the File Copy action to create a copy of a file before working on the copied version.

Tips and tricks

The following tips and tricks will help you to get the most out of the Automation Toolkit and avoid errors.

Build recipes assuming the required workbooks are closed, and ensure workbooks are closed prior to running an automation recipe. This reduces errors and ensures recipes are executed against the correct workbook.
There is no action for creating documents. Instead, have empty versions saved, then use the File Copy action to copy the blank file.
Create multiple recipes in one workbook by copying an existing recipe worksheet.
Keep a clean copy of the original Automation Toolkit template. To create new Automation Toolkit workbooks, take a copy of the template workbook.

Discover other articles: