Help for loop thru worksheets in a Excel File

[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).

Thank you so much.

Susan Boyles

[Deenuji]

@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 🚀

[1Suz]

@Deenuji 

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

Best regards,

Suzzi 

[1Suz]

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

[Deenuji · Accepted solution]

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 👥

[1Suz · Accepted solution]

@Deenuji 

Wonderful & thank you again.  

Have a wonderful day.

Best Regards,

Suzzi