Output Data to Multiple Files in .xlsm

[aba]

Hi Everyone,

Hoping I can get some help from someone please. I've got a workflow that essentially reads in records from a file, executes some data processing in terms of grouping and then outputs the finished output. Problem is, I'm trying to output into a specific .xlsm template as a journal file for upload. I can successfully output into the template with an named range in excel (anything other than this and Alteryx complains about the formula in the template), but challenge is creating multiple files (i.e. multiple template journals) for each group of transactions. 

When I try to use a field in the data for multiple outputs, it says I need to specify a sheet for the output.

Can anyone please help?

aba

[Prometheus]

@aba I created a simple workflow using Sample Superstore data to show how you can dynamically output to multiple files and sheets. The field in my data that I used to dynamically create files is "Segment," of which there are three -- Consumer, Corporate, and Home Office. I also dynamically created sheets within these files based on the "Ship Mode" field, so each created file also has a sheet named after one of the ship modes, that is "First Class,", "Same Day," "Second Class," and "Standard Class." You can choose to name the sheet and range dynamically like this. If you look at the configuration of the Output Data tool, you'll see that I selected to Take File/Table Name From Field and chose the field I created in the Formula tool, called "FullPath" and then chose not to keep it in my output.

[aba]

Thanks @Prometheus. I understand that part quite well. I think my real challenge is incorporating the template into the workflow and then having it output multiple files based on groupings of a field in the data. Can you advise on that?

Thanks,

Aba

[ArnaldoSandoval]

Hi @aba 

I want to understand your issue better, please answer these questions for me:

• Do you have a single XLSM template, from which you generate multiple XLSM output files?
• Do you have many XLSM templates and generate multiple outputs from them.

When I was faced with a similar challenge, I added a sheet to copy of the XLSM template file and with a macro transfer data from that sheet to the proper location in the template.

But I need to understand your scenario better.

Arnaldo

[jdminton]

@aba , The attached workflow is something like what you're wanting. It currently replaces the sheet name, but you can modify the formula to replace the filename. The trick is to bring in the fullpath of the filename in the input tool and modify it with the formula tool. Then in the output, you can use that field to identify where it gets saved to.

  

 

  

 

 

[aba]

Hi @ArnaldoSandoval ,

Sorry been away for a bit. The first scenario would explain it better. I have a single XLSM template which I would like Alteryx to feed into and generate multiple XLSM output files based on groupings in the data.

Hope this explains better.

Aba

[jdminton]

@aba did you see my suggestion above? Did it work?

[ArnaldoSandoval]

Hi @aba 

Please take a look at this topic Locking columns in excel output The workflow structure may solve your problem, for your scenario you may end with multiple green containers, one per each specific xlsm files, and the formula in that container will change per specific xlsm file.

Then you will have multiple purple containers, one per specific xlsm file.

I don't believe you need the third container, as you never mention any need to run a macro defined in the template.

Once you review the topic, you can return here, adding a mocked up sample data to generate as many xlsm as you need and we customise the workflow in that topic.

hope it helps,

Arnaldo

[ChrisWaspe]

Hi @aba . You can use Blob Input tools to read in a .xlsm template. You can then use a Blob Output to dynamically create the amny outputs you need, and then using either Control Containers or the Block Until Done tool, you can write to those files: please see the blog I wrote on something similar: https://community.alteryx.com/t5/Engine-Works/Methods-for-Creating-Formatted-PDFs-Exploring-BLOBs-VB...

[aba]

Hi @jdminton,

Thanks a lot for your detailed explanation and solution. After trying this, I did get multiple outputs with the data I needed. Unfortunately, it wasn't in the formatted .xlsm template I needed.