 
Member
Sort the numbers in an excel sheet using VBScript How can we sort the elements(numbers) in an excel sheet by using VBScript,ascending or descending.
Please help me out. <font color="red"> Subrahmanya</font> 
Senior Member
Re: Sort the numbers in an excel sheet using VBScript There is a direct method for this: Range.Sort
Const xlAscending = 1
Const xlYes = 1
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = _
objExcel.Workbooks.Open("C:\Scripts\SortTest.xls")
Set objWorksheet = objWorkbook.Worksheets(1)
Set objRange = objWorksheet.UsedRange
Set objRange2 = objExcel.Range("A1")
objRange.Sort objRange2, xlAscending, , , , , , xlYes
further Information referer http://www.microsoft.com/technet/scr.../tips0719.mspx 
Member
Re: Sort the numbers in an excel sheet using VBScript Hi mohan thanx 4 ur reponse
But its not working ,have u checked it.
I have given numbers 5 ,3,4 in A1,B1,C1 n its not sorted <font color="red"> Subrahmanya</font> 
Member
Re: Sort the numbers in an excel sheet using VBScript The code u have given is for sorting in column wise but its not working.the elements are not sorted.
And if v want to sort the elements in row wise then wat is the code <font color="red"> Subrahmanya</font> 
Senior Member
Re: Sort the numbers in an excel sheet using VBScript Pls post your question clearly....what type of sorting do you want...once you said columns and once you said rows..
If the code mentioned above is for sort by column, how will it work if you enter values in A1,B1,C1...you should be entering values in A1,A2,A3 and so on..
and yes the above code is wrong because xlAscending, xlYes parameters cannot be passed to excel through VBscript...you have to substitute it with its equivalent numerical values..Do this..
Filepath="C:\Scripts\SortTest.xls"
Dim Excelbook, worksheet
Set Excelbook = CreateObject("Excel.Application")
Set app = Excelbook.Workbooks.Open (Filepath)
Set worksheet = Excelbook.ActiveSheet
worksheet.Columns("A:B").Select
worksheet.Cells.Sort worksheet.Range("A1"),1,,,,,,2,1,False,,,1 
Member
Re: Sort the numbers in an excel sheet using VBScript hey ambience its working .Thanx for ur response <font color="red"> Subrahmanya</font> 
Member
Re: Sort the numbers in an excel sheet using VBScript Can u pls explain the following 2 statmts
worksheet.Columns("A:B").Select
worksheet.Cells.Sort worksheet.Range("A1"),1,,,,,,2,1,False,,,1
Bcoz when i giv the data in two columns A and B only 1 column is being sorted.Then i used the below stmt...
worksheet.Cells.Sort worksheet.Range("B1"),1,,,,,,2,1,False,,,1
Even then, only 1 column is sorted other is not sorted.
So pls explain the syntax.........Thanx in advance <font color="red"> Subrahmanya</font> 
Member
Re: Sort the numbers in an excel sheet using VBScript I know that 1 is for asc and 2 is for desc
sO pls expalin the remaining SYNTAX and gimme the logic for sorting the values in both the columns n explain y v r using A:B <font color="red"> Subrahmanya</font> 
Senior Member
Re: Sort the numbers in an excel sheet using VBScript For explanation...
In your Microsoft office Installation folder, Search for the file VBAXL10.CHM .This file will give you the syntax and explanation of what you need.
Anyways here is the explanatin..
I am selecting the columns(A:B) together because when you sort the values with one of the columns(here col A), then its corresponding value in B will also move along with the value in A when sorted..
as for the syntax..here it is
expression.Sort(Key1, Order1, Key2, Type, Order2, Key3, Order3, Header, OrderCustom, MatchCase, Orientation, SortMethod, DataOption1, DataOption2, DataOption3)
worksheet.Cells.Sort worksheet.Range("B1"),1,,,,,,2,1,False,,,1 
Member
Re: Sort the numbers in an excel sheet using VBScript <font color="red"> Subrahmanya</font>
Posting Permissions
 You may not post new threads
 You may not post replies
 You may not post attachments
 You may not edit your posts

Forum Rules 