Combining sheets (same name, same schema) from different files into one tab

[Sarath27]

Hi All,

I have daily output files named like this for an example (Repo_PV_2024-02-29.xlsx).

This file has a tab called "Trade Level Summary"..

My user wanted to create an App where user can select dates where this tab for chosen dates has to be stacked vertically with a column name - Date.

How can I do this? Pls kindly provide any idea on this.

[rzdodson · Accepted solution]

@Sarath27 here is one approach to do this:

This section of the workflow pulls the list of files from the source directory you are needing to reference, extracts the date from the file name, and then uses that date as a baseline to control Interface-enabled Date tools to control which file names are flowing in to the macro. 

This section of the workflow (the macro), uses the list of sheet names that we got. It'll swap out the file names, retrieving the data, appending the file name from where that data is coming from, and then because it is a macro it'll union the results together.

[Sarath27 · Accepted solution]

@rzdodson Amazing solution.  Let me try this out and keep you posted.

[Sarath27]

Please assist me on this.

How did you update the file value in Standard Macro? Pls find my screenshot and help me to update the tab names as "Trade Level Summary"

[rzdodson · Accepted solution]

@Sarath27 The file name gets updated from the values in the FullPath field that is coming out of the T anchor of the Filter tool. 

If you are wanting to update the specific section you highlighted, I would follow these steps to make sure that the macro doesn't "break".

Steps:

1) In the macro's input tool, update the referenced file with one from the directory (e.g. your 2024-02-09 file).
2) Go back to the Action tool.
3) Select your file name from the Input Data>File section. From here, your file will populate the "Repalce a specific string" field.
4) Delete everything after the ".xlsx" file extension. Since you are wanting to bring in the Trade Level Summaries, you are telling Alteryx here to preserve that so it brings in data from that worksheet.
5) Save the macro, and go back to your original workflow.
6) You'll be prompted that the macro has been changed. Click "Ok".
7) Re-run macro.

[Sarath27]

@rzdodson It worked, amazing solution! Thanks much for the very prompt response.