Using a macro to import multiple sheets from your Excel workbook

by Naledi Hollbruegge

I recently worked with a file that had multiple sheets with the same format. In order to import all of these without having to add an Input Data tool for each sheet I built an iterative macro.

The macro starts with a Control Parameter tool, which reruns the entire workflow for each incoming record.The Action tool then defines what happens with these records. In this case the value is updated through the new input. The file path is specified and the option “Replace a specific string” is selected. Here I entered the name of one of the worksheets.

Next, I inserted an Input Data tool and specify the path to the file I was importing. Make sure to import the same worksheet that you previously entered in the Action tool. Each incoming record will now replace that part of the filepath and thus direct Alteryx to a new sheet that will be imported in the Input Data tool.

The following steps in this workflow bring each of those sheets into the same format so that they can then be processed in the main workflow. An extra tip at this point: Bring up the Interface designer (Ctr + Alt + D) and select the little spanner icon to change the properties. Here you can select how the data will be unioned. Due to some data sorting discrepancies in the files I had to change this to be configured by name. Alternatively it can be configured to trigger a warning if the output is not exactly the same in each sheet or be cofigured by position.


Macro workflow


Action settings

Once you have set up your macro, save it and then import it into your workflow by right-clicking the canvas > selecting Insert > scroll to the bottom and select macro. A new window opens where you can locate the macro you have just saved and this can now be inserted to your workflow just like any other tool.

Now, in order for the input to work you need to feed in all of the sheet names you want to run through the macro. One way is to type these out in a Text Input. A much easier way is to insert an Input Data tool, navigate to the file you are working on, and specify that you would like to query <List of sheet names>. This will bring in just a list of the sheet names in the file without any connected data.

As you can see in the image one of the sheet names is “Keys”, which is just a data dictionary that I don’t need and that has a format different from the other sheets. So I used the Select Records tool in order to take just those rows before and after this sheet. You could of course use a different approach, such as through a Filter or Formula tool. Now the list of sheet names is fed into the iterative macro, which is run automatically for all sheet names. The macro then outputs the data for each of the sheets and this output is prepared in the rest of the workflow for the final output format.

Bring in sheet names

Bring in sheet names


Final workflow with macro

© 2022 The Information Lab Ltd. All rights reserved.