How to delete all sheets in excel but sheet1?

Help Studio
, , , ,
1

I have an excel with two sheets name “A” and “B”. The structure of the two sheets are same. How do I combine both data into one datatable, create “Sheet1” and write the datatable with the same header? And how to delete all sheets but “Sheet1”?

3

HI @arina

Try this

  • Use two Read range and read the two sheets “A” and “B” and save in the variable A_DT and B_DT
  • Use Merge Datatable
    Source : A_DT
    Destination: B_DT (Now you will have the combined DT in the A_DT)
  • Use Write Range and write the A_DT in the Sheet1
  • Next Use Get Workbook Sheets and store in the variable “SheetsList”.
  • Use For Each and give SheetList in the value field
    • Use If and give condition like Not item.ToString.contains(“Sheet1”)
      • Use Excel Process Scope(Modern Excel Activities you need to enable it from the filters in the activities panel Modern Design Experience)
        • Use Excel File and Give the path
          • You can find Delete Sheet activity in the modern activities
            - Give sheet name as Excel.Sheet(item.ToString)

Then it will delete all the sheets other Sheet1

Regards
Sudharsan

4

Hello @arina - Refer attached workflow

Example.zip (10.8 KB)

I have used VBA code to delete the excel sheets. You can change your excel sheet name accordingly in the code (text file)

Capture

Before you run the VBA, make sure you enable Trust Access to VBA project model in an empty excel. Ref below doc on how to enable it

5

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