Rename and Delete Sheet Name in Excel

[KamenRider]

Hi,

I have an excel attached which consist of two tabs. I would like to rename "Summary" into "Controller" and delete the "Data" sheet.

I hope someone there will show and help me to do this.

Thanks,

Kamen

[flying008]

Hi, @KamenRider 

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

[KamenRider]

Hi @flying008 

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

[flying008]

Hi, @KamenRider 

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

[KamenRider]

Hi @flying008 

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

[flying008 · Accepted solution]

Hi, @KamenRider 

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.

[Pravallika20]

Hi @KamenRider 

As the error suggests openpyxl moudule is not found. Get this installed and the code will work as expected.

[KamenRider]

Hi @flying008 

Thank you so much! Finally you got it. Thank you for your patience, support and assistance.

So glad we have the solution.

Kamen :-)