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 

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

[KamenRider]

Hi @flying008 

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

[flying008]

Hi, @KamenRider 

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.

[KamenRider]

Hi @flying008 

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

[flying008]

Hi, @KamenRider 

1-  Save the below code as abc.bat to your path like D:\123\abc.bat . (Assuming that your Rename and Delete.xlsx file is located in the path D:\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 :

3- Click OK then Run.

[KamenRider]

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

[flying008]

Hi, @KamenRider 

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.

[KamenRider]

Hi @flying008 

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.

 

 

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

Please advise.

Thanks,

Kamen

 

[flying008]

Hi, @KamenRider 

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.

[KamenRider]

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