How to do filter (sorting) on Columns of excel file programmetically using vb.net?

Question

we are generating a excel report and we need to sort the data programmatically before saving the file.

Is it possible to implement filter on a specific column i.e. sorting of a excel file programmatically using vb.net?

As per your suggetion, in order to generate excel report with sorting on specific column I just implemented the logic as below..

Dim MyRange As Excel.Range
  gobjExcelReportSheet.Activate()
      MyRange = gobjExcelReportSheet.Range("A8", "L8")
      MyRange.Select()
      MyRange.Sort(Key1:=MyRange.Range("L8"), _
                   Order1:=XlSortOrder.xlAscending, _
                   Header:=XlYesNoGuess.xlGuess, _
                   MatchCase:=False, _
                   Orientation:=XlSortOrientation.xlSortColumns)

I tried this logic just before saving the file and even just after saving the file but it is not giving any result i.e. with sorting result or even I am not getting any error.

But the following code works....

gobjExcelReportSheet.Application.Selection.Autofilter()

But there is no option for sorting programmetically.

Please help me...

Thanks!

Answer 1

Assuming you're using the interop assemblies this is how I do it:

Dim myWorkSheet As Excel.Worksheet = myWorkbook.Worksheets(aSheetName)
myWorkSheet.Activate()
Dim myRange As Excel.Range

myRange = myWorkSheet.Range("A1", "L10")
myRange.Select()


myRange.Sort(Key1:=myRange.Range("D1"), _
                        Order1:=Excel.XlSortOrder.xlAscending, _
                        Orientation:=Excel.XlSortOrientation.xlSortColumns)

Basically you need to select a range (in this case A1:L10, then choose to sort it by a certain column (D).