QTP tutorials & interview questions


Search more on QTP here

VBScript - Sort BY and Sort EACH column on a Microsoft Excel Worksheet

VBScript - Sort BY and Sort EACH column on a Microsoft Excel Worksheet

Sort BY and Sort EACH column on a Microsoft Excel Worksheet
First it SORTS BY each column, treating each row as a single record.
This ASSUMES that excel's sort is stable.
Then it SORTS EACH column individually.
Sorting one column does not affect the order of the other columns.


xlAscending = 1
xlYes = 1

Set xl = CreateObject("Excel.Application")

xl.Visible = True

Set wb = xl.Workbooks.Add
Set ws = wb.Worksheets(1)
'Add some data to excel

ws.Range("a1:d1").value = array("LAST NAME", "FIRST NAME", "AKA" , "AGE")
ws.Range("a2:d2").value = array("Parker" , "Peter" , "Spiderman" , 18 )
ws.Range("a3:d3").value = array("Parker" , "Benjamin" , "Uncle Ben" , 70 )
ws.Range("a4:d4").value = array("Myer" , "Ken" , "Dr. Scripto?", 3.14 )
ws.Range("a5:d5").value = array("Parker" , "May" , "Aunt May" , 70 )

ws.UsedRange.ColumnWidth = 20

'SORT BY each column, with last column having least priority

Set objRange = ws.UsedRange

For col = objRange.Columns.Count To 1 Step -1
WScript.Sleep 1000
WScript.Echo "SORTING BY column " & col
Set objRange2 = ws.Cells(1, col)
objRange.Sort objRange2, xlAscending, , , , , , xlYes
Next

'SORT EACH column individually

Set objRange = ws.UsedRange

For col = 1 To objRange.Columns.Count
WScript.Sleep 1000
WScript.Echo "SORTING column " & col
Set objRange = ws.Columns(col)
Set objRange2 = ws.Cells(1, col)
objRange.Sort objRange2, xlAscending, , , , , , xlYes
Next

[Via]