How to sort an excel column using Powershell

Question

I need to sort an excel spreadsheet by sorting one column be ascending numbers so 1,2,3,4,5...

Does anyone know a quick and dirty way to sort a excel column in powershell?

Have you started working on a script? If so, please post what you have so far. Are you trying to sort only one column, or sort multiple columns based on the values in one column? — Rynant
– Rynant, Commented Apr 25, 2014 at 14:50
i just put in what i have so far. this gives me an error tho saying Exception calling "Sort" with "1" argument(s): "This operation requires the merged cells to be identically sized." — Peter3
– Peter3, Commented Apr 25, 2014 at 14:55
actually this code above works. my first header column was merged with a couple rows so it was preventing to sorting — Peter3
– Peter3, Commented Apr 25, 2014 at 14:57
Yeah, it was working for me as well. Glad you figured it out. — Rynant
– Rynant, Commented Apr 25, 2014 at 15:05
I'll move your code to your answer so that it fits with SE guidelines. — Rynant
– Rynant, Commented Apr 25, 2014 at 15:10

Rynant · Accepted Answer · 2014-04-25 15:11:12Z

5

function Release-Ref ($ref) { 
    ([System.Runtime.InteropServices.Marshal]::ReleaseComObject( 
    [System.__ComObject]$ref) -gt 0) 
    [System.GC]::Collect() 
    [System.GC]::WaitForPendingFinalizers() 
} 
$objExcel = new-object -comobject excel.application 
$objExcel.Visible = $True 
$objWorkbook = $objExcel.Workbooks.Open("C:\test\flag for errors\1921BB.xls")
$objWorksheet = $objWorkbook.Worksheets.Item(1) 

$objRange = $objWorksheet.UsedRange 
$objRange2 = $objworksheet.Range("E1")  
[void] $objRange.Sort($objRange2) 
$objWorkbook.Save()
$a = Release-Ref($objWorksheet) 
$a = Release-Ref($objWorkbook) 
$a = Release-Ref($objExcel)

edited Apr 25, 2014 at 15:11

Rynant

24.6k5 gold badges61 silver badges71 bronze badges

answered Apr 25, 2014 at 15:04

Peter3

2,7294 gold badges26 silver badges40 bronze badges

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

2 Comments

user5954196 Over a year ago

This script works but I'm having trouble with the Excel file being fully "released". I.e., when I manually open the Excel file after using the script, I get a notification stating that the file is Read Only because it's still in use. When I open Task Manager, the EXCEL process is still running even though the file is closed.

user5954196 Over a year ago

Problem was fixed by adding $objWorkbook.Close() just after the $objWorkbook.Save() statement.

Collectives™ on Stack Overflow

How to sort an excel column using Powershell

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related