How to delete excel sheet dynamically

Help Activities
, ,
1

Hi,

I need to delete excel sheets dynamically means I need to pass the sheet name to delete.
Delete sheet - not working
used vba code - i think mcro blocked

is any other way ??

TIA

2
  1. Install Excel Activities:First, ensure that you have the “Excel” package installed in UiPath. You can do this by going to the “Manage Packages” option in UiPath Studio and searching for the “Excel” package. Install it if it’s not already.
  2. Use Excel Application Scope:
  • Drag and drop an “Excel Application Scope” activity into your workflow. This activity allows you to work with Excel files.
  1. Open the Excel File:
  • In the “Excel Application Scope” activity, specify the path to the Excel file you want to work with.
  1. Use Invoke VBA:
  • Inside the “Excel Application Scope,” you can use the “Invoke VBA” activity to run VBA code to delete the sheet dynamically.
  • Create a VBA macro to delete the specific sheet. In the “Invoke VBA” activity, set the VBA code to run the macro. Here’s an example of VBA code to delete a sheet by name:

vbaCopy code

Sub DeleteSheetByName(sheetName As String)
    On Error Resume Next
    ThisWorkbook.Sheets(sheetName).Delete
End Sub
  • In the “Invoke VBA” activity, pass the sheet name you want to delete as an argument to the macro.
  1. Save and Close:
  • After running the VBA code, save and close the Excel file using standard UiPath Excel activities.
  1. Complete the Workflow:
  • Add any additional steps or error handling as needed for your specific use case.

Please check this post.

3

Thanks for reply @copy_writes
As i already mentioned i used invoke VBA but macros are blocked I am unable to use VBA hence looking for other solution.

4

Delete sheet activity also not working?

5

If macros are blocked, and the “Delete Sheet” activity in UiPath is not working for your specific use case, you can use the following alternative approach to delete an Excel sheet:

  1. Use PowerShell Script:

    You can leverage PowerShell to interact with Excel and delete specific sheets. Here’s a step-by-step guide:

    a. Use an “Invoke PowerShell” activity in UiPath to run a PowerShell script.

    b. In the PowerShell script, use the Remove-Item cmdlet to delete the sheet file. You need to specify the Excel file’s path and the name of the sheet to delete. Here’s an example PowerShell script:

    $excelFilePath = "C:\Path\to\YourExcelFile.xlsx"
$sheetNameToDelete = "SheetName"

$excel = New-Object -ComObject Excel.Application
$workbook = $excel.Workbooks.Open($excelFilePath)

# Find and delete the sheet
foreach ($worksheet in $workbook.Sheets) {
    if ($worksheet.Name -eq $sheetNameToDelete) {
        $worksheet.Delete()
        break
    }
}

$workbook.Save()
$workbook.Close()
$excel.Quit()

[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)

    Make sure to replace the paths and sheet name with your specific details.

    c. In the UiPath “Invoke PowerShell” activity, pass the PowerShell script as an argument.

This approach uses PowerShell to manipulate the Excel file and delete the specific sheet without relying on macros or the “Delete Sheet” activity in UiPath. Ensure that your system has PowerShell enabled, and you have the necessary permissions to modify the Excel file.

6

@copy_writes
delete sheet gives error when I am giving excel sheet name there

error

option strict on disallows implicit conversions from ‘string’ to ‘UiPath.Excel.ISheetRef’

7

here , how to pass sheet name dynamically ?

8

Hi @8b6861dc5e0c9f7008548ca66

Can you try this expression Delete sheet activity Excel.Sheet(StrSheetName)

You can pass the your sheet name in string variable StrSheetName = “Sheet1”

image
image732×707 22.6 KB

Refer the screenshot

Regards
Gowtham.K

1 Like
9

The error you’re encountering, “Option Strict On disallows implicit conversions from ‘string’ to ‘UiPath.Excel.ISheetRef’,” is because the “Delete Sheet” activity in UiPath expects an object of type ISheetRef , not a simple string containing the sheet name. This error is related to strong typing and data type compatibility in UiPath.

10

@Gowtham_krishnan
Thanks for ur reply !

I am using classic activities so, I am not getting reference as option and.
If I used above expression , getting error Excel is not declare

11

@8b6861dc5e0c9f7008548ca66 share workflow screenshot

You can use modern activity

12

@Gowtham_krishnan
Apologies!

It’s working :slight_smile: Need to used within excel process scope and then Use excel file . I was using Excel app scope .

Thanks a lot !!

1 Like
13

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.