Trying to sort a excel column

PowerShell Help
1

My PS script is taking a .csv file and creating a excel .xlsx spreadsheet. I want to sort a particular column so the number come out in descending order.. I tried several changes to the sort code and still getting the error.
Unable to get the Sort property of the Range class
At H:\GoogleAnalytics\PowershellScripts\Sample1.ps1:79 char:1

  • [void] $objRange2.Sort($objRange)

This will open the .csv file from above and write it out as an Excel Spreadsheet

Write-Host “Begin converting the .csv report file to an Excel .XLSX Spreadsheet”
$csv = “H:/GoogleAnalytics/reports/”+$reportname+“webstats”+$datetime+“.csv” #Location of the source file
$xlsx = “H:/GoogleAnalytics/reports/”+$reportname+“webstats”+$datetime+“.xlsx” #Desired location of output
$delimiter = “,” #Specify the delimiter used in the file

Create a new Excel workbook with one empty sheet

$excel = New-Object -ComObject excel.application

$workbook = $excel.Workbooks.Add(1)
$worksheet = $workbook.worksheets.Item(1)
$objRange = $worksheet.UsedRange
$objRange2 = $worksheet.Range(“C3”)
[void] $objRange2.Sort($objRange)
$worksheet.Name = $reportname
$worksheet.Cells.Item(1,1) = $TitleOfReport # get title of report from above variable
$result = $len - 8
$urltitle = $reportname.SubString(0,$result)
$urltitle = $urltitle+’ urls’
$worksheet.Cells.Item(1,2) = $urltitle.ToUpper()
$worksheet.Cells.Item(1,1).Font.Bold=$True # make the Report Title in cell A1 Bold
$worksheet.Cells.Item(1,2).Font.Bold=$True # make the Dept URL in cell B1 Bold
$worksheet.Cells.Item(2,1).Font.Bold=$True # make the Page Title in cell A2 Bold
$worksheet.Cells.Item(2,2).Font.Bold=$True # make the Page in cell B2 Bold
$worksheet.Cells.Item(2,3).Font.Bold=$True # make the Page Views in cell C2 Bold
$worksheet.Cells.Item(2,4).Font.Bold=$True # make the Avg Time on Pages in cell D2 Bold
$worksheet.Cells.Item(2,5).Font.Bold=$True # make the Users in cell E2 Bold

Build the QueryTables.Add command and reformat the data

$TxtConnector = (“TEXT;” + $csv)
$Connector = $worksheet.QueryTables.add($TxtConnector,$worksheet.Range(“A2”))
$query = $worksheet.QueryTables.item($Connector.name)
$query.TextFileOtherDelimiter = $delimiter
$query.TextFileParseType = 1
$query.TextFileColumnDataTypes = ,1 * $worksheet.Cells.Columns.Count
$query.AdjustColumnWidth = 1

Execute & delete the import query

$query.Refresh()
$query.Delete()

Save & close the Workbook as XLSX.

$Workbook.SaveAs($xlsx,51)
Write-Host “Excel Spreadsheet Report found at: H:/GoogleAnalytics/reports/”$reportname’webstats’$datetime".XLSX"
$excel.Quit()
#get-process excel | stop-process -force
Write-Host “Deleting the .CSV Report file now that the Excel Spreadsheet has been created”
Remove-Item -path .\GoogleAnalytics\reports$reportname’webstats’$datetime.csv
Write-Host “Process Completed”

2

Please can you edit your post and use the </> button to format your code. This will make it much more readable and enable us to copy and paste for testing.

3 
This will open the .csv file from above and write it out as an Excel Spreadsheet
Write-Host “Begin converting the .csv report file to an Excel .XLSX Spreadsheet”
$csv = “H:/GoogleAnalytics/reports/”+$reportname+“webstats”+$datetime+".csv" #Location of the source file
$xlsx = “H:/GoogleAnalytics/reports/”+$reportname+“webstats”+$datetime+".xlsx" #Desired location of output
$delimiter = “,” #Specify the delimiter used in the file

Create a new Excel workbook with one empty sheet
$excel = New-Object -ComObject excel.application

