.NET Excel Sorting with Vb.net

[.NET] Excel Sorting with Vb.net - Printable Version

+- Hack Forums (https://hackforums.net)
+-- Forum: Tech (https://hackforums.net/forumdisplay.php?fid=444)
+--- Forum: Coding (https://hackforums.net/forumdisplay.php?fid=151)
+---- Forum: Software Development (https://hackforums.net/forumdisplay.php?fid=118)
+---- Thread: [.NET] Excel Sorting with Vb.net (/showthread.php?tid=5970960)

Excel Sorting with Vb.net - JavaJane - May 17, 2019

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?

RE: Excel Sorting with Vb.net - tweety - May 29, 2019

since no one answered lol..

take a look at this; https://stackoverflow.com/questions/23653263/sorting-on-cell-color-in-excel-using-c-sharp

i dont really like linking sof links but couldnt find much on it, the example in that post seems to be what you're looking for, something like this?

PHP Code 


<?php 
currentSheet.Sort.SortFields.Clear()
    currentSheet.Sort.SortFields.Add(DupesColumn, XlSortOn.xlSortOnCellColor, XlSortOrder.xlAscending)
    currentSheet.Sort.SortFields(0).SortOnValue.Color = XlRgbColor.rgbRoyalBlue
    currentSheet.Sort.SetRange(DupesColumn.CurrentRegion)
    currentSheet.Sort.Header = XlYesNoGuess.xlYes
    currentSheet.Sort.MatchCase = False
    currentSheet.Sort.Orientation = XlSortOrientation.xlSortColumns
    currentSheet.Sort.SortMethod = XlSortMethod.xlPinYin
    currentSheet.Sort.Apply() 

give it a shot, if it doesnt work il take a look when im home