Scenario: I have an Excel Worksheet containing two sheets named as Sheet-1, Sheet-2. I want to remove Sheet-2.
This process can be automated using DeleteWorkSheet component.
The following steps would help you:
1. Go to Component ToolBox panel, extract the following components to the Designer Surface.
a. Click Icon Connectors.
b. From the Connectors category drop-down menu, drag Microsoft Excel component.
c. Click Icon General.
d. From General category drop-down menu, drag MessageBox component.
2. Go to Activity Explorer panel and click Activities drop-down menu, click Main and then double-click MicrosoftExcel_1.
3. An Object Explorer panel should appear down the Component ToolBox.
4. On Object Explorer pane, Click Properties drop-down menu and drag FilePath component onto the Designer Surface.
5. Double-click Filepath data-In-port property and enter path/ location of the excel file.
6. Go to Object Explorer panel. click Methods drop-down menu and drag Open and DeleteWorkSheet component onto the Designer Surface.
7. Double-click Sheet property and enter the sheet name.
8. Right-click SheetBy property and click Override.
9. A Port Properties dialog box should appear.
10.Click Static and select Name/Index from the static value drop-down list.
11. Click OK button.
12. Connect the Control In/Out ports between the components right from Start to End components.
13. Connect the data ports of Microsoft Exel-1 and MessageBox components.
14. Press F5 key to run the activity.
The result:
Initially there are two worksheets. After Automation, there is only one worksheet displayed.