Loop through different excel sheets

Help
1

Hi,
I have gone through various discussions and couldn’t find any answer. I have a excel workbook which has 300 sheets. I want to copy all these sheets into a new excel workbook. I am using for each to do this. but still its giving only one sheet output. It’s not going through the loop.

I have attached my flow . Please help me out.

Main.xaml (14.6 KB)

2

Hello,

If you use a Workbook Application variable in the Output property of the Excel Scope, you can use wb.GetSheets to store all sheets in an array

Here is sample of looping through each sheet:
Main (9).xaml (7.4 KB)

Regards.

3

I want to store all the data in a datatable (generate data table) and output this datatable in new excel workbook. how to do that?

4

Hey, can you be more specific? Are you wanting to store the data from each sheet into one large datatable?

If that’s the case, just use a Read Range inside the For each sheet, using sheet in the sheet property. Follow that with an Append Range to the new table or workbook.

Maybe something like this:

Excel Scope of new workbook
    Read Range to dt1 // might not be needed

Excel Scope of data
    For each sheet in wb.GetSheets
        Read Range of sheet to dt2
        Workbook Append Range of dt2 // to new workbook location
1 Like
5

Thanks for the fast response.

I have a workbook called “ABSS history” (It has 300 sheets and is formatted). So i am trying to copy and paste each worksheet from “ABSS history” to new workbook “result”(to get rid of that formatting).

I was able to create a flow to work for one sheet. I have attached the copy of the flow. ( In this flow its simply taking sheet 1 from “abss history” and creating a new sheet with same name and pasting the data).

I want it to do the same for all the 300 sheets.

Main (1).xaml (8.7 KB)

6

Hi,

You can refer to these samples:
Main (1) (1).xaml (9.6 KB)

Main (1) (1)b.xaml (9.9 KB)

First one, 1) creates results file if it doesn’t exist, then 2) just reads each sheet and writes it to new file.
Second one, uses alternate method to edit the current file and just Clear Formats of each sheet.

Not sure which one is faster and to be honest haven’t tested this. Regards.

4 Likes
7

Thanks a lot!! It’s working. :slight_smile: