QTP VBScript - Excel Application - Sorting data in excel worksheet based on column name

Below script or code snippet is very useful in sorting an excel workbook based on the name of columns header . We can sort multiple columns of the excel worksheet. For e.g there are 3 columns with header name as "name","class" and "value" based on which we want to sort the database with priority in order name>class>value. So provide strSortbyfield as "name>class>value". Below piecee of code can be implemented to achieve the same

strWorkBook = InputBox ("Input the workbook with full path")

strWorkSheet = InputBox("Enter the sheet name")

strSortbyFields = Inputbox ("provide the field names, seperated by > in case of multiple sorting of data is required")

Set objExcel = Createobject("Excel.Application")

objExcel.Visible = False

Set XLWorkBook = objExcel.WorkBooks.Open(strWorkbook)

Set objWorksheet = XLWorkBook.Worksheets(strWorkSheet)  

Set objRange = objWorksheet.UsedRange

ColCount = objRange.columns.count

strSortDataArr = split(strSortbyFields,">")

intCnt = ubound(strSortDataArr)

For i = intCnt to 0 step -1

For j = 1 to ColCount step 1

If (objWorkSheet.cells(1,j).value =strSortDataArr(i)) Then

''get the column based on which data needs to be sorted in the excel document

chrCde = Chr(asc("A")- 1+j) & "1"

boolExcelSortData = True

Set objRangeSrt = objExcel.Range(chrCde)

objRange.Sort objRangeSrt, xlDescending, , , , , , xlYes 

XLWorkBook.save

             Exit For

End If  

Next

Next

''save the workbook and close

XLWorkBook.Save

XLWorkBook.Close

objExcel.Quit

Set XLwORKBook = Nothing

Set objExcel = Nothing