How to Batch your Macro

by Philip Mannering

If you have a task that is repeated, then you may want to turn it into a Macro. You can read how to do this here: Further, if you want to run that task for a list of different inputs, then it may be prudent to turn that macro into a Batch Macro.

For example, I have a created a macro that converts a single postcode to latitude and longitude coordinates as shown,

However, the problem with running this macro with more than one postcode is that the output gets concatenated together,

This can be remedied in several ways. One way is to us a Text-to-Columns Tool and then pivot. But this is convoluted and the number of columns will vary depending on the number of records (or in this case postcodes) in the list you want to batch process. A neater, more elegant way is to convert your macro into a batch macro.

To create a batch macro, instead of using a Macro Input, retain the Text Input tool (before converting to Macro Input). Add a Control Parameter Tool and connect it’s ‘Q’ connector to the lightning bolt on top of the text input. An Action tool should appear between the connection as shown below,

The Action Tool has been configured to Update the Cell that contains my postcode. By using this Control Parameter we can run this Macro a multitude of times using a field filled with al list of postcodes. By clicking on the canvas and navigating to the Workflow pane in the Configuration Window, you should now also be able to see that the Macro is classed as a ‘Batch Macro’ and that this value is greyed out and cannot be changed.

Now when you connect your input to the batch macro you must select the Control Parameter in the Questions tab of the configuration window. In my case, I select postcode (String) as this is what I wish to change every time the macro is ran. This should produce a list of outputs to match your list of inputs, with every new output automatically appended rather than concatenated in a single line. Whenever you want to create a macro for a repeated task, but still wish maintain some control over how the tools in that macro operate, it will often be very useful to add control parameters to the appropriate tools within the macro. NB: If the datatype of each value is likely to change for each record, pick an option other than All iterations have the same output schema (error if different) on the Interface Designer Window (Ctrl + Alt + D). Congratulations. You’ve just saved oodles of time by ‘batching your macro’.


Philip Mannering

Tue 23 May 2017

Wed 19 Apr 2017

Wed 19 Apr 2017