Sorting an Excel worksheet with header row in powershell

wmehardt
wmehardt used Ask the Experts™
on
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
$excel.visible=$true
$wb = $excel.Workbooks.Open("C:\scripts\worksheet.xls")
$ws = $wb.Worksheets.item(1)
$range=$ws.usedrange
$range.Sort($ws.range("h2"),2)

Open in new window

Comment
Watch Question

Do more with

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

Commented:
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:

http://www.eggheadcafe.com/software/aspnet/32824483/what-is-usedrange.aspx

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

Chris
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

[void]$ws.UsedRange.Sort($ws.Range("J1"),1,$ws.range("H1"),$null,1,$null,1,1)
#key1,order1,key2,type,order2,key3,order3,headerrow
"Batchelor", Developer and EE Topic Advisor
Top Expert 2015
Commented:
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