Outputting to a formatted excel template

[tsch]

Hi All,

I am new to Alteryx so bare with me! I have no issues getting my data to a raw export (test dummy data attached below). The problem I am having after reading multiple threads is taking this data and outputting this data to a standard excel template by a specific field (in this case Broker #).  Ideal final state would be taking the raw file breaking it into separate sheets by broker number and applying the excel template that has already been built in Excel.

Is this possible? I've used the summation tool within Alteryx so the underlying data by Broker # does not appear to be an issue just more of getting the template to apply by each sheet.

Any help is greatly appreciated!

[AbhilashR · Accepted solution]

@tsch - I have created a sample solution that creates multiple copies of your original template and pastes corresponding broker data into each template based on their NEW MBI id. The output tool is writing to a named range in excel I created called 'brokerOutput'

You could build on this foundation and write another batch script to merge back these individual templates if that is what you are looking for.

Would this get your closer to what you are looking to achieve?

You could also choose to use the Render tools and recreate the template file (with corresponding data) in Alteryx, but you may not be able to achieve your existing excel formatting as is. I chose my approach to only make it easy on myself to come up with something. 

Hope this helps.

[tsch]

@AbhilashR This appears to be exactly what I am going after. Unfortunately, when I try to run the script I keep receiving the error message Error: Run Command (31): The external program "%temp%\script.bat" returned an error code: 1. What do I need to do on my end to avoid this? I am really new to this so appreciate your insight.

Thanks!

[AbhilashR · Accepted solution]

@tsch - can you confirm you have a .xls file in the source folder (Inputfile location in my example)? I am able to replicate your error only when I run the workflow without any file in the source folder.

Also maybe modify the config in the run tool to something like this and try? not sure that will solve the issue though.

[tsch]

@AbhilashR this appears to have fixed it. Thanks so much. I made one tweak to the workflow of rather than generating files by MBI rather by the Broker Invoice # (result of 4 files). When doing that now my predefined Excel template won't populate rather it will just extract the results as raw data.

Thoughts?

Thanks so much for your help.

[AbhilashR · Accepted solution]

@tsch - my output tool writes to a named range in excel, which was only two rows when I first built it. I have now modified it to include few more and it seems to be working. You can modify the range I have set within excel by going to Formulas>>Name Manager>>search for brokerOutput in my case>>Edit>>modify the range as you see fit.

I have attached the modified version for your easy reference. Take a look and let me know if this works.

Hope it makes sense and works for you.

[LoriDMiller]

This was a great workflow to create separate files - how would I modify it to create multiple TABS in one file?

COPY /y ".\template\Template.xls|||Sheet1$" ".\templateCopies\2020-05-01.xls|||123456$

I tried to change the COPY in the Script to use the Tab names but that did not work.

My goal is to generate an output file from a Single Template with One Tab that is formatted and Save that with the specific Countries as Tab names that I need to save. 

Or is I need to do a Different Batch Macro

Thank you

[timesquita]

Hi @LoriDMiller ,

Hope you are doing fine.

Did you get any solution to this case that you mentioned below?

I'm really interesting in this kind of manipulation on tabs from excel.

Appreciate your answer!

Many thanks

Tiago