Renaming Tabs in multiple Excel Workbooks

[Ornelasya]

Hello, 

Is there a way to rename tabs in multiple workbooks if the existing tab name and the desired new tab name are provided? These would be workbooks with multiple other tabs that would have to remain unchanged.

Thank you for your help.

[DataNath]

Hey @Ornelasya you could make the filepath using a Formula tool like so:

Which you could then pass into your Output Data tool like so:

Making sure you have 'Overwrite Sheet or Range' selected in Output Options.

I can see from your screenshot that it looks like you're effectively shifting sheets down when another month comes in, so this approach will do that an replace the 'Prior month' to what was 'Current month'. Any sheets where you just leave them as is and don't put their name next to another sheet to replace will remain unchanged and won't be overwritten.

[Qiu]

@Ornelasya 
I believe we can do this by Run Command tool with a pre-defined Excel VBA function. 😀

[Ornelasya]

Thank you DataNath. 

This is solution seems to only create a new tab with the text input data for that file name as the output. I would like the contents of the "Current" tab to be the same when it is renamed as the "Prior" tab.

[Ornelasya]

Thank you @Qiu !

Would you happen to have an example or resource you could point me to for this? Apologies, I am unfamiliar with a pre-defined Excel VBA function.

[Qiu]

@Ornelasya 
Myself is not an expert on VBA as well. 😁
I will dig a bit more when I have time on the weekend and hope someone else can give a big help also.

[flying008]

Hi, @Ornelasya 

Maybe you can use python tool to rename sheet-name from the input data like below code:

import openpyxl
wb=openpyxl.load_workbook("excelfile.xlsx")
#printing the sheet names
wb_sheet = wb['old_SheetName']
wb_sheet.title = 'New_SheetName'
wb.save("excelfile.xlsx")

[Ornelasya]

Thank you @flying008 

I think python is my best bet. Would you know how I would need to modify your code so that Alteryx dynamically updates all workbooks in a given list rather than just a single file at a time? I've attached a sample workflow and some dummy files for reference.

Thank you!

[flying008]

Hi,@Ornelasya 

You need use for *** in df of code syntax to loop your excel sheet name  in python tool, I'm not a coder, just have poor ideas. 

Maybe other guys can fulfill all code for you.  

[Ornelasya]

Thank you @flying008. 

Yes, I'll repost this as a python coding question and see if I get any luck. I appreciate your help/time! :)