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

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?

Input

Reporting

Accepted answers

apathetichell

Use a render tool. You will need to output to a new file. Render does not overwrite or append data to existing files. It only deletes/creates new files.

All comments

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-sheet-in-Alteryx/td-p/781015

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-another-Excel-File/td-p/1245848/page/4

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.

dandreas

@Qiu @apathetichell

I tried using the Reporting tool called Basic Table, and created column rules which did indeed grey the data per review of the Browse tool. However, the Output tool does not output this dataset with the grey color and instead outputs 1 row of data with a bunch of strange string information. What Reporting tool should I use if not Basic Table or how can I correctly output the greyed data? Please see attached screenshots.

Screenshot1.png

Screenshot2.png

apathetichell

Use a render tool. You will need to output to a new file. Render does not overwrite or append data to existing files. It only deletes/creates new files.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15880

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703