I just assembled (since I did not write it from scratch) a small but useful script which can be used directly from excel. You really don’t need to run anything on command prompt or create an input/output file.The script doesn’t even need admin privileges to run. The result from the script would be stored real time in the same excel sheet. Just paste the list of computer names/IP Addresses /Urls in the sheet and hit the execute button.

 

You can download the excel file directly
from the below link(Don’t forget to change the file extension to .xlsm).

 

Ping_Script

Step 1 – Preparing Excel to run macro

You can refer to my article “Preparing excel to run sysadmin scripts” and follow the steps provided.

Step 2 – Copy the script into the “Microsoft Visual Basic for Applications” window which opens once you click on “view code” option under developers tab.

You need to copy the below code, save it and assign the sub (PingSystem) to the execute button.

‘—————————————————————————————

Sub PingSystem()

‘—-First clear the cells in Row B—————–

ClearStatusCells

‘—————————————————

Dim strcomputer As String

Application.ScreenUpdating = True

For introw = 2 To ActiveSheet.Cells(65536, 1).End(xlUp).Row

strcomputer = ActiveSheet.Cells(introw, 1).Value

‘————Call ping function and post the output in the adjacent cell——-

If Ping(strcomputer) = True Then

strpingtest = “Online”

ActiveSheet.Cells(introw, 2).Value = strpingtest

Else

ActiveSheet.Cells(introw, 2).Font.Color = RGB(200, 0, 0)

ActiveSheet.Cells(introw, 2).Value = “Offline”

End If

Next

MsgBox “Script Completed”

End Sub

Function Ping(strcomputer)

Dim objshell, boolcode

Set objshell = CreateObject(“wscript.shell”)

boolcode = objshell.Run(“ping -n 1 -w 1000 ” & strcomputer, 0, True)

If boolcode = 0 Then

Ping = True

Else

Ping = False

End If

End Function

Sub ClearStatusCells()

Range(“B2:B1000”).Clear

End Sub’———————————————————————————————

Step 3 – Perform a test run

You need to make sure that all your computer names start from cell A2 vertically downwards . The output will be saved in cell B2 respectively.

Video below shows the brief steps and way to run the script.