Sorting Excel Sheet using VB.NET - what am I doing wrong?

Question

Hi Guys,
I'll come straight to the point.

I have a VB.NET application which writes into an excel sheet from SQL database. Additionally, I also format this sheet. Apart from other things, I am suppose to sort the data in the first column. The VB.NET code for this function is completly different from the Macro generated. I obtained this code from internet, but for some reason, my data still does not get sorted. Pls help !

Pls note that the first 2 rows of the Excel sheet are headers and are kept 'Frozen'.

The first column, the column I want the data to be sorted is all numeric and I want the data to be sorted in ascending order.

Pls correct the following code:

ActiveSheet.Columns("A:V").Sort(ActiveSheet.Range("A2"), XlSortOrder.xlAscending, , , , , , XlYesNoGuess.xlGuess, , , XlSortOrientation.xlSortRows, , XlSortDataOption.xlSortNormal)

Thanks a bunch.

Answer 1

little_birdie said:

I have a VB.NET application which writes into an excel sheet from SQL database. Additionally, I also format this sheet. Apart from other things, I am suppose to sort the data in the first column.

Pls note that the first 2 rows of the Excel sheet are headers and are kept 'Frozen'.

The first column, the column I want the data to be sorted is all numeric and I want the data to be sorted in ascending order.

Hi little_birdie,

Here is code sample for you to check:

Imports Microsoft.Office.Interop

Imports Microsoft.Office.Interop.Excel

Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim oExcel As Object = CreateObject("Excel.Application")

Dim oBook As Object = oExcel.Workbooks.Open("C:\Book1.xls")

Dim oSheet As Object = oBook.Worksheets("Sheet2")

oSheet.Columns.Sort(oSheet.Range("A3", "A10"), Excel.XlSortOrder.xlAscending, , , , , , Excel.XlYesNoGuess.xlGuess, , , Excel.XlSortOrientation.xlSortColumns, , Excel.XlSortDataOption.xlSortNormal)

oSheet.Columns.Sort(oSheet.Range("A3", "A10"), _

Excel.XlSortOrder.xlAscending, , , _

Excel.XlSortOrder.xlAscending, , _

Excel.XlSortOrder.xlAscending, Excel.XlYesNoGuess.xlNo, , , _

Excel.XlSortOrientation.xlSortColumns, _

Excel.XlSortMethod.xlStroke, _

Excel.XlSortDataOption.xlSortNormal, _

Excel.XlSortDataOption.xlSortNormal, _

Excel.XlSortDataOption.xlSortNormal)

oExcel.DisplayAlerts = False

oBook.SaveAs("C:\Book1.xls", True)

oExcel.DisplayAlerts = True

oExcel.Quit()

End Sub

End Class

Document: NamedRange.Sort Method (2007 System)
http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.namedrange.sort.aspx

Best regards,
Martin Xie