locked
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.
    Tuesday, July 29, 2008 9:04 PM

Answers

  • 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.ObjectByVal 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

    Tuesday, August 05, 2008 8:50 AM