I open a CSV file in Excel and run this macro to change the background color. I am trying to convert this part of code to PowerShell.
lrow = Range("G" & Rows.Count).End(xlUp).Row
Set MR = Range("G2:G" & lrow)
For Each cell In MR
If UCase(Trim(cell.Value)) = "FALSE" Then
cell.Interior.ColorIndex = 3
End If
Next
Any help converting this code to PowerShell.
Thanks SR
You could write something like this:
$objExcel = New-Object -ComObject Excel.Application
$objExcel.Visible = $true
$objExcel.DisplayAlerts = $false
$filePath = "c:\logs\2015-04-23.csv"
$xlsFilePath = Get-Item -Path $filePath | % { Join-Path (Split-Path $_ -Parent) "$($_.BaseName).xls" }
$workBook = $objExcel.Workbooks.Open($filePath)
$workSheet = $WorkBook.sheets | select -First 1
$xlup = -4162
$lrow = $workSheet.cells.Range("G" + $workSheet.Rows.Count).End($xlup).Row
$workSheet.cells.Range("G2:G" + $lrow) | % {
$value = $_.Text
if($value.ToUpper() -eq "TRUE"){
$_.Interior.ColorIndex = 3
}
}
$WorkBook.SaveAs($xlsFilePath, 18)
$objExcel.Quit()
If you have a very large file, it is faster to search values using powershell then updating the Excel sheet. The following example looks a bit funny but executes much faster.
$filePath = "c:\logs\2015-04-23.csv"
$rowAliases = 97..122 | foreach { ([char]$_).ToString().ToUpper() }
$selectedRow = "G"
$selectedName = (Get-Content $filePath -ReadCount 1 -TotalCount 1).Split(",")[$rowAliases.IndexOf($selectedRow)]
$startRow = 2
$rowCount = 1;
$objExcel = New-Object -ComObject Excel.Application
$objExcel.Visible = $true
$objExcel.DisplayAlerts = $false
$xlsFilePath = Get-Item -Path $filePath | % { Join-Path (Split-Path $_ -Parent) "$($_.BaseName).xls" }
$workBook = $objExcel.Workbooks.Open($filePath)
$workSheet = $WorkBook.sheets | select -First 1
Import-Csv -Path $filePath | % {
if($rowCount -ge $startRow){
[string]$value = $_ | select -ExpandProperty $selectedName
if($value.ToUpper() -eq "TRUE"){
$workSheet.cells.Item($rowCount + 1, $selectedIndex + 1).Interior.ColorIndex = 3
}
}
$rowCount ++
}
$WorkBook.SaveAs($xlsFilePath, 18)
$objExcel.Quit()
