Render multiple tabs to multiple Excel files

[rileyspo42]

Hey all,

So after reviewing the below thread on setting up the Render tool to output multiple tabs to a single Excel file, I was able to get my foot in the door with my goal, but I'm not quite there yet. (https://community.alteryx.com/t5/Engine-Works/How-to-Render-to-Multiple-Named-Excel-Sheets/ba-p/1015...)

My goal is to use the Render tool to not only output separate tabs, but separate tabs to separate Excel files. Right now I'm testing with two files that are pulled in with the Directory tool, the full path is appended with 3-letter abbreviations for all of the months of the year, and then the goal is to populate the tabs of each file with the rendered tables (all 24 tabs already exist in both of the Excel files). I've updated my Render tool using the template in the above thread to "Group Data Into Separate Fields" and using the appended file location as "The Field to Group On." I'm bumping in to "The file [file name] is not a supported format." error and there is no output.

I've had great success with the data output tool accomplishing this goal, but for reporting purposes, I'd like a little more customization. 

I've been unable to track down any solution through what's currently out there in the community, so hopefully this thread can get the ball rolling! Attached are screenshots for the reporting tools configs. Any help is greatly appreciated!

[apathetichell · Accepted solution]

See if you can follow this logic:

1) table tool - you need to capture file path/name and sheet name as key columns.

2) layout tool - you need to group on both filename -> and sheet name - sheet name becomesyour section break.

3) render tool -> you need your filepath/name to what you are sending in to change entire path.

4) the file path sent in to render should end in xlsx. your sheetname is considered in your layout tool.

as currently set up - you are sending your full path in as both the sheet name and the filename. That's yucky. Even if it worked you don't want c:\mylocation\myfile||mysheet as your tab name.

[rileyspo42]

Okay we are moving in the right direction here, thank you for your reply.

The issue I'm running into now is the Render tool deleted my other tabs, is it possible to only overwrite the specified sheets or does the render tool overwrite the entire file every time?

[atcodedog05]

Hi @rileyspo42 ,

Render tool by default overwrites the whole file and deletes other existing sheets. Sadly :(

[rileyspo42]

That is so unfortunate. I'm trying to automate some monthly reports that have a summary tab with sumif formulas in Excel, would've liked to have the monthly data presented cleaner, but I guess the output tool is my only option.

Thank you both for your help!

[atcodedog05]

Hi @rileyspo42 

If thats the case. Here is another option you can explore. This generally what i use. Explore preserve output formatting in output tool.

Solved: Excel output to formatted sheet - Alteryx Community

Idea is to write data to an already formatted sheet. This was you are not using render tool and none of the sheets gets deleted.

Hope this helps :)

[atcodedog05]

Its a life saver if it works trust me on that 😁

[rileyspo42]

I gave that a try and I'm getting a corruption notification when I open the workbook and all of my data are on recovered tabs. I used a pretty large range (these reports require a lot of data) of A1:AG20000

[atcodedog05]

Hi @rileyspo42,

It but of a trail and error method. Trying formatting a fresh new sheet and outputting to that.

[rileyspo42]

Gotcha, I'll play with it. I appreciate the recommendation!