Rename and Delete Sheet Name in Excel

So, please change your code to below and try again:

@echo off
setlocal enabledelayedexpansion

set "file=C:\Users\Asus_user\Downloads\Powershell\Rename and Delete.xlsx"

if not exist "%file%" (
    echo File does not exist.
    exit /b
)

@echo Rename and Remove sheet from %file%...

powershell -Command "$excel = New-Object -ComObject Excel.Application; $excel.displayalerts = $False; $sht2del = 'Detail'; $workbook = $excel.Workbooks.Open('%file%'); $worksheet = $workbook.Worksheets.Item('Summary'); $worksheet.Name = 'Controller'; $worksheet = $workbook.sheets.Item($sht2del); $worksheet.Delete();$workbook.Save(); $workbook.Close(); $excel.Quit()"

@echo All Done.

All comments

Anyone please...

nagakavyasri

You can use 'Overwrite file(Remove)' option to replace the file as deleting a sheet is not possible.

FYI.

Hi @flying008 @nagakavyasri

Thanks for the response. While your ideas work, I forgot to include in the file the condition that it should retain or preserve its formatting. I have once again save a sample file with format.

Also, I notice a quotation on the tab name. Can we remove it? Can you state your formula here again, i noticed something in the formula which I cant get.

Hope to hear from you guys and from anyone who would like to share their ideas. I need to solve this case for the project.

Thanks

Kamen

Rename and Delete.xlsx

Swathi

Can you try clicking Preserve formatting on Overwrite in the output tool

1- You didn't read my post carefully.

Replace([FileName], '`Summary$`', 'Controller')

2- If you want to retain all format of sheet, maybe you need use python tool to save the case, like below code:

import openpyxl
import os

# Defind file path
ABC = "D:\abc\Rename and Delete.xlsx"

# Open Excel File
workbook = openpyxl.load_workbook(ABC)

# delete Sheet 
if "Data" in workbook.sheetnames:
    workbook.remove(workbook["Data"])

# Rename Sheet
if "Summary" in workbook.sheetnames:
    worksheet = workbook["Summary"]
    worksheet.title = "Controller"

# Save File
workbook.save(ABC)

# Close File
workbook.close()

Hi @Swathi Yes I did but nothing happen. It did not retain the format.

Kamen

Thank you for writing the formula since I get confuse with the punctuation you use with "Summary". Having it copy - paste fix the problem.

In line to the python code, can you help me how I am going to enter it in Alteryx? I still building my knowledge with Alteryx and especially not a coder expert.

I do hope this will solve the problem. Looking forward for your response and assistance.

Thanks,

Kamen

Hi @Swathi @nagakavyasri @flying008

Have used the Python tool using the code provided. Having a hunch of using it which I hope I am doing it right. See below errors I've got. I hope you could help me debug the error since I have zero knowledge in using python.

Thanks

Kamen

You need already installed the module for python env, if not, must add the below code to the first line.

Package.installPackages(['openpyxl'])
import openpyxl
import os

Am I correct to insert the package in the phrase below?

Found errors when I run the workflow. Kindly check and let us know to fix the issue.

Please advise.

Kamen

There is maybe have many cause for error, like your network or multiple python version or havn't install right.

1- Run Alteryx as Admin right.

2- In python tool , try again.

3- If fail, please contact your IT.

Thanks for the advise, unfortunately it is not easy for us to request access like that. There will be a lot of waiting and processes. Is there any other way you could share with us?

We appreciate for your patience in helping us. Looking forward for your response.

PS. Others with great ideas and expertise please help.

Thanks,

Kamen

1- Save the below code as abc.bat to your path like \123\abc.bat . (Assuming that your Rename and Delete.xlsx file is located in the path \123\)

@echo off
setlocal enabledelayedexpansion

set "file=D:\123\Rename and Delete.xlsx"

if not exist "%file%" (
    echo File does not exist.
    exit /b
)

echo Rename and Remove sheet from %file%...

powershell -Command "$excel = New-Object -ComObject Excel.Application; $workbook = $excel.Workbooks.Open('%file%'); $worksheet = $workbook.Worksheets.Item('Summary'); $worksheet.Name = 'Controller'; $worksheet = $workbook.sheets.Item('Data'); $worksheet.Delete(); $workbook.Save(); $workbook.Close(); $excel.Quit();"

echo All Done.

2- Use the Run Command tool or event to set :

录制_2024_01_30_11_46_32_670.gif

3- Click OK then Run.

HI @flying008

Apology for a late reply since there are a lot of things in my plate to complete. Meanwhile, I've tried your sample and I got an error. I've check the file and it doesn't remove the "Data" tab.

Can you please update the batch file, skip the renaming. Just command it to delete the data sheet. Also what if the path of the file is in the network drive, how can I write it in your batch file code?

Looking forward for your response.

Thanks,

Kamen

1- Post your error by you run time.

2- All function are fine in my location, so if your file in the network drive, please replace the code set "file=D:\123\Rename and Delete.xlsx" by your UNC path.

I manage to remove the error however my only problem is that it does not delete the other sheet tab. Kindly below and let me know what I missed.

Delete not working.PNG

bat file.PNG

This one shows nothing happen. It does not delete the "Detail" Tab

Please advise.

Thanks,

Kamen

1- Your worksheet name must be identical to the code $worksheet = $workbook.sheets.Item('Detail'); Please note that there should be no spaces before or after "Detail".

2- Check above and try again.

HI @flying008

Sorry, but what do you man worksheet should be identical? in what part? I only have two sheets namely "Summary" and "Detail". The summary tab will be rename with "Controller PnL" which correctly done. My only problem is that it is not removing or deleting the "Detail" tab. I've check the code and there are no spaces before and after the "Detail".

Kindly elaborate.

Thanks,

Kamen

Please replace $workbook.sheets.Item('Detail') to $worksheet = $workbook.Worksheets.Item('Detail') , and try again.

It is still not working. The "Detail" sheet name is still there in the file. I can't figure out why?

Please advise.

Thanks,

Kamen

Jesus! The code get all want in my pc, so please try the code not in UNC path first.

Thanks for the patience. I used the C drive to save and create a batch file and still the deletion is not working. I cannot figure out why it is working for you and why to me is not. It keeps me wondering.

Attaching sample file and the code you may use to validate.

Thanks and looking forward your reply on this.

Kamen

Rename and Delete.xlsx

code.docx

So, please change your code to below and try again:

@echo off
setlocal enabledelayedexpansion

set "file=C:\Users\Asus_user\Downloads\Powershell\Rename and Delete.xlsx"

if not exist "%file%" (
    echo File does not exist.
    exit /b
)

@echo Rename and Remove sheet from %file%...

powershell -Command "$excel = New-Object -ComObject Excel.Application; $excel.displayalerts = $False; $sht2del = 'Detail'; $workbook = $excel.Workbooks.Open('%file%'); $worksheet = $workbook.Worksheets.Item('Summary'); $worksheet.Name = 'Controller'; $worksheet = $workbook.sheets.Item($sht2del); $worksheet.Delete();$workbook.Save(); $workbook.Close(); $excel.Quit()"

@echo All Done.