Duplicate and Rename Excel Sheet, while preserving Formatting, using Alteryx

[calebhowellks]

Hello all - 

I have a scenario where I have an Excel template sheet, which contains some formulas, formatting, and specific sizing. Nothing too crazy. I need to fill out several hundred of these templates for a downstream process. For sake of argument today, let's assume that the formatting needs to remain unchanged. 

My initial plan was to write specific values to specific cells (ie, write out an employee name to cell A2 and preserving formatting) by using the new Excel output functionality included in the 2020.1+ releases. This worked great. 

The issue arises when I want to have each sheet in the Excel file dynamically renamed to a different employee name. I have dynamically written out sheet names before many times, but I have never had to do this with existing formatting on that sheet, meaning that I can't just create it out of nothing. Therefore, I need to:

1) Dynamically copy & rename sheets within an Excel file, while preserving formatting.

2) Output data to specific cells on specific sheets. 

Thanks in advance! Looking forward to seeing what you guys think I should do.  

[flying008]

Hi, @AkimasaKajitani 

You are a master !

[187]

@AkimasaKajitani 

Thank you buddy!

[KimLamNg]

@AkimasaKajitani I got this error when running your code: "Failed to find virtual env named: "designerbasedtools_venv"

Do you know how to fix it? I'm using Version: 2020.4.5.12471

[AkimasaKajitani]

@KimLamNg 

I think the problem is happening with older versions as follows.

https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Knowledge-Base/Error-Failed-to-find-virtua...

Please make a new workflow and paste that code to it in your designser and then run it. 

Note that don't copy the Python tool from older workflow.

[chanmills3]

When I use this I lose the conditional formatting from my original sheet. Is there a way to preserve conditional formatting? 

[chanmills3]

Is there a way to preserve conditional formatting from my source tab in Excel? 

[Elise_Prime]

I have been using this for a while but am now running into issues. I believe the errors I'm seeing are because of the most recent pandas version wanting to use concat instead of append. I'm not sure how to update that portion of the code and am wondering if someone here knows how that would be done? Thanks very much for any help here!

[juanigr]

Hi @AkimasaKajitani,

Thanks for sharing your solution. I find this thread helpful. 

I am also working on an almost similar case, but I need to copy and rename 2 specific sheets (red tabs) within the template as the name of the other sheets are static. I am also thinking of removing the old sheets once it is replaced with the new copied sheets and retain other tabs. For example: Q1 Summary and Q1 Detailed needs to be replaced with Q2 Summary and Q2 Detailed for the next cycle run. Is this also possible with python?

[AkimasaKajitani]

@juanigr 

I think you can do it based on this code https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Duplicate-and-Rename-Excel-She... .