$workbook = $excel.Workbooks.Add(1)
$worksheet = $workbook.worksheets.Item(1)
$objRange = $worksheet.UsedRange
$objRange2 = $worksheet.Range(“C3”)
[void] $objRange2.Sort($objRange)
$worksheet.Name = $reportname
$worksheet.Cells.Item(1,1) = $TitleOfReport # get title of report from above variable
$result = $len - 8
$urltitle = $reportname.SubString(0,$result)
$urltitle = $urltitle+’ urls’
$worksheet.Cells.Item(1,2) = $urltitle.ToUpper()
$worksheet.Cells.Item(1,1).Font.Bold=$True # make the Report Title in cell A1 Bold
$worksheet.Cells.Item(1,2).Font.Bold=$True # make the Dept URL in cell B1 Bold
$worksheet.Cells.Item(2,1).Font.Bold=$True # make the Page Title in cell A2 Bold
$worksheet.Cells.Item(2,2).Font.Bold=$True # make the Page in cell B2 Bold
$worksheet.Cells.Item(2,3).Font.Bold=$True # make the Page Views in cell C2 Bold
$worksheet.Cells.Item(2,4).Font.Bold=$True # make the Avg Time on Pages in cell D2 Bold
$worksheet.Cells.Item(2,5).Font.Bold=$True # make the Users in cell E2 Bold

Build the QueryTables.Add command and reformat the data
$TxtConnector = (“TEXT;” + $csv)
$Connector = $worksheet.QueryTables.add($TxtConnector,$worksheet.Range(“A2”))
$query = $worksheet.QueryTables.item($Connector.name)
$query.TextFileOtherDelimiter = $delimiter
$query.TextFileParseType = 1
$query.TextFileColumnDataTypes = ,1 * $worksheet.Cells.Columns.Count
$query.AdjustColumnWidth = 1

Execute & delete the import query
$query.Refresh()
$query.Delete()

Save & close the Workbook as XLSX.
$Workbook.SaveAs($xlsx,51)
Write-Host “Excel Spreadsheet Report found at: H:/GoogleAnalytics/reports/”$reportname’webstats’$datetime".XLSX"
$excel.Quit()
#get-process excel | stop-process -force
Write-Host “Deleting the .CSV Report file now that the Excel Spreadsheet has been created”
Remove-Item -path .\GoogleAnalytics\reports$reportname’webstats’$datetime.csv
Write-Host “Process Completed”
4

That’s not much better, now you’ve got code mixed with your comments.

Everyone here gives up their free time to help answer queries. Please help us by taking a few minutes to format your posts properly.

What, exactly, are you trying to sort? In your code you’re calling the Sort method on $ObjRange2 but you’ve defined that range as a single cell, C3.

If you want to sort the items in the range you’ve defined using the UsedRange property, then use:

$objRange.Sort($objRange)

Otherwise, define your range first:

$myRange = $worksheet.Range("D2:D7")
$myRange.Sort($myRange)
5

Thanks for your help. After replacing your 2 statements the column in the original order.
I want the column to be in descending order.

Write-Host "Begin converting the .csv report file to an Excel .XLSX Spreadsheet"
$reportname = "baseball"
$csv = "H:/GoogleAnalytics/baseball.csv"    
$xlsx = "H:/GoogleAnalytics/reports/baseball.xlsx"  
$delimiter = "," #Specify the delimiter used in the file
$date = Get-Date -Format yyyy-MM-dd 
$time = get-date -Format HHmmss
$datetime = $date + "-" + $time
$excel = New-Object -ComObject excel.application
$workbook = $excel.Workbooks.Add(1)
$worksheet = $workbook.worksheets.Item(1)
$worksheet.Name = $reportname
$myRange = $worksheet.Range("B1:B7")
$myRange.Sort($myRange)
$TxtConnector = ("TEXT;" + $csv)
$Connector = $worksheet.QueryTables.add($TxtConnector,$worksheet.Range("A1"))
$query = $worksheet.QueryTables.item($Connector.name)
$query.TextFileOtherDelimiter = $delimiter
$query.TextFileParseType  = 1
$query.TextFileColumnDataTypes = ,1 * $worksheet.Cells.Columns.Count
$query.AdjustColumnWidth = 1
$query.Refresh()
$query.Delete()
$Workbook.SaveAs($xlsx,51)
$excel.Quit()
6

Sorry, missed that. You just need to specify a value for the sort order:

$myRange.Sort($myRange,2)
7

Thanks Matt. I added the XISortOrder number 2 for descending… The column still comes out in the order of the .csv file. I created this simple 2 column .csv file. Column A has a team name, column B has a 3 digit number. There are only 7 rows… Do you have any idea why the sort is not working?

8

Are you adding the data to the sheet after you apply the sort? It appears that way from your script.

You should populate the data, then define the range where the data occurs, then sort.

Edit:
Incidently, the code I gave only sorts 1 column. If the value in column B is associated with the value in column A, you need to specify the range to be sorted, and the range you want to sort on:

$rangeToSort = $worksheet.usedRange
$rangeToSortBy = $workSheet.Range("B1:B7")
$rangeToSort.Sort($rangeToSortBy,2)