Count of Sheets in Excel File and Copy sheet from one excel file to new excel file

Help Studio
, ,
1

Hello,

I need to check the count of the sheets in an excel file and if I have more than one sheet in the excel I need to create new excel file for each sheet in the original excel file.

I have used the activities - Get Workbook Sheets and Copy Sheets, these activities work fine, but we do not want to use Classic Activities. Please let me know how these can be done with Modern activities or what can be other approach to achieve this.

Thank you,
Shreya

2

you can utilize modern activities and custom code. Here’s a step-by-step approach:

1.Use Excel Application Scope (Modern Activity):
Start by using the “Excel Application Scope” activity to work with Excel files in a modern way. Make sure to provide the file path.
Read Sheets Information:

2.Inside the “Excel Application Scope,” use the “Get Workbook Sheets” activity to get the list of sheet names in the Excel file. This will give you a List of strings containing the sheet names.

3.Iterate Over Sheet Names:

  • Use a For Each loop to iterate over the list of sheet names obtained in the previous step.
  1. Create a New Excel File for Each Sheet:
  • Within the loop, for each sheet name, create a new Excel file using the “Excel Application Scope” again.
  • Use the “Add Data Row” activity to filter data for the current sheet and write it to the new Excel file.

@Shreya_Gandhi

3

Hi Praveen,

Thank you so much for the quick response.

We have currently used - Get Workbook Sheets activity already but we do not want to use it as it comes under Classic Activities. I am looking for some other activity/approach where I can avoid using Classic activities.

Shreya

4

How about using ClosedXML as the following?

image
image863×557 24.4 KB

Using wb As New ClosedXML.Excel.XLWorkbook(filepath)
    numOfSheet = wb.Worksheets.Count
End Using

Sample
Sample20230927-2L.zip (8.8 KB)

Regards,

5

@Shreya_Gandhi

hi
no need to use get workbook sheet activity

use
Excel Application Scope

create output for excel application scope and use assign activity or log message

count=output.getsheets.count

cheers