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).
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.
‘—-First clear the cells in Row B—————–
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
ActiveSheet.Cells(introw, 2).Font.Color = RGB(200, 0, 0)
ActiveSheet.Cells(introw, 2).Value = “Offline”
MsgBox “Script Completed”
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
Ping = False
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.
Hi, how would one add a retry on the ping for x amount of times before updating as offline?
need to loop ping, like 1 to 10 IP ping finished after again start 1 to 10
Hi – how do I amend the code to scan from coloum D10 downwards and results in I10 downwards?
Thank you very much, it is very useful for me
I use PingView
For me it didnt work , when i verify most server were offline but the script output shows online lol
This worked great. Thanks for sharing!
Pingback: Excel Makro ile Host Alan Adı Listesinden IP Adresi Listesini Alma – Haruncetin.com.tr