Hi,
I want to keep my formatting of my existing rendered excel. When I output i am getting error "You must specific sheet name".
However I have 8 sheets that I need to overwrite. How do I do this?
These are tables that i want to overwrite.
@wonka1234 you need to specify a sheet name in the output tool
@binu_acs i do that, but it is not preserving my existing file that was in there and updating it.
@wonka1234 Use specific range to write your output instead of the entire sheet at once,
@binu_acs Seems to work and preserve the format but things are not being overwritten with new data correct. Do you know why this would occur?
bump!
@wonka1234 When you write the output to a sheet , it actually replaces everything in the existing sheet including the formatting, but you if you use only the specific range to write into a sheet it will only replaces what mentioned in the range also it will keep the formatting
@binu_acs but i am writing to an existing sheet. and it doesnt keep the table formatting that was previously output there.
Please see attached workflow. Step 3 in my workflow will need new data to be refreshed in the tabs that should be colored.
The workflow you uploaded is wrong. you are not outputting a report snippet in your output data - you are outputting your original data source timed after your render in your output data. I would use a dummy field (and drop my report snippets) to control order after my render. see attached.
A report snippet (as generated by the table tool) is formatted html it is not compatable with an output data tool.
@apathetichell works nicely ! so my steps are
1) I use render, write my data to exce
2)then i manually go open excel and color the tab.
3) Then I disable that render tool (indefinetly) and will just write to that workbook using output data from now on.
But now getting issues -
1) Header isnt staying from original output - See photo
2) Range, how can I make the output data range dynamic?
You are writing over cell A1 (which includes the header cell). I'd recommend doing a row count. appending that to your datasource. using a formula tool to create a new field called something like filename the range (is filename|sheetname$range) where range starts on row2 (so you don't overwrite the header). you'll need to use a column count too if you don't want ot hardcode it. use a select tool to drop the extraneous fields except filename. edit your output data tool to change the entire path to filename.
@apathetichell thanks, looks to be working, but having trouble preserving that cool table look!
@apathetichell not sure if this can be done
Hello!@wonka1234 Untick "Skip file names" in the output tile or adjust the range.If you have skipped the file names, your range is shifted by one and the output was saved outside the table.
@mzak89 do you mean skip field names ? trying that and it still isnt ending up in the table format.
Please see attached.
skip field names is a check box in output data. it is not checked. because it is not checked you are printing your fieldname in column a2. you want to print data in a2. Check the box.
@wonka1234 Yes, I meant field, sorry, autocorrect I did some amendments to make it work:1. Enabled Table container2. Added paralel block until done, to make sure firstly the table is render, and secondly - values are overwritten.3. Amended ranges, to write each line as a separate range, using record ID instead of count4. Turning off AMP engine, to make sure parallel block until done is working properly.5. Added formula tool to add "amended prefix" to make sure the values rendered by table are overwritten by the output tileAmended workflow in the attachment.Make sure that the path used in render tool and path used in the output tool are the same, as zipping workflow to yxzp is changing the paths
@mzak89 thanks but my coloring of tab does not stay! Run once and color it, then run your flow again and its no color tab sheets!
@mzak89
All good - except - 3. Amended ranges, to write each line as a separate range, using record ID instead of count
not necessary.
@wonka1234 - are you sure tabtwo has color in the render tool? I dont see it in the workflow. I added coloring there - and I changed formula tool 27 to include a | to separate the sheet. after creating a file with green data - I had no issues. Are you saying it's header speciifc? If so - I'll try that.
@wonka1234 Sorry, I do not follow. What do you need to color and based on what?Please try the following:1. Run my workflow.2. Color the output.3. Disable the render tool before the second run.4. Run the workflow again
@wonka1234 - to clarify - if you change your underlying file - and then run container two - it will overwrite and preserve the formatting.
if you change your underlying file to have new formatting and run container one - it will overwrite the file and destroy the formatting.
This is expected behavior.
Also - I don't use the render tools much - ever - but when looking at your project I noticed column headings should be set in the Default Table Setting? you can change your column heading color there - vs in Excel.
@apathetichell I had some error like "data outside of defined range detected" (or something like this), that's why I've changed it. I'm aware that there was probably another way to fix it And yes, you can colour the file using some conditions defined in table tool instead of doing it manually, You can use row conditioning colouring based on columns conditions.
this works fine, however when new data is added to the output after step 3, i get whats in my photograph
1. Run my workflow.2. Color the output.3. Disable the render tool before the second run.
3.5 New data in text input tool is not formatted4. Run the workflow again
@wonka1234 see attached.
Can you remind me - what version of Alteryx are you running? If it's 2023.X downgrade - re-test.
@apathetichell
These are my steps -
4. Add new data to the text input5. Run the workflow again.
The tab color stays, but the newly added data from step 4 is not in the table format.
I am using version - Version: 2023.1.1.247 Patch: 2
@wonka1234 Since you add new data there, that expands outside original table, it won't be formatted.Rendered table will not expand once the new data comes in.The best approach here would be to color data directly with table tool, instead of doing it manually.What are your criteria for colouring the output when you do it manually?
Downgrade to version 2022.X.
Test my workflow. Adjust for your needs. I do not believe you can do what you are trying to do in 2023.X because "stuff." I think the way it handles render - possibly even when the tool is deactivated will not allow you to do what you want. My hunch is that not just your formatting - but your core data would change between runs because of how render handles files in 2023.X.
IE you:
1) create file in render.
2) edit different sheet manually.
3) go to run workflow with render disabled.
I believe you will find that your file the edited sheet will change - even if you do not use the disabled tool. Render in 2023.X is really for producing files - and does not really support re-editing them.
@mzak89 I want to color the tabs on a sheet. Dont think table tool can color the sheet tab itself.
background color in default table settings can (or should be able to) to set a tab color
Hey @wonka1234 - where are we here? did you test my workflow/did you downgrade? asking because - my workflow works in 2021.4...
