Update a macro with TWO tab inputs at once

[jordansandona]

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

[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.

[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!

[JoeS · Accepted solution]

---

@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.

[jordansandona]

Perfect!!! I just needed to add a crosstab to my first macro to have the Comments & USD tabs as separate fields, on the same line as the same file. Then I used each field as a control parameter to separate the tabs as they went into the next macro to be joined. 

Thank you SO MUCH for your help!

[JoeS]

---

@jordansandona wrote:

Perfect!!! I just needed to add a crosstab to my first macro to have the Comments & USD tabs as separate fields, on the same line as the same file. Then I used each field as a control parameter to separate the tabs as they went into the next macro to be joined. 

Thank you SO MUCH for your help!

---

Great stuff, always nice to be able to use a lot of what you have already done!

And you're most welcome!