dcsimg
Results 1 to 9 of 9

Thread: Excel Sorting with Vb.net

  1. #1

    Thread Starter
    New Member
    Join Date
    Dec 2012
    Posts
    6

    Excel Sorting with Vb.net

    I'm creating and manipulating an Excel spreadsheet from within my VB.net program. I'm using these imports:
    Code:
    Imports Microsoft.Office.Interop.Excel
    Imports Excel = Microsoft.Office.Interop.Excel
    I'm creating these objects:

    Code:
    Dim oExcel As Object
    Dim oBook As Object
    Dim oSheet As Object
    Initializing them:


    Code:
    oExcel = CreateObject("Excel.Application")
    oBook = oExcel.Workbooks.Add
    oSheet = oBook.Worksheets(1)
    After writing to the cells I'm sorting like this:

    Code:
    myRange = oSheet.Range("A2", "H50" )
    myRange.Select()
    myRange.Sort(Key1:=myRange.Range("f1"), Order1:=XlSortOrder.xlDescending, Orientation:=XlSortOrientation.xlSortColumns)
    Everything works fine at this point. I've made some of the cells background color green in column H and now I want to sort on the cell background color. What is the code to accomplish that?

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,085

    Re: Excel Sorting with Vb.net

    As far as I'm aware, Excel itself doesn't have the ability to sort based on things like background colour, which means that it doesn't expose that functionality to you... so you'll have to create it yourself.

    To do it yourself you'll need to use a sorting algorithm (perhaps Bubble Sort or another), and get it to sort based on the thing(s) you want to sort by.

    As you have got formatting as well as data, you will need to cut+paste the rows (which will be slow) to ensure the formatting moves too... alternatively you could sort the data before you put it into Excel, which would avoid the slowdown and probably also make for simpler code.

    As you haven't shown where the data is coming from (or how you determine the colour) we can't really give apt advice, but if the data is coming from a database then it is likely that you can just add an appropriate Order By clause to your query.

  3. #3

    Thread Starter
    New Member
    Join Date
    Dec 2012
    Posts
    6

    Re: Excel Sorting with Vb.net

    Thanks, but Excel does allow you to directly sort by cell color: https://www.timeatlas.com/sort-excel-by-color/

  4. #4
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,085

    Re: Excel Sorting with Vb.net

    I expect the pre-sorting the data idea would run faster (and possibly take less code), but you can find out the syntax for Excel functionality by asking Excel. Simply record a Macro, and perform the steps manually - Excel will write VBA code for you, which is fairly easy to convert to VB.Net

  5. #5

    Thread Starter
    New Member
    Join Date
    Dec 2012
    Posts
    6

    Re: Excel Sorting with Vb.net

    This is what I get:
    Code:
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add(Range("A1:A20"),xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(198, 239, 206)
        With ActiveWorkbook.Worksheets("Sheet1").Sort
            .SetRange Range("A1:A20")
            .Header = xlGuess
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    Plugging into my Vb.net form I get:
    Error BC30800 Method arguments must be enclosed in parentheses.
    Error BC30451 'ActiveWorkbook' is not declared. It may be inaccessible due to its protection level.
    Error BC30111 'Range' is an interface type and cannot be used as an expression.
    Error BC30451 'xlSortOnCellColor' is not declared. It may be inaccessible due to its protection
    Error BC30451 'xlAscending' is not declared. It may be inaccessible due to its protection level.
    Error BC30451 'xlSortNormal' is not declared. It may be inaccessible due to its protection level.
    Error BC30451 'xlGuess' is not declared. It may be inaccessible due to its protection level.
    Error BC30451 'xlTopToBottom' is not declared. It may be inaccessible due to its protection level.
    Error BC30451 'xlPinYin' is not declared. It may be inaccessible due to its protection level.

  6. #6
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,085

    Re: Excel Sorting with Vb.net

    ActiveWorkbook is only used in order to get the Worksheet, so ActiveWorkbook.Worksheets("Sheet1") should be replaced by your sheet object (oSheet).

    Ranges only exist within sheets, so the proper way to write Range("A1:A20") in Excel VBA would be ActiveWorkbook.Worksheets("Sheet1").Range("A1:A20") . As such, in your .Net code is should be oSheet.Range("A1:A20")

    All of the constants prefixed with xl (such as xlAscending) need to have the enum name in front of them, like you had before (eg: XlSortOrder.xlDescending ). I suspect Visual Studio will help you with those, just hover your mouse over them to see if you get a 'quick fix' icon appear.
    Last edited by si_the_geek; May 18th, 2019 at 12:47 PM.

  7. #7

    Thread Starter
    New Member
    Join Date
    Dec 2012
    Posts
    6

    Re: Excel Sorting with Vb.net

    Thanks. Again the VB Macro code:
    Code:
     ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add(Range("H2:H12"), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(0, 128, 0)
    
        With ActiveWorkbook.Worksheets("Sheet1").Sort
            .SetRange Range("A1:H12")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With

    This converted to VB.net compiles and runs with no errors, but the cells don't get sorted:

    Code:
    With oSheet
    
                .Sort.SortFields.Add(.Range("H2:H12"), Excel.XlSortOn.xlSortOnCellColor, Excel.XlSortOrder.xlAscending, ,
                                     Excel.XlSortDataOption.xlSortNormal).SortOnValue.Color = RGB(0, 128, 0)
            End With
            Dim rng = CType(oSheet.UsedRange, Excel.Range)
            With oSheet.Sort
                .SetRange(rng)
                .Header = Excel.XlYesNoGuess.xlYes
                .MatchCase = False
                .Orientation = Excel.XlSortOrientation.xlSortColumns
                .SortMethod = Excel.XlSortMethod.xlPinYin
                .Apply
            End With
    There didn't seem to be a vb.net enum for
    Code:
    .Orientation = xlTopToBottom
    so I went with
    Code:
    Excel.XlSortOrientation.xlSortColumns
    Also the syntax for
    Code:
    .SetRange Range("A1:H12")
    proved to be troublesome producing various errors including "Public member 'Range' on type 'Sort' not found.'" and "Type mismatch. " depending on how I wrote it, but the above is error free, but like I said, no joy on the actual sorting.

    So in the end, I added a temporary column that contained "1" in the cell next to the colored cell and sorted on that column, then cleared it. Was much simpler and fewer lines of code.
    Last edited by Maver; May 18th, 2019 at 07:19 PM.

  8. #8
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,085

    Re: Excel Sorting with Vb.net

    While xlSortColumns seems to be wrong, a quick search of the documentation doesn't find it within an enum, but does show that it actually has the same internal value as xlTopToBottom, so the behaviour would actually be the same.

    There aren't any other obvious issues, so I don't know why it isn't working.

    Quote Originally Posted by Maver View Post
    So in the end, I added a temporary column that contained "1" in the cell next to the colored cell and sorted on that column, then cleared it. Was much simpler and fewer lines of code.
    That is a good work-around.

    Note that the idea of sorting before you put the data into Excel is likely to be even simpler still (perhaps just adding "ORDER BY MyField" to an existing line), and is also likely to run quicker.

  9. #9
    Frenzied Member ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    1,986

    Re: Excel Sorting with Vb.net

    Hi,

    this might be an option
    I have in column 2 values with colors, I'll read those values (ColorIndex) and put them in column 6 for sorting

    Code:
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    
            Dim xlApp As New Microsoft.Office.Interop.Excel.Application()
            Dim xlWb As Microsoft.Office.Interop.Excel.Workbook
            xlWb = xlApp.Workbooks.Open("E:\vbExcel.xlsx")
            Dim xlSt As Microsoft.Office.Interop.Excel.Worksheet = xlWb.ActiveSheet
    
            With xlSt
                'get the ColorIndex from Column 2 and put that value in Column 6
                For iCounter = 2 To 16
                    .Cells(iCounter, 6) = _
                       .Cells(iCounter, 2).Interior.ColorIndex
                Next iCounter
    
            End With
            With xlSt
                'now sort with column 6 = F
                xlSt.Cells.Sort(Key1:=.Range("F2"), _
                               Order1:=XlSortOrder.xlAscending, _
                               Header:=XlYesNoGuess.xlYes, _
                               OrderCustom:=1, MatchCase:=False, _
                               Orientation:=Constants.xlTopToBottom, _
                               DataOption1:=XlSortDataOption.xlSortTextAsNumbers)
               
            End With
            xlSt.Columns(6).ClearContents() 'delete the ColorIndex from Column 6
    
            xlApp.ActiveWorkbook.SaveAs("E:\sorted.xlsx")
            xlApp.ActiveWorkbook.Close()
            xlApp.Quit()
            xlApp = Nothing
        End Sub
    HTH
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width