Update a macro with TWO tab inputs at once

Hi everyone,

We are a couple newbies looking for help on an interesting Alteryx task. Unfortunately it is a confidential workbook so we are unable to share, but please let me know if you need any more info to clarify the issue below!

We have a nested batch macro. The Alteryx starts out with a Directory tool, sending the FullPath (file name) into the first macro. This macro extracts the sheet names of each file, and uses a filter to filter out any sheets of the Excel files that we don't need to deal with. We need to keep TWO tabs that we need to combine in the next macro. These tabs will always have the names "Comments" and "USD" respectively.

Then we have been sending the full file name PLUS sheet name into the next macro, but think this is what needs to change since we recently added the Comments input tab that we need to combine with the USD tab. These tabs are not identical and contain different information that gets matched up based on one field. This second macro is where the summarization of the tabs happens, with the Comments and USD tabs needing to be joined up almost immediately.

Essentially, our need is to go file by file in a folder, and combine TWO specific tabs of each file and then perform further summarization of the combined files. Does anyone have any ideas on this?

So far our trouble is that in the first macro mentioned where we filter to the tabs we need, we end up with th

Datasets

Tips and Tricks

Common Use Cases

Batch Macro

Help

Dynamic Processing

Preparation

Iterative Macro

Accepted answers

JoeS

@jordansandona wrote:
Hi @JoeS - thank you SO much for the quick response. This is a big help!
Is there any way to build in a safety net on the chance one tab in the hundreds of files we're running gets accidentally named Comment, rather than Comments? We're trying to prevent this but it is a possibility I should have mentioned.
Thank you!

If that was the case, I'd combine it with what you have done already, where you read in the sheet names and filter for them.

Then instead of just having one control parameter, have two, one for USD and one for Comment(s).

Pass through the full string with the sheet name into the batch macro and replace the whole string in the action tool.

All comments

JoeS

Hi @jordansandona

I'd go about this a slightly different way as you always know the sheets you want.

I have created a batch macro that reads in 2 sheets from each file.

I then have a control parameter that updates the file name part of each input.

I can then use this in a workflow above and just use a directory tool sending in the FullPath.

I have uploaded a zip file with my example files and workflow/macro.

Hopefully that will work for you in your scenario too.

TwoTables.zip

jordansandona

Hi @JoeS - thank you SO much for the quick response. This is a big help!

Is there any way to build in a safety net on the chance one tab in the hundreds of files we're running gets accidentally named Comment, rather than Comments? We're trying to prevent this but it is a possibility I should have mentioned.

Thank you!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703