Powershell Script for Sorting Multiple Excel Files

A user with the handle Peter3 posted a Powershell script to stackoverflow.com which can be used to sort an Excel spreadsheet. Below I have edited the script in a very basic way so three different Excel files can be sorted by the script. I am just copying the portions from line reading, "function Release-Ref ($ref) { " to "$a = Release-Ref($objExcel) " . The file path to the Excel spreadsheets is listed at:

$objWorkbook = $objExcel.Workbooks.Open("C:\helloworld3\fielding.xlsx")

You specify the worksheet you want to sort on this line:

$objWorksheet = $objWorkbook.Worksheets.Item(1)

So this would sort the first worksheet,

$objWorksheet = $objWorkbook.Worksheets.Item(2)

. . . would sort the second worksheet, and so forth.

You specify the column you want to sort the data by on this line:

$objRange2 = $objworksheet.Range("A2")

. . . so the reference to A2 causes the data to be sort by column A starting with A2 as the first cell. The script will auto-Save the files, so you can just close them all without saving each one.

Just enter the script in the Script Pane in PowerShell ISE, and press play

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:\helloworld3\fielding.xlsx") $objWorksheet = $objWorkbook.Worksheets.Item(1)

$objRange = $objWorksheet.UsedRange $objRange2 = $objworksheet.Range("A2") [void] $objRange.Sort($objRange2) $objWorkbook.Save() $a = Release-Ref($objWorksheet) $a = Release-Ref($objWorkbook) $a = Release-Ref($objExcel) 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:\helloworld3\batting.xlsx") $objWorksheet = $objWorkbook.Worksheets.Item(1)

$objRange = $objWorksheet.UsedRange $objRange2 = $objworksheet.Range("A2") [void] $objRange.Sort($objRange2) $objWorkbook.Save() $a = Release-Ref($objWorksheet) $a = Release-Ref($objWorkbook) $a = Release-Ref($objExcel) 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:\helloworld3\pitching.xlsx") $objWorksheet = $objWorkbook.Worksheets.Item(1)

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