Can't sort in Excel on multiple Keys

Using Office Automation and Excel I can’t sort my data on multiple keys/columns :frowning:
The type parameter is optional but is placed between two other parameters (that I do need) and
I can’t seem to get the Sort-function to accept it (it throws an OLE-Exception).

myExelDataRange.sort( myExelDataRange.columns("A"), Office.XLAscending, _ myExelDataRange.columns("B"), Office.XLSortValues, Office.XLAscending, _ myExelDataRange.columns("C"), Office.XLAscending )

Hello Jan,

Excel is able to sort a wide amount of data and may need a few more parameters to sort the data. Here is a snippet of code from my Xojo Excel Book with Example 3-19 that should help order columns a, b, and c in your example.

Const xlSortOnValues = 0 'Not Defined in Xojo 'Set Sort Parameters xl.Worksheets("Sheet1").Sort.SortFields.Clear 'Sort Column A first xl.Worksheets("Sheet1").Sort.SortFields.Add(xl.Range("A1"), xlSortOnValues, Office.xlAscending) 'Sort Column B second xl.Worksheets("Sheet1").Sort.SortFields.Add(xl.Range("B1"), xlSortOnValues, Office.xlAscending) 'Sort Column C third xl.Worksheets("Sheet1").Sort.SortFields.Add(xl.Range("C1"), xlSortOnValues, Office.xlAscending) xl.Worksheets("Sheet1").Sort.SetRange(xl.Range("A1:C10")) xl.Worksheets("Sheet1").Sort.Header = Office.xlGuess xl.Worksheets("Sheet1").Sort.MatchCase = False xl.Worksheets("Sheet1").Sort.Orientation = Office.xlSortColumns xl.Worksheets("Sheet1").Sort.SortMethod = Office.xlPinYin 'Begin Sorting xl.Worksheets("Sheet1").Sort.Apply

Does this work for you?

Eugene, you are the best!
works perfectly :slight_smile:

I didn’t know it was possible to pass the parameters like they are properties of the sort-method (using the argument labels as the property name).
Does this work in the same way with other methods / third party DLL’s or just with Excel?

Maybe you should get the book? :wink:

@Jan Verrept
See “Program Excel 2016 with Xojo in Windows” from Eugene Dakin
http://www.xdevlibrary.com
I (and probably many more here) can recommend Eugene Dakin’s books.

The answer is ‘it depends’. If the person or company who programmed the method has purposefully set up the method in this manner, then it can.

@Markus Winter and @Paul Sondervan, thanks for the kind recommendation.

I’ll be sure to check out the library in the future.
Many topics covered that I had troubles with in the past :slight_smile:

Are-you using Excel as a Data Base softwqre ?