Convert code from Excel Macro to PowerShell

Question

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

Jeno Laszlo Jeno Laszlo · Accepted Answer · 2015-05-20T10:02:05

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

Convert code from Excel Macro to PowerShell

1 Answers