CODEWITHSUNDEEP
excelpowershell
Josh C
Josh C

Reputation: 11

Pass/Fail Test-Connection Into Excel

I'm new to Powershell. I'm trying to automate a few things. My biggest obstacle right now is trying to get a failed Test-Connection to export into Excel with a -Foreground color. Green for "Online" and red for "Offline."

Another issue, it does ping a computer offline, it writes to the Computer Pane as multiple exceptions. Is there a way to make it do it only one time?

I would like to have the "Offline" ping in Excel (1,4).

Any help will be definitely appreciated! If you don't mind explaining how and why it works too, that would be perfect.

Thank you.

$Comps = Get-Content -Path "FilePath\ComputerList.txt"
$Date = Get-Date -Format MM-dd-yyyy

#Stop-Process -Name "Excel" -ErrorAction SilentlyContinue

#Suspends activity for a # of seconds.
Start-Sleep 3

$XL = New-Object -comobject Excel.Application
$xL.displayAlerts = $false
 
$WB = $XL.Workbooks.Add()
$WS = $WB.Worksheets.Item(1)

$WS.Cells.Item(1,1) = "Date"
$WS.Cells.Item(1,2) = "IP Address"
$WS.Cells.Item(1,3) = "Computer Name - Online"
$WS.Cells.Item(1,4) = "Computer Name - Offline"
 
$counter = 2
$CompStatus = @()

$Comps = Get-Content -Path "FilePath\ComputerList.txt"
$Processes = Test-Connection -ComputerName $Comps -Count 1| select @{n='TimeStamp';e={Get-Date}}, Address, ProtocolAddress 

foreach($Proc in $Processes) {
If(Test-Connection $Comps){ 
Test-Connection $Comp
}
    write-host "$Comp is online" -ForegroundColor Green
    $WS.cells.item(2,1) = $Proc.TimeStamp
    $WS.cells.item($counter,2) = $Proc.ProtocolAddress
    $WS.cells.item($counter,3) = $Proc.Address

    $WS.columns.autofit()
    $counter++

    }else{
    write-host "$Comp is offline" -ForegroundColor Red
    $WS.cells.item($counter,4) = $Proc.$CompStatus += $Comp + "Offline"
}

$XL.Visible = $True

$XL.ActiveWorkbook.SaveAs("FilePath\ComputerListPinged_$Date")

Upvotes: 0

Views: 151

Answers (1)

Santiago Squarzon
Santiago Squarzon

Reputation: 60633

You really should have a look at Douglas Finke's ImportExcel Module, it has a Conditional Formatting parameter that works fine for what you need. You can also set RGB colors on it same as you would see them on Excel (like the classic light green background with green font, and light red background with red fount, etc). I think it will make your life easier.

Here are a few examples: https://dfinke.github.io/powershell/2020/05/02/PowerShell-Excel-and-Conditional-Formatting.html

The module itself is pretty easy to use and there are plenty of guides on Google.

Upvotes: 1

Related Questions