Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Outputting to a formatted excel template

tsch
5 - Atom

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!

7 REPLIES 7
AbhilashR
15 - Aurora
15 - Aurora

@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
5 - Atom

@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
15 - Aurora
15 - Aurora

@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.

Capture.PNG

 

 

 

tsch
5 - Atom

@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
15 - Aurora
15 - Aurora

@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.

 

Capture.PNG

 

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
6 - Meteoroid

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
6 - Meteoroid

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

Labels