Slpitting Excel

[vinay]

Hi, Can anyone tell me how to split the Excel Sheets? Suppose i have 50 records in Sheet1 i have to slpit it into 2 sheets as 25 records in Sheet2 and 25 records in Sheet3 like this

 

[bharadwaj813]

I hope this works
1. Put them in a collection
2.Create a new excel
3.Paste ur collection in this new excel with corresponding sheets.

Hope, this works fine.

 

[vinay]

No it doesn't works

 

[bharadwaj813]

Forgotten to tell u
Use split collections from Utility Collection Manipulation and then u can dump ur req collection into your req sheet.
Hope this helps.

 

[bharadwaj813]

Still one more option for u
In the MS Excel Vbo, we have split worksheet option. Hope this answers ur quesiton.

 

[vinay]

Still one more option for u
In the MS Excel Vbo, we have split worksheet option. Hope this answers ur quesiton.

yes but split work sheet splitting the one excel file into 2 excel files,but i want in one excl file 2 sheets

 

[bharadwaj813]

Then use split collection

 

[vinay]

Not Working

 

[VJR]

Hi @vinay,

Here is what @bharadwaj813 is suggesting to convey.
Be noted that this is not fully tested and works well under general conditions. You need to check all possible scenarios: 1 row in excel, 0 rows in excel, etc.

2.jpg Output Stored in 'Number of rows' data item
4.jpg Output Stored in collection called 'Data'
5.jpg Output stored in collection called 'Output Collection'
7.jpg Output stored in the same collection called 'Output Collection'

 

[VJR]

Another easier approach is as below.
You still need to make amendments for scenarios like 1 row in excel, 0 rows in excel, etc.
To do that simply add a Decision and Calc stage to adjust the SplitTotalRows depending on the Total Rows returned.
Make use of the Copy Paste Worksheet Range.
Copy Header and then Data to the corresponding sheets.

 

[bharadwaj813]

Amazing work man.
Keep it up.
Cheers !! @ VJR

 

[vinay]

Another easier approach is as below.
You still need to make amendments for scenarios like 1 row in excel, 0 rows in excel, etc.
To do that simply add a Decision and Calc stage to adjust the SplitTotalRows depending on the Total Rows returned.
Make use of the Copy Paste Worksheet Range.
Copy Header and then Data to the corresponding sheets.

Can u give logic for Calculate rows and round no.of rows? i mean screen shots of those

 

[vinay]

Can u give logic for Calculate rows and round no.of rows? i mean screen shots of those

Hi, everything works but im getting following error

 

[vinay]

Another easier approach is as below.
You still need to make amendments for scenarios like 1 row in excel, 0 rows in excel, etc.
To do that simply add a Decision and Calc stage to adjust the SplitTotalRows depending on the Total Rows returned.
Make use of the Copy Paste Worksheet Range.
Copy Header and then Data to the corresponding sheets.

Got It Bro...thankyou