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]

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

[rhutchison]

Please see attached.

[chuckleswk · Accepted solution]

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.

[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

[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!!