Sorting Excel range of cells by column in VB

nhsal69

Hi there, I have the following code which takes a table from SQL does some calculations on it and adds a couple of columns... I'm having issues with sorting the data (descending) on Column "N" from Row 4 to row 50000 (the end of the document) the code I think should work appears to reorder the columns by the entries in row 4. Any advice gratefully received.... <pre> Dim SQlQuery2 As String = "select * from growth where Name <> '[Files]'AND InitialSize <> 0 order by FullPath" Dim SQLCommand2 As New SqlCommand(SQlQuery2, myConn) Dim SQlReader2 As SqlDataReader = SQLCommand2.ExecuteReader Dim R2 As Integer = 3 While SQlReader2.Read R2 = R2 + 1 osheet.Range("I" & R2).Value = SQlReader2.GetValue(0).ToString osheet.Range("I" & R2).BorderAround(8) osheet.Range("J" & R2).Value = SQlReader2.GetValue(1).ToString osheet.Range("J" & R2).BorderAround(8) osheet.Range("K" & R2).Value = SQlReader2.GetValue(2).ToString osheet.Range("K" & R2).BorderAround(8) osheet.Range("L" & R2).Value = SQlReader2.GetValue(3).ToString osheet.Range("L" & R2).BorderAround(8) End While SQlReader2.Close() SQlReader2 = Nothing Dim D1 As Integer = 4 Do Until D1 = R2 + 1 osheet.Range("M" & D1).Formula = "=(K" & D1 & ")-(L" & D1 & ")" osheet.Range("M" & D1).BorderAround(8) osheet.Range("N" & D1).Formula = "=((M" & D1 & ")/(L" & D1 & "))*100" osheet.Range("N" & D1).BorderAround(8) D1 = D1 + 1 Loop osheet.Range("I4:N50000").Sort(Key1:=osheet.Range("N4:N50000")) </pre>

Johan Hakkesteegt

I am not sure, but considering that you are telling excel to do just that in the last line of your code, what is it that you wanted to happen?

My advice is free, and you may get what you paid for.

nhsal69

ok, what "should" happen is that the range of values from I4 to N50000 should be sorted in descending order by the numerical values in Column N, in this case from N4 to N50000 (As N1, N2 and N3 have text in them...) osheet.Range("I4:N50000").Sort(Key1:=osheet.Range("N4:N50000")) I thought that this would do that, but it clearly doesn't but I don't see what I've done wrong. Can you help??

Johan Hakkesteegt

I'm sorry, but you are not entirely making sense to me...

nhsal69 wrote:

should be sorted in descending order by the numerical values in Column N

nhsal69 wrote:

N1, N2 and N3 have text in them

Which one is it, text or numbers, or both? Never mind, sorry, I didn't quite read it right. If this doesn't do what you want it do to, then what is it doing, if anything?

My advice is free, and you may get what you paid for.

modified on Thursday, November 12, 2009 6:32 AM

nhsal69

N1, N2 and N3 have text in them [quote] Which one is it, text or numbers, or both? [/quote] It's both the first 3 rows contain text which should be ignored (the headers), the rest of the rows in the column contain numbers which need to be sorted in descending order. This process should sort columns I, J, K, L, M by the numerical values in N from largest value in column N to the smallest What the code: osheet.Range("I4:N50000").Sort(Key1:=osheet.Range("N4:N50000")) Appears to do, is sort the columns I, J, K, L, M, N in ascending order by the value in each column in row 4. So for example the contents of column I move to column J because I4 has a larger value in it than J4 Does this now make sense to you??

Johan Hakkesteegt

So if I understand you correctly, excel is now sorting the range by column instead of rows. If that is what is happening I think you'll have to play around with how the ranges are determined in that last sentence.

My advice is free, and you may get what you paid for.

Johan Hakkesteegt

