Using Office Automation and Excel I can’t sort my data on multiple keys/columns
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).
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
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?
@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.