Split excel sheet

[polugopi]

Hi All,
i have an excel sheet contains 531 records in Sheet 1. Now want to split the data into sheets like sheet 1 contains 1-50 records , sheet 2 contains 51-100 records, sheet 3 contains 101-150 , sheet 4 contains 151-200 , etc.. meanwhile i need check whether sheet is exists or not ? if sheet exists then perform insertion if sheet not exists then first create a sheet and perform insertion. and finally i need to save as this excel file in a particular location with the name " Final output.xlsx/csv "

expected out put : Here Every sheet maintain headers names also.
sheet 1 : 1-50 records
sheet 2 : 51-100 records
sheet 3 : 101-150 records
sheet 4 : 151-200 records
sheet 5 : 201-250 records
sheet 6 : 251-300 records
sheet 7 : 301-350 records
sheet 8 : 351-400 records
sheet 9 : 401-450 records
sheet 10 : 451-500 records
sheet 11 : 501-531 records

 

[sivagelli]

hi,

Split Sheets Action in Excel VBO will get you achieve this.

1. Start
2. Create Instance
3. Open Workbook
4. Show (Optional)
5. Activate Workbook
6. Split Sheet
   • You need to specify how many rows per sheet
   • Name of the Workbook from where the data needs to be picked
   • Name of the worksheet from where data needs to be picked
   • Directory location where the created sheets to be placed

On Execution, you can find the files created under the specified directory.

Post back if it worked!

 

[sivagelli]

By default, the files created are of .xls type. If you wish to change the extension to either xlsx or csv, take a copy of the Split Sheet Object in Excel VBO and change the last line in the Code to .xlsx or .csv instead of .xls.

 

[polugopi]

hi,

Split Sheets Action in Excel VBO will get you achieve this.

1. Start
2. Create Instance
3. Open Workbook
4. Show (Optional)
5. Activate Workbook
6. Split Sheet
   • You need to specify how many rows per sheet
   • Name of the Workbook from where the data needs to be picked
   • Name of the worksheet from where data needs to be picked
   • Directory location where the created sheets to be placed

On Execution, you can find the files created under the specified directory.

Post back if it worked!

Hi Shiva,
it's working but it is creating separate Excel files instead of single excel file.

code : View attachment 1543756311877.png



Out put :
View attachment 1543756115410.png

 

[sivagelli]

Alright! if that is the requirement, you can achieve using the below steps-

The below approach will not modify the original worksheet which has the records. It will create a new workbook and add new worksheets with 50 records to each sheet.

Create a Collection with desired sheet names - Sheet1, Sheet2, Sheet3, etc.,. Have a couple of Data Items called StartRange and EndRange with initial values as 2 and 51 respectively.

• Start
• Create Instance
• Open Workbook (Path of the file that has 531 records)
• Activate Worksheet
• Create New Workbook
• Loop Start
  • Create WorkSheet
  • Use Copy and Paste to add Header to the newly added worksheet
  • Copy a range of cells A[StartRange] to A[EndRange] to the newly added worksheet. (For the first iteration this will copy range A2 to A51)
  • Use Mult calc to add 50 to both StartRange and EndRange. (This will increment the values to 52 to 101 respectively)
• Loop End
• Save Workbook As
  • For Filename input pass the destination path with desired file name. Output.xlsx
• Close the Instance
• End

Post back if you have questions!

 

[polugopi87]

Alright! if that is the requirement, you can achieve using the below steps-

The below approach will not modify the original worksheet which has the records. It will create a new workbook and add new worksheets with 50 records to each sheet.

Create a Collection with desired sheet names - Sheet1, Sheet2, Sheet3, etc.,. Have a couple of Data Items called StartRange and EndRange with initial values as 2 and 51 respectively.

• Start
• Create Instance
• Open Workbook (Path of the file that has 531 records)
• Activate Worksheet
• Create New Workbook
• Loop Start
  • Create WorkSheet
  • Use Copy and Paste to add Header to the newly added worksheet
  • Copy a range of cells A[StartRange] to A[EndRange] to the newly added worksheet. (For the first iteration this will copy range A2 to A51)
  • Use Mult calc to add 50 to both StartRange and EndRange. (This will increment the values to 52 to 101 respectively)
• Loop End
• Save Workbook As
  • For Filename input pass the destination path with desired file name. Output.xlsx
• Close the Instance
• End

Post back if you have questions!

instead of creating collection with sheet names can we achieve this ? like read all the data from excel sheet into collection and paste the data like 50 records to each sheet .(before inserting records it should check sheet is available or not )

 

[Aparna]

Alright! if that is the requirement, you can achieve using the below steps-

The below approach will not modify the original worksheet which has the records. It will create a new workbook and add new worksheets with 50 records to each sheet.

Create a Collection with desired sheet names - Sheet1, Sheet2, Sheet3, etc.,. Have a couple of Data Items called StartRange and EndRange with initial values as 2 and 51 respectively.

• Start
• Create Instance
• Open Workbook (Path of the file that has 531 records)
• Activate Worksheet
• Create New Workbook
• Loop Start
  • Create WorkSheet
  • Use Copy and Paste to add Header to the newly added worksheet
  • Copy a range of cells A[StartRange] to A[EndRange] to the newly added worksheet. (For the first iteration this will copy range A2 to A51)
  • Use Mult calc to add 50 to both StartRange and EndRange. (This will increment the values to 52 to 101 respectively)
• Loop End
• Save Workbook As
  • For Filename input pass the destination path with desired file name. Output.xlsx
• Close the Instance
• End

Post back if you have questions!

Hi Sivagelli,

I have the same issue that Excel Workbook has 64 Columns 67845 Rows, I want split this data in to different worksheet with in the same workbook. I'm not able to understand the below method that you mentioned. Please help me how to do this.

• Loop Start
  • Create Worksheet
  • Use Copy and Paste to add Header to the newly added worksheet
  • Copy a range of cells A[Start Range] to A[End Range] to the newly added worksheet. (For the first iteration this will copy range A2 to A51)
  • Use Mult calc to add 50 to both Start Range and End Range. (This will increment the values to 52 to 101 respectively)
• Loop End

Thanks!