Using Render tool to format multiple sheets in the same file

[User1]

Hello,

I built a workflow that creates rows of data, which I want to export to several separate excel files.
For this, I generated a Field "File Address" that contains the full path (FilePath) where I want to save my grouped data.

Important information : some of the FilePath drive to the same excel file, but with different sheet names.

I can easily generate my Excel files with the Output Tool, with workbooks and worksheets with the right names and in the right folders.

However, I would like to give a basic format to these recorded files:
• HEADER in bold, colored.
• If possible, but not mandatory, Columns with optimized widths

I tried many things, via the Table tool and Render Tool but without success. I still have this error: "Error: x64 Designer: x64 Designer: InboundNamedPipe :: ReadFile: Not enough bytes read. The communication channel has been closed. "

I think that this comes from the generation of different sheets at the same time, in the same file.

You will find attached my simplified data and workflow.

Is it possible to render different sheets in the same file with the render tool ?

Does someone have an idea to fix it ?

Thank you very much for your help.

[danrh · Accepted solution]

Take a look at the attached.

In the Table tool, group by the fields that contain the sheet names and the file names.  In the Layout tool, select "Each Group of Methods" as your Layout Mode, group by both the sheet and file names again, and set Orientation to "Vertical with Section Breaks".  If you want to re-name the tabs in the excel file, change Section Name to whatever field has the tab names.  In the Report Text tool, select that you want to "Attach text to existing field" and select the "Layout" field.  Insert/format fields and/or text to your liking in the text box.  In the Render tool, select to "Group Data Into Separate Reports" with a Separator of "Insert Section Breaks Between Records".

You'll need to play around until the formatting is how you need it, but this should get you pretty close.

Good luck!

[User1]

Thank you ! It worked !

Best regards,

[kvr]

Hi,

I have a multiple tab with different fields in a excel where I have to format each an every tab separately. can some one help me how to achieve this?

[Fz]

Hello @kvr,

did you find solution to your  request? I need to do the same. Thank you

[kvr]

Hi Fz,

I have managed to achieve my request using table, formula and Render tool, I have attached the sample workflow for your reference.

There will be a demerit in render tool to achieve these kind of requests.

We cannot insert new tab into the existing excel output using render tool for that we have to use Layout, but while we use render tool it takes standard width for all the tables in all tabs.

For eg., Lets consider I have a three tab called A, B and C. A tab has 2 columns, B tab has 3 columns and C tab is summary table (kind of pivot). I am fixing total table width for table A is 4inch, B is 6inch and C is 3 inch, but it seems like the render output takes its paper size and realigning table fields so the width for all the tables will be same in all the tabs.

You can format all the columns as per your requirement only concern here is total table width.

This is based on my experience with render tool, please advice otherwise.

[papalow]

@danrh

Thanks for posting this solution.  It has given me an idea on how to solve a reporting problem that requires multiple spreadsheets in the same file.  

papalow

[chandeepsharma]

This is a great solution ! :)

[tajinder]

Hi ,

In the below example when we add "Report Text Tool"  it create 2 rows one with heading (c in our below example) and one blank row(highlighted with yellow) , so is there any way to remove blank row.

[JoeBarndollar]

Very useful solution.

Thanks!