Delete specific sheet in multiple sheets in excel

Help Studio
, ,
1

There are multiple sheet need to delete only specific sheets

sheets names eg: SMC -2022 , SSC - 2022 , SMC - 2021 , SSC- 2021 , SSC - 2020 and earlier , SMC - 2021 and earlier
I need to keep current year sheet like 2022 and earlier sheets of last year eg: SMC - 2021 and earlier

  1. I used excel scope → get worksheets
    2.initiated an empty list of string
    3.used for each for get worksheet → used if conditions to match
    4.used add to collection and added them to list
    5.used invoke code to delete them

Note : code working fine if pass values to list manually like new List(Of String)(new String(){})

but when i try to add to collection and use its not deleting .

Thanks in advance

1 Like
2

Hey @kavya.s16

Could you please show some screenshots…

#nK

3

Hi @kavya.s16

We can delete excel sheet using powershell script

4

@kavya.s16

Check below thread for alternatives to delete sheets

Hope this may help you

Thanks

1 Like
5

Hi @kavya.s16

If your organizations allows you to use third party package then go head with solution provided by @Srini84

or else

Save the below powershell script code into txt file

$ExcelObject = new-Object -comobject Excel.Application
$strPath1=“D:\Powershell\New Microsoft Excel Worksheet.xlsx”
$ActiveWorkbook = $ExcelObject.WorkBooks.Open($strPath1)
$deleteWorkSheet = $WorkBook.sheets.item(“your sheet name”)

#Deleting the worksheet
$deleteWorkSheet.Delete()

$ActiveWorkbook.Save()
$ActiveWorkbook.close($true)
$ExcelObject.quit()

Replace the highlighted text as per your document
pass the output of read text file activity into invoke power shell script

Thanks
Robin

6

@kavya.s16

In the thread given above you can see one third party tool and also VBA to invoke

So, If you can’t use Third party packages then you can go with VBA or as suggested by @Robinnavinraj_S you can go with Powershell scripting also

Hope this will help you

Thanks

1 Like