Merge Individual Formatted Excel sheets into 1 Workbook with individual Formatted Tabs

[rhutchison]

Hi All. I've watched several YouTube videos and read the discussions I can find on this topic but I just can't find what I need.

This is what I have going on:

1 data input that I need to break out into 3 Formatted Tabs (Premium, Claims, Statement). Each tab has its own template that I need to match to, and each template has its own schema. I also have to repeat this for each individual accounts listed in the input (I haven't gotten this far)

I have been able to take the single input and get the 3 formatted tabs to output (by using 3 different workflows). I can't get them to output to the same workbook. I also can't figure out how to combine them into a workbook once they are output.

I would like, if possible, 1 workflow to do the following:

Input 1 has data for 6 accounts. 

Output 1 = Account 1 with Formatted Statement, Formatted Premium, and Formatted Claim in one workbook, in that order, named Account 1

Output 2 = Account 1 with Formatted Statement, Formatted Premium, and Formatted Claim in one workbook, in that order, named Account 2

Etc.

I will not always have the same accounts or number of accounts so I know I need a directory tool here and a Macro to repeat tasks but I just can't get them to work.

To be honest, I was impressed with myself that I got the blob tool to get the formatting.

Any assistance is appreciated.

[chuckleswk]

It would be helpful if you had an example input and output that we could look at to help with creating the workflow.

[rhutchison]

Hi. Thanks for looking! I've attached my "Input 1" which is the coinsurer transaction template. My final output should have date on the FBS template.

My Coinsurance workflow gets me the data split between account. I use that output to then run the Create All Templates. This outputs the Premium Tab Formated and the Claims tab formatted. Then I have to run Statement of Acct template. So I can get all the data output in pieces. I would then need to run Create All Templates and Statement of Acct on all outputs from the Coinsurance Workflow and then combine them all.

I know there has to be a simpler way.

[OTrieger]

@rhutchison 

It seems that the automation will do the same for each of the accounts, so what you need to do is build a workflow that can handle just that. Put it into a batch macro and then have the accounts as the Control criteria. To get the different naming for the files just imbed the account naming in the file name path and the Batch Macro will create all the needed files from the same template files, and then create the needed output for each of the accounts.

[chuckleswk]

This can all be done in a single workflow. I have a workflow that I'm doing something similar (the data is being pulled from a database) and then parsing it out and building the output and writing each file out to the desired location with different account names. I will see if I can work through or provide you with an example.

[rhutchison]

Thanks! I've been trying to go at it with the Batch Macro but haven't quite mastered these when my workflows reference containers. I also run into the problem that I can't use Block Until done within a control container so it stops my workflow because I'm trying to reference the same input in multiple places. I've tried to remove block until done and only use control containers, but then my outputs either throw an error (in use same time) or I the output overwrites, even though I have the outputs going to different sheet names..

[Gaurav_Dhama_]

Don't use control containers if you don't need to. Just use normal containers for grouping the process together. Control containers are for controlling the process flow and not the output, although it can but it's not for that.

I have seen very few cases where you actually need a control container. I would always start without control container and use it only if there is no other alternative.

I had a look at the workflows attached and they don't really need control containers. 

[chuckleswk]

Just to clarify, is the Claim Template the same as the FBS_Template?

[rhutchison]

In the FBS_Template are the individual templates. Each tab is its own template. I tried breaking them into their own individual templates but that seemed to make it messier. So for input for claims, I had it pull in the claims tab, premium, the premium sheet and statement of acct, that sheet. Does that make sense?

I really appreciate you looking into this!!

[rhutchison]

To add, the FBS_Template is what the final output should look like. And I need one for each individual account code on the coinsurer input.