How To Delete Sheets From An Excel File Programmatically – In UiPath

carlos-muza-hpjSkU2UYSU-unsplash.jpg

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:
UiPath Delete sheet from excel 3.PNG

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)

UiPath Delete sheet from excel 1UiPath Delete sheet from excel 2

Step 2:
Finally, execute the workflow to see the results:)
(Note: Sheet2 and Sheet3 are no longer available)

UiPath Delete sheet from excel 4.PNG

Click hereΒ to download the Source Code…

Hope it has helped you…

SignUp To Get Latest Articles

* indicates required

Leave a Reply

%d bloggers like this: