Sorting an Excel worksheet with header row in powershell

wmehardt used Ask the Experts™
I'm trying to learn powershell from a vbscript background and for the life of me I can't figure out how to properly sort a worksheet in powershell.

I want to sort descending on column H, but I have a header row, so I'd like to start on cell H2.

How would I do something like this? And how would I sort on 2 separate columns? I can't seem to find any references specifically for powershell on the web.
$excel = New-Object -ComObject excel.application
$wb = $excel.Workbooks.Open("C:\scripts\worksheet.xls")
$ws = $wb.Worksheets.item(1)

Open in new window

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Chris DentPowerShell Developer
Top Expert 2010

It's tricky to find references like this because PowerShell was never built as a language to manage Excel. It can for the same reasons that VbScript can but examples of this are in the minority.

For instance, if your file was saved as a CSV you would run:

Import-CSV "YourFile.csv" | Sort-Object WhateverColumn1, WhateverColumn2

The best I can offer at the moment is a link to this:

At least until Monday when I have a working copy of Excel to test things with.

I managed to sort on 1 field, but I can't manage to sort on the 2 fields using the method below (which is the same as I'd do in VBscript)

I want to sort on column J, then H

"Batchelor", Developer and EE Topic Advisor
Top Expert 2015
Though the official VBA syntax is
Sort(Key1, Order1, Key2, Type, Order2, Key3, Order3, Header, OrderCustom, MatchCase, Orientation, SortMethod, DataOption1, DataOption2, DataOption3)
> $range.Sort
outputs the following syntax:

MemberType          : Method
OverloadDefinitions : {Variant Sort (Variant, XlSortOrder, Variant, Variant, XlSor
                      tOrder, Variant, XlSortOrder, XlYesNoGuess, Variant, Variant
                      , XlSortOrientation, XlSortMethod, XlSortDataOption, XlSortD
                      ataOption, XlSortDataOption)}
TypeNameOfValue     : System.Management.Automation.PSMethod
Value               : Variant Sort (Variant, XlSortOrder, Variant, Variant, XlSort
                      Order, Variant, XlSortOrder, XlYesNoGuess, Variant, Variant,
                       XlSortOrientation, XlSortMethod, XlSortDataOption, XlSortDa
                      taOption, XlSortDataOption)
Name                : Sort
IsInstance          : True
The two variants after the first XlSortOrder are puzzling. I tried and had success by ignoring the first variant of those:

> $range.Sort($ws.Range("J:J"), 2, $null, $ws.Range("H:H"), 2)

But don't ask me more - has been my first time I tried to access Excel thru PS. It is not the way PS is supposed to be used - Chris-Dent's approach is in fact PS-stylish.
TY.. I know this isn't a "powershell" way to do this, I just wanted to make sure i knew how it would be done.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial