Automation Toolkit Getting Started

< Back to Help menu

This article guides you through an example project, to help understand the basics of working with the Automation Toolkit.

The project includes an example file to download. By working along with each stage you will get to grips with the fundamentals much faster.

Contents

About the project

In this project, we work through an example of saving multiple PDF files from an Excel report. This may not be your scenario, however, once you’ve grasped the fundamentals, you can apply them to your scenario.

Download the example file here: Reporting Example.xlsx Download

The sections of the example report are as follows:

Automation Toolkit Report Layout
  • The Print Area is set to include only the report area.
  • The regions include North, South, East, West and Central.
  • The Data includes all month-end dates from 31 January 2023 to 31 December 2023.

When the cells in the Selections are changed, the report recalculates to include the data for the selected region and date.

Stage 1: Base automation

Level: Beginner

Stage Overview

In this stage, we build an automation recipe to:

  • Open an Excel Workbook
  • Change two cell values in the Excel workbook for the region (cell J4) and the date (cell J6)
  • Save the Print Area as a PDF file
  • Save the workbook
  • Close the workbook

Steps

As this may be your first view of the Automation Toolkit, we’ll go through the steps of this stage in detail.

  1. Take a copy of the Automation Toolkit template and save it as My Automation Project.xlsm
  2. Open the My Automation Project.xlsm workbook
  3. Let’s add some actions to the automation recipe. In the Search box, type Open.
    Search for the word Open to reduce the action list
  4. The items in the Actions list now include only those containing the word Open.
    Show the drop-down list of all actions
  5. Select Excel Workbook Open from the Actions list, then click the Add Action button.
    Add action to Automation Recipe
  6. The Excel Workbook Open action is added to the automation recipe.
    New action added to recipe - Excel Workbook Open
  7. The first action is to open the Reporting Example.xlsx workbook. It does not have any passwords or links, and we do not want to open it as read-only. To achieve this, change the example text in the Parameters column to:
    File Path:C:\Examples\Automation Toolkit\Reporting Example.xlsx
    Read Only:False
    Update Links:False
    File Open Password:
    Modify Password:
    Do not include any spaces after the : (colon) characters.
    Adjust the File Path in the first line to the location where the Reporting Example.xlsx workbook is saved.
    The action step should look as follows:
    Completed action step
  8. Repeat the steps for all the actions listed in the recipe below:
    Base automation all steps

Notes:

  • In the Excel Cell Value Change action, use the date format for your region (e.g. for United States use 07/31/2023)
  • In the Excel PDF Save action, ensure the PDF File Path is valid for your environment

Run the automation recipe

Ensure the Reporting Example.xlsx workbook is closed.

Click the Run button.

Run the automation recipe

Automation recipe ran successfully?

If you entered all the parameters correctly, the automation recipe runs:

  • The Reporting Example.xlsx workbook opens. In the Report tab, cell J4 changes to North, and cell J6 changes to 31/07/2023 (or your region equivalent)
  • A PDF file is saved in your chosen file path
    Automation Creates PDF
  • The workbook saves and closes.
  • A message box appears showing the run time
    Successful automation run - shows time

Error message appears?

If you have not entered the parameters correctly, the automation recipe starts but does not complete.

  • A message box appears showing an error. This provides details about the problem. Click OK to close the message box.
    Automation Error Message - Number 9
  • The actions that have run successfully are highlighted in green, and the action causing the error is highlgihted in amber.
    Automation recipe with errors
  • Correct any errors. (In the screenshot above, the error is caused by a typo; the workbook name has been entered as Reporrting Example.xlsx)
  • If you are struggling to find the error, the following is a list of common error messages and causes:
    • 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 or does not exist
    • 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 cell references listed than values
    • Error Number: -2147021773 – Automation error. The filename, directory name, or value label syntax is incorrect
      • The file path for the PDF does not contain a valid folder path

Once the errors are removed, close the Reporting Example .xlsx workbook, and click the Run button again.

Continue fixing any errors until the process runs correctly.

Stage 2: Using variables

Level: Beginner

Stage Overview

In this stage, we build on Stage 1:

  • Change the hardcoded File Path, File Name, PDF File Path and Cell Value parameters to variables.

Steps #1

Let’s create variables to use within the action parameters.

  1. Enter the following into cells G11 to H15:
    • G11:Full File Path
    • H11:C:\Examples\Automation Toolkit\Reporting Example.xlsx (amend to match with the location of the workbook on your PC).
    • G12:File Name
    • H12:Reporting Example.xlsx
    • G13:Region
    • H13:North
    • G14:Date
    • H14:31/07/2023 (use your local date format)
    • G15:PDF Save Path
    • H15:C:\Examples\Automation Toolkit\Save PDF.pdf (amend to match with the location on your PC).
      The variables section will look like this.
      Variables List for automation
      The values in Column G are the Variable Names. the values in Column H are the Variable Values.
      Do not change the values in cell G9 to H10. These are pre-populated variables that we use in the following stages.
  2. Next, to use the variables withing the parameters we use {VariableName} within the parameters text.
    For example The Excel Workbook Open parameter text changes to:
    File Path:{Full File Path}
    Read Only:False
    Update Links:False
    File Open Password:
    Modify Password:
  3. Update the remaining actions in the recipe.

