BLOB - Workflow to output Multiple tabs_files

[PassION_es]

Dear Alteryx Blob expert,

I have a case in which I want the output to be formatted as it is (see input_BLOB tab) and populate all cells in pink font.  I would like to output multiple tabs, with similar format, headers, table etc.

Please refer to INPUT.xlsx for the sample input and table format.

Kindly note that I have already read and understand blog about this but still cannot find use cases similar to mine. The Blob: Reporting That Isn’t From a Horror Film - Alteryx Community

Thank you.

[PassION_es]

I already developed a workflow with Blob tool and it produce multiple outputs.  However, the output that I am trying to have should be multiple tab names (instead of files).

[apathetichell]

The blob workflow works off a template files where the files schema (sheet names) do not change.  You can copy the file multiple times - but any new sheets - not in the template file -  will not be formatted. You will need to create a formula with filename|sheet$cellrange to write over the sheet in the file you want.

[PassION_es]

hi @apathetichell , thank you for the response. By the way, can you share with me a sample formula and where to put that? is it after blob input tool?

I have formula to make the filename dynamic but it did'nt work, because although the workflow produced the right sheet names and files, but the file is not formatted, ONly the "sheet1" file is formatted and the rest of the sheets do not have formats.

Here's my sample formula: [File]+"|||"+[GL_AU_Entity]+"$D4:D4"

[GL_AU_Entity] -this is the sheet name (dynamic).

Thank you.

[apathetichell · Accepted solution]

Yes. That's what I said. Only the formatted sheet in your template file will be formatted. You must format every sheet you want formatted in your template file.

[PassION_es]

But I have hundreds of sheets that need to output, is there a formula that can change sheets dynamically in BLOB? Appreciate if you can share a sample blob workflow. Thanks @apathetichell 

[caltang · Accepted solution]

If the sheet does not exist, it means the template does not exist as well. So your output will still work but it will create a new tab which is not formatted. 

If you want to dynamically create that new sheet and maintain the template, you could create a template excel and duplicate each tab until x number of times to achieve the count of tabs you want, then you can dynamically rename the template with some VBA: https://techcommunity.microsoft.com/t5/excel/i-would-like-to-rename-all-the-sheets-from-a-list-of-na...

Then your Blob from Alteryx will be able to write out to the Excel template and each will maintain their format.

[caltang]

Alternatively, you can design and render them out with the reporting tools. This will take some effort, and may not be as fast/flexible as a pre-formatted Excel output template. @DataNath wrote it here: https://community.alteryx.com/t5/Engine-Works/How-to-Render-to-Multiple-Named-Excel-Sheets/ba-p/1015... 

[PassION_es]

okay thank you @caltang 

[apathetichell]

you can try this macro that I built - it does require openpyxl. https://github.com/apathetichell/2024_AlteryxMacros/blob/main/Excel%20Template%20Sheet%20Copier.yxmc

@caltang - if you have a chance - any feedback would be much appreciated.