Use powershell to sort an excel column with header

Question

So I'm importing a CSV, formatting the date column and saving as an XLS file.

I'd like to be able to sort the date column. I have the following code and am stumped on how to sort the column. The date column is column 4 and has "date" as the header in the 1st row

$xl = new-object -comobject excel.application
$xl.visible = $false
$Workbook = $xl.workbooks.open(“$destination")
$xl.columns.autofit() >$null
$xl.Columns.Item('D').NumberFormat = "MM/dd/yyy"
$Workbook.SaveAs($final,1)
$Workbook.Saved = $True
$xl.Quit()

I've recorded the following macro yet cannot decipher how to change it to powershell. Any help is appreciated

Sub Macro1()
'
' Macro1 Macro
'

'
   Columns("D:D").Select
     ActiveWorkbook.Worksheets("newreport").Sort.SortFields.Clear
     ActiveWorkbook.Worksheets("newreport").Sort.SortFields.Add Key:=Range("D1"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("newreport").Sort
        .SetRange Range("A1:F251")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Sort it with Sort-Object when you import the CSV? e.g. Import-Csv x.csv | Sort-Object -Property { [datetime]$_.Date } | ... — TessellatingHeckler
– TessellatingHeckler, Commented Jul 29, 2017 at 6:38
There is a small bug in your recording: it should read .Header = xlYes if you want to mark the first row as header. — alfred s.
– alfred s., Commented Dec 15, 2020 at 8:31

Vadim KOT · Accepted Answer · 2019-07-19 15:09:28Z

3

It is very easy!

$empty_Var = [System.Type]::Missing
$sort_col = $objWorkSheet.Range("A1:A255")
$objWorkSheet.UsedRange.Sort($sort_col,1,$empty_Var,$empty_Var,$empty_Var,$empty_Var,$empty_Var,1)

For the Sort method arguments meaning, you can check office vba manual:https://learn.microsoft.com/en-us/office/vba/api/excel.range.sort

answered Jul 19, 2019 at 15:09

Vadim KOT

1261 silver badge4 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Use powershell to sort an excel column with header

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related