The full recipe looks as follows:

Full recipe for Using Variables

Run the automation recipe

Ensure the Reporting Example.xlsx workbook and Saved PDF.pdf file are closed.

Click the Run button.

As in Stage 1, if the automation recipe runs correctly, the run time appears and a PDF file is saved (or overwritten if it already exists). If there are any errors, correct them. Ensure the Reporting Example.xlsx workbook is closed, then run the automation again.

If you have an error and are unable to find it, click the Parameters icon. This shows the text which is used in the recipe after the variables have been included.

Using the Show Parameters Feature

As shown in the Screenshot above, the {Full File Path} variable is converted into the corresponding file path.

If variable names are still visible in the parameters box, it means the variable does not exist or is mistyped. Variable names are case-sensitive.

Updating the variables

Now, let’s change the variable values.

  1. Change the cell values in the automation recipe as follows:
    • H14:South
    • H15:31/08/2023 (or your local date format)
  2. Click the Run button

The automation recipe runs again. The saved PDF file is for the South region on 31 August 2023.

To save PDFs for each region and month, we only need to change cells H13 and H14, then click Run.

Steps #2

Variables can be formulas based on other cells. Let’s change the file name of the PDF to incorporate the Region name and date.

  1. Change the formula in cell H15 as follows:
    ="C:\Examples\Automation Toolkit\Save PDF - "
    &$H$13&" "&TEXT($H$14,"YYYYMMDD")&".pdf"
  2. Click the Run button.

The automation recipe now saves a version of the PDF file called Save PDF – South 20230831.pdf.

Stage 3: User input

Level: Beginner

Stage Overview

In this stage, we build on Stage 2:

  • Add steps to allow the user to select the Excel workbook to run the automation on
  • Add steps to allow the user to provide the date to run the report for

Steps #1

