Using Blob input and blob output tool and populate data into excel file

[ashamluo]

Hello Alteryx Community,

I am trying to use a excel template file with summation formulas in it and populate the Alteryx results with this template. So I could avoid errors when the data is altered. 

For example, I have three data rows and if I wanted to make changes and add a fourth row into the output, so then excel would help me add up the correct numbers. I know that blob input and blob output could help me. However, I keep having errors in my final output. Even though the template was read in, but when open the final excel output it says that data needs to be recover. After recovering the summation, formula is taken out. 

Book1.xlsx is the final result I would like to have.

Any idea why am I having this error? 

Thank you!

[SPetrie · Accepted solution]

Using the blob for the template file is a good idea, but I believe the issue you are having is with the excel file itself. 

Overriding a sheet and keeping the formatting requires you to give it a range to work with when saving and I believe not giving it the range is what is causing the issue.

You also cant override existing formulas when updating a range, so thats something else we need to account for.

Its a bit annoying, but the way I get around it is to split my flow and have an output for each range I want data to go to.

I edited your flow to use this concept.

 Since you mentioned wanting to use the excel formulas, I removed the total formulas from the workflow. I also extended the formulas down in the template excel file.

I use a formula tool to assign the file output name and the range I want the selected columns to go to

Setup the two (or however many you eventually need) output tools and you should be ready

The output data goes to the ranges we specified and since the total columns were not in the range, the formulas are still intact and calculate the totals for us.

[ashamluo]

Thank you for the explanation, now I know what went wrong! 

Is there a way to have the range in a dynamic way? The rows of data I am having changes when I used the workflow, is there a way to make it more dynamic? 

[SPetrie]

You are welcome.

As far as dynamically updating the range, the answer is yes. I used a static range, but you can always use formulas or other info to generate the range you are putting into the output file name.