I need an automatic pinging tool because I have 70 computers in my network and I am sick of pinging them through cmd
every time. So I have this excel sheet which I have made work (mostly).
Column A: contains computer names written manually by me
Column B: it should (but doesn't) write out the IP addresses of computers in column A
Column C: shows if computer is online or offline based on ping.
Column D: Something I put manually as well, to know who sits behind which computer, just usernames.
I have two buttons, ping
and stop ping
. They work fine. How can I by pressing the ping
button, make it go through all the computers in column A
and show me both their IPs and if they are online or not, all at once?
Of course, it doesn't have to be done through excel, if you have a better solution with great view of all 3 things I need to see clearly (comp name
, comp ip
, online/offline
) and the 4th thing with usernames would be nice to have.
Please help me on this, I am desperate:
Dim objshell, boolcode
Set objshell = CreateObject("Wscript.Shell")
boolcode = objshell.Run("ping -n 1 -w 1000 " & strip, 0, True)
If boolcode = 0 Then
Ping = True
Else
Ping = False
End If
End Function
'_________________________
Sub PingSystem()
Dim strip As String
Do Until Sheet1.Range("G9").Value = "STOP"
Sheet1.Range("G9").Value = "Ping"
For introw = 1 To ActiveSheet.Cells(65536, 2).End(xlUp).Row
strip = ActiveSheet.Cells(introw, 2).Value
If Ping(strip) = True Then
ActiveSheet.Cells(introw, 3).Interior.ColorIndex = 0
ActiveSheet.Cells(introw, 3).Font.Color = RGB(0, 0, 0)
ActiveSheet.Cells(introw, 3).Value = "Online"
ActiveSheet.Cells(introw, 3).Font.Color = RGB(0, 200, 0)
Else
ActiveSheet.Cells(introw, 3).Interior.ColorIndex = 0
ActiveSheet.Cells(introw, 3).Font.Color = RGB(200, 0, 0)
ActiveSheet.Cells(introw, 3).Value = "Offline"
ActiveSheet.Cells(introw, 3).Interior.ColorIndex = 6
End If
If Sheet1.Range("G9").Value = "STOP" Then
Exit For
End If
Next
Loop
Sheet1.Range("G9").Value = "Stop ping"
End Sub
Sub stop_ping()
Sheet1.Range("G9").Value = "STOP"
End Sub
This worked for me
Sub temp()
Set WshShell = CreateObject("WScript.Shell")
RowCount = Worksheets("Sheet1").UsedRange.Rows.Count
For i = 1 To RowCount
Url = Worksheets("Sheet1").Cells(i, 1).Value
cmd = "ping " + Url
Set WshShellExec = WshShell.Exec(cmd)
result = WshShellExec.StdOut.ReadAll
Worksheets("Sheet1").Cells(i, 2).Value = getIP(result)
If (InStr(result, "Received = 4")) Then
Worksheets("Sheet1").Cells(i, 3).Value = "Online"
End If
Next
End Sub
Function getIP(result)
ip = Split(result, vbNewLine)(1)
startIndex = InStr(ip, "[") + 1
endIndex = InStr(ip, "]")
getIP = Mid(ip, startIndex, endIndex - startIndex)
End Function