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() 