BLOB - Workflow to output Multiple tabs_files

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.

File 1.xlsx

INPUT.xlsx

File 2.xlsx

Topic of Interest

Output

Common Use Cases

Transformation

Accepted answers

apathetichell

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.

caltang

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-names-i/m-p/204784

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

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.

All comments

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

workflow_snapshot-BLOB.JPG

2ND FORMULA_BLOB.JPG

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

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

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.

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/1015093

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.

PassION_es

Thank you @apathetichell , I will check with someone from our company who knows what python package that I need to have.

apathetichell

@PassION_es try opening alteryx as admin (which is not the same as downloading alteryx in admin mode) - just right click designer and click run as administration. Then try running my app macro once - It should (should being the key word) use the Alteryx install.packages module to install openpyxl in your python venv.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703