Excel sorting

Share this post
FaceBook  Twitter  Mixx.mn     


To sort excel sheet by Rows, use following code:
Steps:
1) Create a Excel application object

2) Open Excel file

3) Activate the sheet

4) Sort Excel by Rows

5) Save the Excel file

6) close the Excel file


Code:
'Excel Sorting By Row:

Const xlAscending = 1
Const xlNo = 2
Const xlSortRows = 2

'1) Create a Excel application object
'
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

'2) Open Excel file'
Set objWorkbook = objExcel.Workbooks.Open("C:\.......1.xls")

'3) Activate the sheet'
Set objWorksheet = objWorkbook.Worksheets(1)
objWorksheet.Cells(1,1).activate

'4) Sort Excel by Rows'
Set objRange = objExcel.ActiveCell.EntireRow
objRange.Sort objRange, xlAscending, , , , , , xlNo, , , xlSortRows

'5) Save the Excel file'
objWorkbook.Save

'6) close the Excel file'
objExcel.objWorkbook.Close
set objExcel=nothing



To sort excel sheet by Column, use following code:
Steps:
1) Create a Excel application object

2) Open Excel file

3) Activate the sheet

4) Sort Excel by Column

5) Save the Excel file

6) close the Excel file


Code:
'Excel Sorting By Column :

Const xlAscending = 1'represents the sorting type 1 for Ascending 2 for Desc
Const xlYes = 1

'1) Create a Excel application object'
Set objExcel = CreateObject("Excel.Application")'Create the excel object
objExcel.Visible = True'Make excel visible

'2) Open Excel file
Set objWorkbook = objExcel.Workbooks.Open("C:\.....1.xls")'Open the document


'3) Activate the sheet'
Set objWorksheet = objWorkbook.Worksheets(1)'select the sheet based on the index .. 1,2 ,3 …
Set objRange = objWorksheet.UsedRange'which select the range of the cells has some data other than blank
Set objRange2 = objExcel.Range("A1")' select the column to sort

'4) Sort Excel by Column'
objRange.Sort objRange2, xlAscending, , , , , , xlYes


'5) Save the Excel file'
objWorkbook.Save

'6) close the Excel file'
objExcel.objWorkbook.Close
set objExcel=nothing