Powershell: How to change cell color based on text?

PowerShell Help
1

I have below script which gives list of local admin members.

I was wondering if you techies can help me, highlight “Server - $SName -> ########Cannot Access” output to be in red background color cell in result excel file.

Function LocalAdmin

{

$GFile = New-Item -type file -force “C:\Scripts\SGroupMemberDetails.xls”

Import-Csv “C:\Scripts\Servers.csv” | ForEach-Object {

$SName = $_.ServerName

$AdminPath = Test-Path "\$SName\admin$"

If ($AdminPath -eq $TRUE)

{

Write-host -nonewline “Collecting Admin info from $SName…”

“Server Name - $SName” | Out-File $GFile -encoding ASCII -append

$group = ADSI

$GMembers = $group.psbase.invoke(“Members”)

$GMembers | ForEach-Object {$.GetType().InvokeMember(“Name”,‘GetProperty’, $null, $, $null) | Out-File $GFile -encoding ASCII -append}

Write-host “…Done!”

}

else

{

write-host -fore Red “Cannot Acces $SName”

out-file -inputObject “Server - $SName -> ########Cannot Access” -filepath $GFile -encoding ASCII -append

}

$GMembers = “”

}

}

LocalAdmin

2

Hello there!

Others might provide you a more usable solution. Just wanted to give you a heads up on a module that might help.

First off, props to Doug Finke for the ImportExcel module and idea for using the EPPlus library : D

Check out PSExcel. In fact, there is a specific function for formatting cells, and another for searching cell contents (which could identify the row/column to format).

Here’s an example. You might need to change your methodology. Need to add an append parameter to export… thanks for the reminder!

Good luck!

3

Thanks Warren,
I have downloaded and imported the PSExcel module. But when I run the script proposed by you, I see bundle of errors (e.g. “Row out of range” \Search-CellValue.ps1:149 char:47).
I am using PS 2.0, as I have windows 2008 servers. do you think the PS version is the cause or something else?

4

Hi again!

That would indeed be it. Modified the code in the previous post to generate a demo file with support for PS2.

If you can, I would recommend pushing for the latest .NET Framework / Windows Management Framework (includes PowerShell) combination. .NET 4.5 and PowerShell 4.

Even if you can only get buy in for your own systems and management servers, it’s well worth it. If I’m writing tools at work for our support org nowadays, they use the latest PowerShell, or they don’t use the tools : )

Edit: Side note, added a -Append switch on Export-XLSX. It would be inefficient, but you can potentially append a row at a time.

Cheers!

Warren