How to sort excel sheet column wise?

Sorting Excel Worksheets using Testcomplete scripts:



Well, I got it working by creating an Excel macro enabled Template and creating the VBScript:

    Cells.Select

    ActiveWorkbook.Worksheets("{worksheet}").Sort.SortFields.Clear

    ActiveWorkbook.Worksheets("{Worksheet}").Sort.SortFields.Add Key:= _

        Range("A2:A200"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _

        :=xlSortNormal

    With ActiveWorkbook.Worksheets("{worksheet}").Sort

        .SetRange Range("A1:D200")

        .Header = xlYes

        .MatchCase = False

        .Orientation = xlTopToBottom

        .SortMethod = xlPinYin

        .Apply

    End With



where {worksheet} is the excel worksheet name.



Change the Range to the column(s) you want sorted.

Then envoke the macro via TC script (VB script):

Set Excel = Sys.OleObject("Excel.Application")

Excel.Workbooks.Open(filepath & filename)

' Excel.Visible = "True"  ' enable if you want Excel to open on desktop

Excel.Run("{Macro Name}")

Excel.Quit





I have additional Excel macro code to import the data from a csv file to be sorted and edited, then saves the file with a new name, then deletes the imported data in the Excel template, resaves the blank template.



What would normally take me a couple of minutes to launch Excel, import test data in csv format, sort it, perform other formating to the data, then resave the data in a new xls format file now takes less than 5 seconds between the Excel macro and the TC script.



Note: I found that it is possible to do what I needed via TestComplete scripts, but after numerous headaches trying to code using the Excel COM objects, I decided to do everything in an Excel Macro.  Much easier and much faster.

 

http://social.technet.microsoft.com/Forums/scriptcenter/en-US/87e5dde7-e0be-42b9-98ad-f3d3eb8d0476/sorting-data-in-excelvbs might be an option as well...



(Also: http://answers.oreilly.com/topic/883-sorting-an-excel-worksheet-using-vbscript/)

Well...  I was able to sort a worksheet by recording a script and manually performing all the steps needed as one of my test scripts requires a sorted Excel spreadsheet.



However, this returns a code that uses absolute mouse position on the open Excel worksheet and running a test with a different sized Excel window causes the mouse position to be different.

But as I run this script with the Excel window maximized using the same monitor, it works.



Another Solution is to record a Macro of what is needed, then use:



excel.Run("MACRO_NAME")



to run the macro.



I haven't tried this method, yet. So I'm not sure just how well it'll work.

It's on my ToDo list