Deleting first 3 rows in excel from multiple worksheets located in a specific folder using Power Automate

[Bros]

Hi there,

Is there a way to delete the first 3 rows in excel from multiple worksheets located in a specific folder using Power Automate? 

If what is the flow? I tried a few options without any success. Grrr

[VJR]

Hi @Bros 

Make changes as necessary in the below Pseudo logic.

- Launch Excel

- Get all Excel Worksheets (this will give SheetNames variable)

- Use a For Each loop on the SheetNames (this will give CurrentItem)

- Set active Excel worksheet (to set each of the CurrentItem sheets in the loop as the active sheet)

• Loop from 1 to 3   (to delete each of the rows from 1 to 3)

- Delete row from Excel worksheet  (Pass row number as 1)

• End of loop from 1 to 3

- End of For Each Loop 

The Delete row action only deletes 1 row at a time. So you will have to loop 3 times to delete each row.

Other options you could try are selecting all rows using Sendkeys and then pressing the Delete key.

[Bros]

Hi VJR.

Can't seem to get to work. The files are located in a folder called Days Consulted. See below (+-219 excel files)

So I want to delete the top 3 rows in each of these files. I am still trying to see if I can use your suggested logic. But was hoping you could assist further.

Thank you 

[VJR · Accepted solution]

Okay, so here is the confusion. The post said Worksheets so I suggested a logic related to sheets for 1 file and the same to be repeated for other files in the folder.

From your screenshot it is clear that it is Workbooks. 

You just need to iterate through the files in folder before running the sheets logic above.

Here is the modified logic.

- Copy Paste the below code in a blank Flow Editor and Test the Flow.

- Make sure to run it against a copy of the Excel files and not the original files.

- Change the path in the code

- Refer to the comments in the code

SET InputFolder TO $'''C:\\Test\\MyFolder'''
Folder.GetFiles Folder: InputFolder FileFilter: $'''*.xlsx''' IncludeSubfolders: False FailOnAccessDenied: True SortBy1: Folder.SortBy.NoSort SortDescending1: False SortBy2: Folder.SortBy.NoSort SortDescending2: False SortBy3: Folder.SortBy.NoSort SortDescending3: False Files=> Files
LOOP FOREACH CurrentItem IN Files
    Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: CurrentItem Visible: True ReadOnly: False Instance=> ExcelInstance
    Excel.SetActiveWorksheet.ActivateWorksheetByName Instance: ExcelInstance Name: $'''Sheet1'''
    # Set the correct Sheet name here from where you want to delete the rows.
    SET RowCounter TO 1
    LOOP WHILE (RowCounter) <= (3)
        Excel.DeleteRow Instance: ExcelInstance Index: 1
        Variables.IncreaseVariable Value: RowCounter IncrementValue: 1
    END
    Excel.CloseExcel.CloseAndSave Instance: ExcelInstance
END

[VJR · Accepted solution]

 

[Bros]

Thank you VJR, this worked like a charm. Any suggestion of training resources I can avail to educate myself further in Power Automate? Clearly, this is what I need. But once again thank you!

[VJR]

Hi @Bros 

The official training modules for Power Automate Desktop is this. 

Scroll down to where it says "Items in this collection".

Exam PL-500: Microsoft Power Automate RPA Developer - Certifications | Microsoft Learn

Below are some free YouTube training content

Microsoft Power Automate Desktop For Beginners [2022] - YouTube

This one explains every single action of Power Automate Desktop

Microsoft Power Automate Desktop - YouTube