Sometimes we might want to delete unwanted sheets from an excel file.
We can do it through different means .i.e VB.net code, Powershell Script, Python or 3rd party Uipath activities…
Let’s see how to do it using PowerShell script for now 🙂
Example
Implementation using UiPath :
Let us implement a workflow to delete unwanted sheets from an excel file.
Sample Excel File with 3 sheets in it:
PowerShell Script(File has been attached in the source code)
Param
(
[string]$FilePath,
[String[]]$SheetNames
)# Create an Object Excel.Application using Com interface
$objExcel = New-Object -ComObject Excel.Application
$objExcel.displayalerts = $false# Disable the ‘visible’ property so the document won’t open in excel
$objExcel.Visible = $false# Open the Excel file and save it in $WorkBook
$WorkBook = $objExcel.Workbooks.Open($FilePath)
#Delete required sheets by looping
[String]$FailedSheets=””
foreach ($SheetName in $SheetNames)
{
try
{
$workbook.worksheets.item($SheetName).Delete()
}
catch
{
$FailedSheets=$FailedSheets,$SheetName
}
}if($FailedSheets -ne “”)
{Write-Error $FailedSheets” doesn’t exists”
}$workbook.Save()
$objExcel.Quit()
Step 1:
Drag “Invoke Power Shell” activity into the designer panel and supply the required fields as shown below along with the input arguments.
Arguments:
FileName (String)
SheetNames (Array of Strings)
Step 2:
Finally, execute the workflow to see the results:)
(Note: Sheet2 and Sheet3 are no longer available)
Click here to download the Source Code…
Hope it has helped you…
