Help for loop thru worksheets in a Excel File

(0) Share

Report

Posted on by 1Suz

Hello All,

I have only done a few desktop flows and stuck on this one I am working on now.

I receive an Excel report with multiple worksheets (tabs) each month. I separate them to condense them into their own Excel Workbook using the worksheet name. I will copy and paste the next month (& so on) into the new file.

I have successfully created the flow for the first tab, but stuck on creating an "If /loop" to go thru each tab.

I need the IF statement - as each original file will have different # of worksheets included each month. I attached the flow to date.

End Goal (Making a file for each one of these).

Power Automate Desktop multiple worksheets loop thru flow.png

Thank you so much.

Susan Boyles

Categories:

Power Automate Desktop

I have the same question (0)

All responses (5)

Answers (2)

Sort by

CU16071609-1 6,255 Moderator on at

Like
a
(1)

Report
Copy link

Link copied!
@1Suz

You've created your desktop flow using the Power FX feature, which is currently in its preview stage. However, I wouldn't suggest using it for live projects just yet. So recreate your desktop flow just disabled power fx as mentioned in the below screenshot and follow the below instructions.

Please follow the below approach:

My Input excel file:

Output:

Code:

Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''C:\\Users\\deenu\\OneDrive\\Documents\\Split.xlsx''' Visible: True ReadOnly: False Instance=> ExcelInstance Excel.GetAllWorksheets Instance: ExcelInstance Worksheets=> SheetNames LOOP FOREACH CurrentSheet IN SheetNames Excel.SetActiveWorksheet.ActivateWorksheetByName Instance: ExcelInstance Name: CurrentSheet Excel.ReadFromExcel.ReadAllCells Instance: ExcelInstance ReadAsText: False FirstLineIsHeader: True RangeValue=> ExcelData Excel.LaunchExcel.LaunchUnderExistingProcess Visible: True Instance=> ExcelInstance1 Excel.WriteToExcel.WriteNamedCell Instance: ExcelInstance1 Value: ExcelData.ColumnHeadersRow CellName: $'''A1''' Excel.WriteToExcel.WriteCell Instance: ExcelInstance1 Value: ExcelData Column: $'''A''' Row: 2 Excel.SaveExcel.SaveAs Instance: ExcelInstance1 DocumentFormat: Excel.ExcelFormat.FromExtension DocumentPath: $'''C:\\Boot\\%CurrentSheet%.xlsx''' END

How to copy/paste the above code into you PAD?

Thanks,
Deenuji Loganathan 👩‍💻
Automation Evangelist 🤖
Follow me on LinkedIn 👥

-------------------------------------------------------------------------------------------------------------
If I've helped solve your query, kindly mark my response as the solution ✔ and give it a thumbs up!👍 Your feedback supports future seekers 🚀

Was this reply helpful? Yes No
1Suz 17 on at

Like
a
(0)

Report
Copy link

Link copied!

@Deenuji
Thank you, I will do that. I appreciate the feedback.

Best regards,
Suzzi

Was this reply helpful? Yes No
1Suz 17 on at

Like
a
(0)

Report
Copy link

Link copied!

Hello @Deenuji ,

Thank you for your assistance.
The flow is successful sans saving to the One Drive Folder
The flow is success with saving to the desktop folder.

1) Is there a reason (is C/Boot that you had in your code (which failed when I tried after the one drive didn't work)?

2) For next month, should I create a new flow (copying this one as I will still need to separate the date, but instead of saving to a new file the worksheet will be extracted and adding to the file created by the above flow.

I would keep the same loop process, correct? By the end of the year, I should have 6 files with 12 worksheets each.

Thank you again and I appreciate the time.
Best regards,
Suzzi

Was this reply helpful? Yes No
Verified answer

CU16071609-1 6,255 Moderator on at

Like
a
(1)

Report
Copy link

Link copied!

If I've helped solve your query, kindly mark my response as the solution ✔ and give it a thumbs up!👍 Your feedback supports future seekers 🚀

And regarding your couple of questions,

1) Is there a reason (is C/Boot that you had in your code (which failed when I tried after the one drive didn't work)? You can change into your local folder. I want to save my worksheet in that folder, so I have configured that path.

2) For next month, should I create a new flow (copying this one as I will still need to separate the date, but instead of saving to a new file the worksheet will be extracted and adding to the file created by the above flow. Yes you can use this flow as base one and you can modify it as per your business logics. Next month you can try, in case facing any issues you can raise one more new topic like this.

Thanks,
Deenuji Loganathan 👩‍💻
Automation Evangelist 🤖
Follow me on LinkedIn 👥

Was this reply helpful? Yes No
Verified answer

1Suz 17 on at

Like
a
(0)

Report
Copy link

Link copied!

@Deenuji
Wonderful & thank you again.
Have a wonderful day.

Best Regards,
Suzzi

Was this reply helpful? Yes No