Hans Troost
New Member
- Joined
- Jan 6, 2015
- Messages
- 35
- Office Version
- 365
- 2019
- 2016
- Platform
- Windows
Dear all
Background and context of my question:
Have project using Power Query to get data followed by VBA-code that copies the resulting table to new workbook and format it there: conditional formats, etc.
Works fine and showed a result to user who had only 1 comment: the sorting order: PQ sorts diacritic characters like Ö after the Z, while the excel sort that they are used to places e.g. names like "Öt***"
in the O-range, between "Ob***"and "Oz***": "Ob***","Öt***","Oz***"
So I decided to move the sorting from PQ to VBA-code but had no idea about syntax.
Luckily I had a result file from the PQ/VBA project and used that to get example code using the Macro Recorder and the Data -- Sort dialog, to sort on 2 columns.
Resulting code:
So using this as an example it seemed easy:
I seems that I do not understand the use of table columns here and do something stupid.
Remark: the statement "Set tTbl = tSh.ListObjects("Uitgiftelijst")" works and is used by the - not shown - code below and has to be kept.
Any help regarding the correct syntax of the Sortfields.add Key:=??? will be very appreciated.
Kind regards, Hans
Background and context of my question:
Have project using Power Query to get data followed by VBA-code that copies the resulting table to new workbook and format it there: conditional formats, etc.
Works fine and showed a result to user who had only 1 comment: the sorting order: PQ sorts diacritic characters like Ö after the Z, while the excel sort that they are used to places e.g. names like "Öt***"
in the O-range, between "Ob***"and "Oz***": "Ob***","Öt***","Oz***"
So I decided to move the sorting from PQ to VBA-code but had no idea about syntax.
Luckily I had a result file from the PQ/VBA project and used that to get example code using the Macro Recorder and the Data -- Sort dialog, to sort on 2 columns.
Resulting code:
VBA Code:
ActiveWorkbook.Worksheets("23-03 totaal").ListObjects("Uitgiftelijst").Sort. _
SortFields.Clear
ActiveWorkbook.Worksheets("23-03 totaal").ListObjects("Uitgiftelijst").Sort. _
SortFields.Add Key:=Range("Uitgiftelijst[Naam]"), SortOn:=xlSortOnValues, _
Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("23-03 totaal").ListObjects("Uitgiftelijst").Sort. _
SortFields.Add Key:=Range("Uitgiftelijst[Straat]"), SortOn:=xlSortOnValues _
, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("23-03 totaal").ListObjects("Uitgiftelijst"). _
Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End WithSo using this as an example it seemed easy:
VBA Code:
'
' now the formatting of the target table
'
Set tTbl = tSh.ListObjects("Uitgiftelijst")
' Opmaak van de hele tabel: sortering, font, Borders etc.
'
With tTbl
'
' Sortering in VBA, want Power Query sort plaatst diacritische tekens na de Z,
' zodat bijv. ÖZTÜRK onderaan de lijst komt. De gebruiker van de lijst verwacht deze
' naam bij de O
'
With .Sort
.SortFields.Clear
'
' Based on the code from recorded macro. result: error 1004
' .SortFields.Add Key:=Range("Uitgiftelijst[Naam]"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlNormal
' .SortFields.Add Key:=Range("Uitgiftelijst[Straat]"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlNormal
' from https://www.reddit.com/r/vba/comments/30jmu8/sorting_a_tablelistobject_by_customorder_driving/
' result error 1004
' .SortFields.Add Key:=.Parent.ListColumns("Naam").Range, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlNormal
' .SortFields.Add Key:=.Parent.ListColumns("Straat").Range, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlNormal
' inspired by the above
'result: error 1004
' .SortFields.Add Key:=.Parent.ListColumns("Naam").DataBodyRange, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlNormal
' .SortFields.Add Key:=.Parent.ListColumns("Straat").DataBodyRange, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlNormal
'some others:
' .SortFields.Add Key:=tTbl.ListColumns("Naam").DataBodyRange, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlNormal
' .SortFields.Add Key:=tTbl.ListColumns("Straat").DataBodyRange, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlNormal
' .SortFields.Add Key:=tTbl.ListColumns("Naam"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlNormal
' .SortFields.Add Key:=tTbl.ListColumns("Straat"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlNormal
' could find back where I got this from.
' .SortFields.Add Key:=Range("Uitgiftelijst[[#All],[Naam]]"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlNormal
' .SortFields.Add Key:=Range("Uitgiftelijst[[#All],[Straat]]"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlNormal
' from https://stackoverflow.com/questions/46738840/how-to-refer-to-this-table-column-by-name-when-sorting and
' https://excel.officetuts.net/en/vba/sort-columns-in-table
' result: error 5.
' .SortFields.Add Key:=.Parent.Range("Uitgiftelijst[Naam]"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlNormal
' .SortFields.Add Key:=.Parent.Range("Uitgiftelijst[Straat]"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlNormal
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End WithI seems that I do not understand the use of table columns here and do something stupid.
Remark: the statement "Set tTbl = tSh.ListObjects("Uitgiftelijst")" works and is used by the - not shown - code below and has to be kept.
Any help regarding the correct syntax of the Sortfields.add Key:=??? will be very appreciated.
Kind regards, Hans