Firstly, let’s give the user the power to select the file to run the automation on.

  1. In the Actions list, find the File Selection Get action.
  2. Select the first action in the automation recipe, then click the Insert Action button. File Selection Get should now be the first action.
  3. Change the parameter text for the File Selection Get as follows:
    Title:Select a file
    Button Text:Select
    Filter Description:Excel Workbooks
    Filter Extensions:.xlsx;.xlsm
    Default Folder:C:\Examples\Automation Toolkit\
    File Name Element:Full
    Return File Name:{This Workbook}
    Return Sheet Name:{This Worksheet}
    Return Cell:$H$11
    Ensure the Default Folder is a valid folder on your PC.
    This time, we are using the pre-populated variables in H9, {This Workbook}; and H10, {This Worksheet}.
    This action asks the user to select a file. Once they clicked Select, the Full file path is returned to cell H11 of the current recipe sheet {This Worksheet} in the workbook {This Worksheet}.
    The Return Cell parameter requires a cell reference, so we have entered $H$11. This is the cell for the Full File Path variable. There is an alternative method that uses variable names. Change, H11, to {Full File Path}\a to get the cell address of that variable (\a is a switch to represent the address of the variable).
    slash a to return the cell address
    As shown above {Full File Path}\a evaluates to the cell $H$11
  4. As the user can select any file, the File Name variable is likely to be wrong. This needs to be calculated based on the Full File Path variable. Enter the following formula in cell H12.
    =MID(H11,FIND("*",SUBSTITUTE(H11,"\","*",LEN(H11)-LEN(SUBSTITUTE(H11,"\",""))))+1,LEN(H11))
    This and other formulas for working with Automation Toolkit are available on the Useful Formulas reference page.
    This formula works on all versions of Excel. For Excel 365 users, there are easier versions of this formula available.

Run the automation recipe

Ensure the Reporting Example.xlsx workbook PDF files are closed.

Click the Run button.

The file selector window opens. Navigate to the Reporting Example.xlsx workbook, then click the Select button.

File selector

The automation runs as before and saves the PDF for the selected file.

If the user clicks Cancel in, the automation starts, but returns a blank value to cell H11. These subsequently cause errors in the recipe. Let’s correct this in the next stage.

Stage 4: Controlling flow

Level: Beginner

Stage Overview

In this stage, we build on Stage 3:

  • Add a check to stop the automation if a user clicks Cancel when selecting a file.

Steps

  1. Enter the following into the variables section:
    • G16:Cancel Clicked
    • H17:=$H$11=""
      If a user selects a file, H11 includes the file path, and H16 calculates as FALSE. If a user clicks Cancel, H11 is blank and H16 calculates as TRUE.
  2. Add an Automation Reference step to the end of the recipe. Set the parameter as follows:
    Reference:jumpToEnd
    The purpose of this action will become clear after the next step.
  3. Add an Automation If Value Is GoTo step between the File Selection Get and Excel Workbook Open actions. Set the parameter formula as follows:
    File Name:{This Workbook}
    Sheet Name:{This Worksheet}
    Cell:{Cancel Clicked)\a
    GoTo Reference:jumpToend
    Test Value:TRUE
    Comparison Operator:=
    This action checks if the cell of the Cancel Clicked variable is equal to TRUE. If so, the automation jumps to the Automation Reference action with the reference of jumpToEnd.

The entire recipe looks like this:

Controlling Flow Example

Run the automation recipe

Ensure the Reporting Example.xlsx workbook and PDF files are closed.

Click the Run button. Now, if a user clicks Cancel, the automation jumps to the end of the recipe and no action is taken.

Notes:

  • The reference name used in Automation Reference and Automation If Value Is GoTo must be the same.
  • For more complex recipies where jumping to the end is not an option, use the Automation Stop action.

Stage 5: Looping

Level: Intermediate

Stage Overview

In this stage, we build on Stage 4:

  • Add steps to allow the automation to loop through a list of all regions and prepare a PDF report for each.

Steps

  1. Add a new worksheet to the My Automation Project.xlsm workbook called Lists.
  2. On the Lists worksheet, enter the following values into cells A1:A7:
    Region List for Reporting
  3. In the automation recipe, enter the following into the variables section:
    G17:Total Count
    H17:=ROWS(Lists!$A$2:$A$6) – this counts the number of regions (excluding the header row)
    G18:Counter
    H18:1
    G19:Next Counter
    H19:=$H$18+1
  4. We want the Region variable in Cell H13 to be based on the List tab and the Counter variable. Therefore, in cell H13 enter the following formula:
    =INDEX(Lists!$A$2:$A$6,$H$18)
    If Counter (cell H18) is 1, the first item from the region list is displayed, if Counter is 2, the second item, etc.
  5. Between the Excel Workbook Open and Excel Cell Value Change actions, add an Automation Reference action with the following parameter formula:
    Reference:loopBack
  6. Between the Excel PDF Save and Excel Workbook Save actions, add an Automation If Value Is Go To action with the following
    File Name:{This Workbook}
    Sheet Name:{This Worksheet}
    Cell:{Counter}\a
    GoTo Reference:loopBack
    Test Value:{Total Counter}
    Comparison Operator:<=
    This action checks if the value in Counter is less than or equal to (<=) the Total Counter. If it is, the automation jumps back to the loopBack reference.
  7. Currently, we have an infinite loop, because the Counter in cell H18 never changes. To fix that, we add an Excel Cell Value Change action between Excel PDF Save and the last Automation If Value Is Go To. The parameter text to use is:
    File Name:{This Workbook}
    Sheet Name:{This Worksheet}
    Cells:{Counter}\a
    Values:{Next Counter}
    Separator:,
    This action makes the Counter equal to the Next Counter.
    This process keeps jumping back to loopBack until Counter equals 6. At that point, Counter is greater than Total Counter, and the recipe continues to the next step.
  8. Finally, we need to ensure Counter is set to 1 before the automation runs. To do this, insert an Excel Cell Change Value action at the top of the recipe with the following parameter:
    File Name:{This Workbook}
    Sheet Name:{This Worksheet}
    Cells:{Counter}\a
    Values:1
    Separator:,

The full automation recipe should look as follows:

Full Recipe for Example Project

Run the automation recipe

Ensure the Reporting Example.xlsx workbook and PDF files are closed.

Click the Run button.

If everything is working correctly, a PDF is created for each region.

Stage 6: Run automation recipe from a button

Level: Advanced

Stage Overview

In this stage, we build on Stage 5:

  • Execute the automation from a button on a worksheet, so we can hide the automation recipe worksheet.

Steps

  1. Add a new worksheet to the My Automation Project.xlsm workbook called Control
  2. Enter the following into the Control worksheet:
    Control worksheet
    For this example, Looping is the name of the worksheet which contains the automation recipe.
  3. In the automation recipe change the Date variable in cell H14 so it is:
    =Control!$C$2
  4. Insert a shape or icon onto the face of the Control worksheet. I’ve selected a picture of a robot.
  5. In the Name Box, change the name of the shape to Range:C4, press Enter to commit the name. C4 is the cell that contains the name of the recipe sheet.
  6. Right-click on the shape, select Assign Macro… from the menu
  7. In the Assign Macro dialog box, select CallAutomationShape, then click OK.
    Assign Macro to icon
  8. Finally, hide the recipe sheet

Run the automation recipe

Ensure the Reporting Example.xlsx workbook and PDF files are closed.

Click the shape to run the automation.

Now change the date in Cell C2 to 30 September 2023. Click the button again. This save PDFs for all regions for the selected date.

NOTE:

  • Running an automation recipe in this way does not trigger the run time message on completion. If you want a completion message, use the Input Message Display action at the end of your recipe.

TIP:

  • If you have multiple automation recipes in one workbook, why not create a Data Validation list to select between them, then one button can run all the automations.

Next steps

This project was an introduction to get you started with the Automation Toolkit.

The toolkit contains many other actions for working with Files, Folders, Power Query, PivotTables, Slicers, PowerPoint etc. So, now it’s time to create some automation recipes for yourself.

Discover other articles:





Getting StartedFeature OverviewActions ReferenceUseful Formulas



AdvancedFeature RequestReport Bug