Solved

Ping list of computers in excel and return result

Posted on 2012-04-10
4
2,122 Views
Last Modified: 2013-12-06
Hello

I have a large excel spreadsheet which contains a list of computer names and IP Addresses

Is there a way to have excel Ping each computer name and then return a result of "Online" or "offline" in a new column next to each computer name, depending on the result of the ping?

Otherwise i have to do this manually

Thanks

Andrew
0
Comment
Question by:Wolf
  • 2
4 Comments
 
LVL 14

Expert Comment

by:luconsta
ID: 37827774
See if this EE answered problem is what you need: ping all computer from sheet list...
0
 
LVL 26

Accepted Solution

by:
redmondb earned 500 total points
ID: 37829177
Hi, Andrew.

Please see attached. Macro is...
Sub Ping_Check()
' Based on http://social.technet.microsoft.com/Forums/en-US/ITCG/thread/e59a38e1-eaf0-4b13-af10-fd4be559f50f/
Dim oPing As Object
Dim oRetStatus As Object
Dim xCell As Range
Dim xLast_Row As Long
Dim xWork1 As String
Dim xWork2 As String

xLast_Row = ActiveSheet.Range("A1").SpecialCells(xlLastCell).Row

Application.ScreenUpdating = False
    
    For Each xCell In Range("A2:A" & xLast_Row)
        If xCell = "" Then
            xCell.Offset(0, 1) = ""
        Else
            Set oPing = GetObject("winmgmts:{impersonationLevel=impersonate}").ExecQuery("select * from Win32_PingStatus where address = '" & xCell & "'")
            For Each oRetStatus In oPing
                If IsNull(oRetStatus.StatusCode) Or oRetStatus.StatusCode <> 0 Then
                    xCell.Offset(0, 1) = "N/A"
                    '11001   Buffer Too Small
                    '11002   Destination Net Unreachable
                    '11003   Destination Host Unreachable
                    '11004   Destination Protocol Unreachable
                    '11005   Destination Port Unreachable
                    '11006   No Resources
                    '11007   Bad Option
                    '11008   Hardware Error
                    '11009   Packet Too Big
                    '11010   Request Timed Out
                    '11011   Bad Request
                    '11012   Bad Route
                    '11013   TimeToLive Expired Transit
                    '11014   TimeToLive Expired Reassembly
                    '11015   Parameter Problem
                    '11016   Source Quench
                    '11017   Option Too Big
                    '11018   Bad Destination
                    '11032   Negotiating IPSEC
                    '11050   General Failure
                Else
                    xCell.Offset(0, 1) = oRetStatus.ResponseTime & " ms ; " & oRetStatus.ResponseTimeToLive
                End If
            Next
        End If
    Next
    
Application.ScreenUpdating = True

End Sub

Open in new window

Regards,
Brian.Ping-Check.xlsm
0
 
LVL 1

Author Closing Comment

by:Wolf
ID: 37841620
Perfect, thanks
0
 
LVL 26

Expert Comment

by:redmondb
ID: 37841769
Thanks Andrew.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question