I got this to work for me (Excel 2007):

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim xlApp As Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet
Dim misValue As Object = System.Reflection.Missing.Value

    Dim i As Integer = 1


    Dim tmpConn As New SqlConnection(connStrDBHERFI)
    Dim tmpComm As SqlCommand = Nothing
    Dim tmpRdr As SqlDataReader = Nothing
    Try

        xlApp = New Excel.ApplicationClass
        xlWorkBook = xlApp.Workbooks.Add(misValue)
        xlWorkSheet = xlWorkBook.Sheets("sheet1")
        Me.Cursor = Cursors.WaitCursor
        If Not tmpConn.State = ConnectionState.Open Then tmpConn.Open()
        tmpComm = New SqlCommand("Select cardcode, cardname from ocrd where 'y' not in (FrozenFor, ValidFor)", tmpConn)
        tmpRdr = tmpComm.ExecuteReader(CommandBehavior.Default)
        If tmpRdr.HasRows Then
            Do While tmpRdr.Read
                xlWorkSheet.Cells(i, 1) = tmpRdr.Item("CardCode")
                xlWorkSheet.Cells(i, 2) = tmpRdr.Item("CardName")
                i += 1
            Loop
            With xlWorkSheet.Range("A1", "B" & i)
                '.BorderAround()'
                .Sort(Key1:=.Columns(2), Order1:=Excel.XlSortOrder.xlAscending, \_
Key2:=.Columns(1), Order2:=Excel.XlSortOrder.xlAscending, \_
Orientation:=Excel.XlSortOrientation.xlSortColumns, \_
Header:=Excel.XlYesNoGuess.xlNo, \_
SortMethod:=Excel.XlSortMethod.xlPinYin, \_
DataOption1:=Excel.XlSortDataOption.xlSortNormal, \_
DataOption2:=Excel.XlSortDataOption.xlSortNormal, \_
DataOption3:=Excel.XlSortDataOption.xlSortNormal)
            End With


        End If
        tmpRdr.Close()
        tmpConn.Close()
        tmpConn.Dispose()

        xlWorkSheet.SaveAs("c:\\Temp\\test.xlsx")
        xlWorkBook.Close()
        xlApp.Quit()

        releaseObject(xlApp)
        releaseObject(xlWorkBook)
        releaseObject(xlWorkSheet)

    Catch ex As Exception
        MsgBox(ex.ToString)
    Finally
        Me.Cursor = Cursors.Default
    End Try
End Sub

Private Sub releaseObject(ByVal obj As Object)
    Try
        System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
        obj = Nothing
    Catch ex

Oakman

I'll thank you because now that you've done his work for him, he's got better things to do.

Jon

Rob Graham wrote:

100% subsidies are very popular among the 50% of the population that pays no income tax...

Soap Box 1.0: the first, the original, reborn troll-less

Johan Hakkesteegt

Thanks... ;) Fortunately, this was something I have use for myself, and I too got it almost entirely the old fashioned Google-and-Paste way.

My advice is free, and you may get what you paid for.

nhsal69

Just logged in again... Ta for that.... I'm just using the "With xlWorkSheet.Range("A1", "B" & i).Sort(Key1:=.Columns(2), Order1:=Excel.XlSortOrder.xlAscending" with my information: osheet.Range("I4", "N10").Sort(Key1:=osheet.Columns("Current Size"), Order1:=Excel.XlSortOrder.xlAscending) But unfortunatly get the following error: Type mismatch. (Exception from HRESULT: 0x80020005 (DISP_E_TYPEMISMATCH)) when compiling my code.. If I try the following line (substituting "Current Size" to the first cell of the sort column "N4": osheet.Range("I4", "N10").Sort(Key1:=osheet.Columns("N4"), Order1:=Excel.XlSortOrder.xlAscending) I get the following error Exception from HRESULT: 0x800A03EC Have you any idea why?? Is this a VB error, an Excel one or a formatting one?? Thanks in advance....

Johan Hakkesteegt

To be perfectly honest, creating excel sheets using interop is an absolute development nightmare. For one, if the user does not have excel installed, your application will not work. The error you described is one of many similar ones I have seen when I tried to do it with interop (that's what is throwing the error). The problem is, the syntax changes with each different version of excel, so even if you finally get it to work, you can start over as soon as MickeySoft decides to market a new Office version. Personally, I have decided to cut my losses, and I have rebuilt the whole thing using xml. That means I basically build up an xml file in a string and in the end I save the string to an excel file. I can now create excel files containing autofilters, all kinds of formatting, frozen panes, and more, and the resulting file can be opened in any excel version from 97 to 2007. In other words, I warmly recommend using my approach, and saving yourself a big headache.

My advice is free, and you may get what you paid for.