Creating a Copy of Each Tab in Excel & Adding the Word "Copy" to the New Tab Name

[dandreas]

Hello,

Seems simple but having trouble with creating a dynamic solution that basically replicates right clicking on every tab in an uploaded excel file and creating a copy of each tab and adding the word "Copy" to the end of the newly created tab copy names. It has to be dynamic enough in that the file names and tab names will change so it has to be able to capture any file name or tab name and perform the same steps. 

Also, after that, I would love to have it fill in or highlight in grey (RGB 211, 211, 211) every cell with data in it, whether it's numbers or letters, on the newly created copy tabs. If no data, then leave as is. That's it. Filling in or highlighting grey the entire range of columns and rows from the top of the dataset to the bottom of the dataset with data in them works too (Anything below the data or to the right of the data would be left as is).

Thoughts?

[Qiu]

@dandreas 
The two targets are actually not possible at the same time.

We can color the cell with Reporting tools but we have to use the Row or Column Rules in the Reporting tools, meaning it is not "Copy" anymore.
We will have to read in the data, losing all the formatting in the original Excel file.
refer to reply below for simply Copying with RunCmd tool.

https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Make-copies-of-Excel-template-...

[apathetichell]

@dandreas I posted a macro to github which copies formatted sheets to new sheets - so if you are just copying a formatted sheet to a new sheet - you can use that.

As @Qiu pointed out - if your goal is to copy a sheet - and use specific formatting - you should be using VBA or (maybe) Python. Not Alteryx.

[Qiu]

@apathetichell 
Maybe you share the link of your macro? 😁

[flying008]

Hi, @dandreas @Qiu 

Maybe the below post can help you get some tips, and IMHO, the operator of fill color in cell or sheet name all will done in Power-Shell too,  

So you can try it yourself.

https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Batch-File-to-Copy-Sheet-to-an... 

[apathetichell]

Of course - I posted a topic on it --- https://github.com/apathetichell/2024_AlteryxMacros

There's also a csv sniffer which detects csv delimiters and a macro for extracting list of files in a zipfile. I have a script code for an SFTP python tool (via ssh/Private Key) but not sure if the best use case is a) read files 2) download files 3) upload files - or some combo - and I don't have a specific Alteryx use case for it right now.

[apathetichell]

@flying008 - 100% - great solution.

[Qiu]

@apathetichell 　@flying008 
This topic has turned to be a very deep discussion.
I am sure I am going to learn a lot from this. Need some time to digest 😁

[dandreas]

@apathetichell Thanks! I initially used and completed Excel Macros but they don't work on larger file sizes (Tens of thousands of rows). I thought macros would be able to handle this but apparently not - takes 20 minutes to run which is inefficient and we also got many pop-up errors saying there's not enough resources. I looked online and others ran into the same resources issue with macros - which is why I turned to Alteryx which can handle larger file sizes.

[dandreas]

@flying008  Thanks! Never used Power-Shell and not sure I can get access, so prob not an option right now.