Sorting data excel

[brwiese]

I've been working with excel in visual basic .net 2002. The only thing I'm having trouble with is sorting data. What I do in excel is select the entire sheet and sort all the data by one of the columns. I want to do this in visual basic and have had some problems. For one I would like to define the header cell by number something like worksheet.cells(1,5), MSDN gives examples calling the sort function by string. I cannot get the worksheet.range.sort to work for me and I don't even know if this is what I should use.


brwiese

[phenom]

Hi brwiese,

Use this code, it shows you how to sort Data in Excel…

'VB 2005
Dim ObjExcel As New Microsoft.Office.Interop.Excel.Application
        ObjExcel.Visible = True
        ObjExcel.Workbooks.Add()
        ObjExcel.Cells(1, 1) = "1000"
        ObjExcel.Cells(2, 1) = "100"
        ObjExcel.Cells(3, 1) = "500"
        ObjExcel.Cells(4, 1) = "900"
        'Sorting Data
        ObjExcel.Cells.Range("A1:A4").Sort(Key1:=ObjExcel.Cells.Range("A1"), Order1:=XlSortOrder.xlAscending, Header:= _
    XlYesNoGuess.xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=XlSortOrientation.xlSortColumns, _
        DataOption1:=XlSortDataOption.xlSortNormal)
        ObjExcel = Nothing

Hope this helps...

Regards,

[brwiese]

Thanks for the quick response. I've gotten as far as sorting a single column, its extending this to sort all columns by the data in a single column that I'm not getting.

brwiese

[phenom]

Hi brwiese,

Sorry for late reply, I’m little busy cuz I’m traveling tonight for a vacation

Here’s how to sort multiple columns

Dim ObjExcel As New Microsoft.Office.Interop.Excel.Application
        ObjExcel.Visible = True
        ObjExcel.Workbooks.Add()
        ObjExcel.Cells(1, 1) = "Column A"
        ObjExcel.Cells(2, 1) = "1000"
        ObjExcel.Cells(3, 1) = "100"
        ObjExcel.Cells(4, 1) = "6000"
        ObjExcel.Cells(5, 1) = "20"
        ObjExcel.Cells.Range("A1:A5").Sort(Key1:=ObjExcel.Cells.Range("A2"), Order1:=XlSortOrder.xlAscending, Header:=XlYesNoGuess.xlGuess, _
                OrderCustom:=1, MatchCase:=False, Orientation:=XlSortOrientation.xlSortColumns, _
                DataOption1:=XlSortDataOption.xlSortNormal)
        ObjExcel.Cells(1, 2) = "Column B"
        ObjExcel.Cells(2, 2) = "10"
        ObjExcel.Cells(3, 2) = "200"
        ObjExcel.Cells(4, 2) = "5000"
        ObjExcel.Cells(5, 2) = "20"
        ObjExcel.Cells.Range("B1:B5").Sort(Key1:=ObjExcel.Cells.Range("B2"), Order1:=XlSortOrder.xlAscending, Header:=XlYesNoGuess.xlGuess, _
                OrderCustom:=1, MatchCase:=False, Orientation:=XlSortOrientation.xlSortColumns, _
                DataOption1:=XlSortDataOption.xlSortNormal)
        ObjExcel = Nothing

I’m sure there is better way, I’ll try to find it out, but as you know you cannot sort all columns based on a specific one… anyway try this…

Hope this helps…

Regards,

[brwiese]

Thanks phenom,

Your first post helped me with the code I have below, I got it working before your last reply, but I'll definetly have a look at the code you posted. I think the biggest problem I seemed to have was how you select rows or columns for the range object. Anyway here's the code I have that selects the entire sheet (first 5000 rows is good enough for what I need) and sorts according to a specified column.

Dim testRng, data As Range

data = DirectCast(excelWorksheet.Rows("1:5000"), Excel.Range)
testRng = excelWorksheet.Cells(1, ppColumn)
data.Sort(key1:=testRng,_ Order1:=XlSortOrder.xlAscending,Header:=XlYesNoGuess.xlYes,_ Orientation:=Excel.XlSortOrientation.xlSortColumns)

The ppColumn is just an integer to the column I'm sorting by.

brwiese