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

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.

Output

Macros

Batch Macro

Accepted answers

chuckleswk

I was able to work through it and come up with a solution similar to what I currently do with one of my other workflows. This solution does combine all of your workflows down to one and uses the input file just once and pulls all the data from that. It's not as clean as I would like it to be, but I think you'll get the general idea from it.

Some things to note that I cleaned up but wanted to make you aware of:

The Statement of Acct section produced multiple duplicate records, so I just removed all of those duplicates
- Because of this, please validate the output to make sure I didn't mess anything up!!!
Throughout the Claims / Premium / Statement of Acct section, I did identify a few steps that you were doing that weren't needed or could be combined with other steps you had previously done.
In your Statement Of Acct workflow - Formula Tool 96 you create a field called "Premium Due" and while I believe that you wanted to use this field to output the Premium Due on the Statement of Acct, you de-select the Premium Due field and use the Sum_Open Balance field.
- I did correct it in my workflow but am not sure if that was intentional or not
In your Statement of Acct workflow - You have two sections (Aviation Premium and Total Aviation Premium) that do the exact same thing and output to the same location.
- I did not include the Aviation Premium and built everything off of the Total Aviation Premium section that you have
You will want to update Formula (49) to output the file to where you want them to goto. This will update all of the outputs.
If you want to just run and get the data and not output files, then all you have to disable is Control Container (60). This will ensure that none of the follow-on sections are run but you will still be able to view all of the data on the tools prior to the outputs.

This does use Control containers for the output so that each portion that is loaded to the file waits until the previous section finishes. This ensures that the output files aren't open at the same time.

I did also include a final step that deletes all of the *.bak files that are created in the previous step.

Sorry this is long winded! Let me know if you need some further explanation on what it's doing of if there is something that I missed.

Create Reports.yxmd

All comments

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.

FBS_Template.xlsx

Coinsurer_Transaction_Template.xlsx

Coinsurance.yxmd

Create All Templates.yxmd

Statement of Acct Template.yxmd

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.

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.

chuckleswk

Do you have an example of what the final output looks like?

rhutchison

Please see attached.

CONT0733.xlsx

chuckleswk

Some things to note that I cleaned up but wanted to make you aware of:

The Statement of Acct section produced multiple duplicate records, so I just removed all of those duplicates
- Because of this, please validate the output to make sure I didn't mess anything up!!!
Throughout the Claims / Premium / Statement of Acct section, I did identify a few steps that you were doing that weren't needed or could be combined with other steps you had previously done.
In your Statement Of Acct workflow - Formula Tool 96 you create a field called "Premium Due" and while I believe that you wanted to use this field to output the Premium Due on the Statement of Acct, you de-select the Premium Due field and use the Sum_Open Balance field.
- I did correct it in my workflow but am not sure if that was intentional or not
In your Statement of Acct workflow - You have two sections (Aviation Premium and Total Aviation Premium) that do the exact same thing and output to the same location.
- I did not include the Aviation Premium and built everything off of the Total Aviation Premium section that you have
You will want to update Formula (49) to output the file to where you want them to goto. This will update all of the outputs.
If you want to just run and get the data and not output files, then all you have to disable is Control Container (60). This will ensure that none of the follow-on sections are run but you will still be able to view all of the data on the tools prior to the outputs.

I did also include a final step that deletes all of the *.bak files that are created in the previous step.

Sorry this is long winded! Let me know if you need some further explanation on what it's doing of if there is something that I missed.

Create Reports.yxmd

rhutchison

This is fantastic! Let me check it out today. To answer your question below:

What I'm attempting to do here is a balance validation.

If the claims balance > premium balance, Claims balance should be in Claim balance and then the Total due should be the NET balance of claims-premium.

If the premium balance > claim balance, then premium balance in premium balance and then the Total due should be the NET balance of claims-premium

I included a snippet.

Again thanks so much and let me see how this works

In your Statement Of Acct workflow - Formula Tool 96 you create a field called "Premium Due" and while I believe that you wanted to use this field to output the Premium Due on the Statement of Acct, you de-select the Premium Due field and use the Sum_Open Balance field.
- I did correct it in my workflow but am not sure if that was intentional or not
In your Statement of Acct workflow - You have two sections (Aviation Premium and Total Aviation Premium) that do the exact same thing and output to the same location.
- I did not include the Aviation Premium and built everything off of the Total Aviation Premium section that you have

Screenshot 2025-08-29 072111.png

chuckleswk

Hopefully it helps and works for you! Please make sure that you mark this as the Accepted Solution if it works so that it can help others.

rhutchison

This was perfect! Thank you so much!!

Quick Links

This months top contributors

SophiaF 432

AkimasaKajitani 390

mceleavey 388

mbarone 337

michael_weaver 335