How to Ping a Computer from Excel
Oddly enough, I often find myself needing to ping a list of computers from Excel. Usually it’s part of some asset management or software deployment exercise. If it’s a small list, I’ll often do it manually. Medium and larger lists usually warrant some type of batch file hitting the list and I just keep an eye on the results or have them pipe back to a text file. Recently I developed an Excel sub that would do the work for me and then return the results back to a column for me to sort and filter by.
This could certainly be cleaned up a bit, there’s probably a better way to query the Win32_PingStatus class with VBA and I could do a better job commenting, but this will do me fine for now. After selecting a column of computer names, I execute the sub and then enter a column number to return the results to. The result is a list of computers with a list of ping status somewhere next to them.
You’re free to use this as you see fit and to distribute as you like. I would appreciate if you’d leave the header intact and link back here if possible.
Update: I have updated this function to version 1.1 with some new functionality. Be sure to check out my post on this update here.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 | Public Sub PingCell() ' Version: 0.1 (First Release) ' Excel Version: 2003 ' Language: English ' Description: Function that pings a computer and returns the result to an adjacent column ' http://www.TechnicallyChris.com/ ' 30-Jun-2009: Created Function Dim column As Integer Dim strStatus As String Dim objPing As Object Dim objPingStatus As Object Dim r As Range ' Ask user for column number to return results to column = InputBox("Please select a column NUMBER to start the dump:", "Ping Systems") For Each r In Application.Selection Cells(r.Row, column + 0) = "Pinging ..." Set objPing = GetObject("winmgmts:{impersonationLevel=impersonate}").ExecQuery("select * from Win32_PingStatus where address = '" & r.Value & "'") ' Call DoEvents to stop this thing from hanging Excel on long lists DoEvents For Each objPingStatus In objPing ' Status Codes: http://msdn.microsoft.com/en-us/library/aa394350%28VS.85%29.aspx If IsNull(objPingStatus.statuscode) Then ' Not from MSDN strStatus = "Unable to Resolve Host" Else Select Case objPingStatus.statuscode Case 0 strStatus = "Success" Case 11002 strStatus = "Destination Net Unreachable" Case 11003 strStatus = "Destination Host Unreachable" Case 11004 strStatus = "Destination Protocol Unreachable" Case 11005 strStatus = "Destination Port Unreachable" Case 11006 strStatus = "No Resources" Case 11007 strStatus = "Bad Option" Case 11008 strStatus = "Hardware Error" Case 11009 strStatus = "Packet Too Big" Case 11010 strStatus = "Request Timed Out" Case 11011 strStatus = "Bad Request" Case 11012 strStatus = "Bad Route" Case 11013 strStatus = "TimeToLive Expired Transit" Case 11014 strStatus = "TimeToLive Expired Reassembly" Case 11015 strStatus = "Parameter Problem" Case 11016 strStatus = "Source Quench" Case 11017 strStatus = "Option Too Big" Case 11018 strStatus = "Bad Destination" Case 11032 strStatus = "Negotiating IPSEC" Case 11050 strStatus = "General Failure" Case Else strStatus = "Unknown Ping Result (" & objPingStatus.statuscode & ")" End Select End If Cells(r.Row, column) = strStatus Next Next r End Sub |
Wow! Thanks for sharing this is great. I need to check the status of 1000 machine before installing and this did the trick.