Ping list of servers in Excel

PadreBinde · May 20, 2009

****** http-equiv="Content-Type" content="text/html; charset=utf-8">****** name="ProgId" content="Word.Document">****** name="Generator" content="Microsoft Word 12">****** name="Originator" content="Microsoft Word 12"><link rel="File-List" href="file:///C:%5CUsers%5CPatrick%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_filelist.xml"><link rel="Preview" href="file:///C:%5CUsers%5CPatrick%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_preview.wmf"><link rel="themeData" href="file:///C:%5CUsers%5CPatrick%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_themedata.thmx"><link rel="colorSchemeMapping" href="file:///C:%5CUsers%5CPatrick%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_colorschememapping.xml"><style>  </style> Hello,

I am trying to solve a problem but unfortunately my MSBasic skills are very very limited.
I have a list of about 100 www addresses that I would like to check. I a column next to the 100 addresses I want to have a column with an indication whether the server is online or offline.

I found this problem in forums but I did not manage to use any of the solutions given. Does anyone have a ready to use macro for Excel that i could use?

Thank you very much for your help

Padre<o

></o

>
<o

> </o

>

John_w · May 20, 2009

Try this, based on http://scriptorium.serve-it.nl/view.php?sid=68

I've used early binding, which will be much faster for 100 addresses, so you need to set the references as noted in the comment at the top of the code. Put this code in a standard module.

Code:

Option Explicit

'Requires references to Microsoft Scripting Runtime and Windows Script Host Object Model.
'Set these in Tools - References in VB Editor.


Public Function PingResult(sHost As String) As String
    Dim sResponse As String
    
    sResponse = sPing(sHost)
    If InStr(sResponse, "Reply from") Then
        PingResult = "Online"
    Else
        PingResult = "Offline"
    End If
    
End Function


Private Function sPing(sHost As String) As String

    Dim oFSO As FileSystemObject, oShell As WshShell, oTempFile As TextStream
    Dim sFilename As String
    
    Set oFSO = New FileSystemObject
    Set oShell = New WshShell
    
    sFilename = oFSO.GetTempName
    oShell.Run "%comspec% /c ping -n 1 " & sHost & " > " & sFilename, 0, True
    
    Set oTempFile = oFSO.OpenTextFile(sFilename, ForReading)
    sPing = oTempFile.ReadAll
    oTempFile.Close
    oFSO.DeleteFile (sFilename)

End Function
 

Public Sub TestPing()
    MsgBox sPing(InputBox("Enter hostname to test"))
End Sub

You can get the result of a ping into a cell by using =PingResult(A1), where A1 contains the hosthame (for example, www.mrexcel.com), or run TestPing for ad hoc tests, or call sPing() in your own VBA code.

PadreBinde · May 21, 2009

This is exactly what I wal looking for

))

I tried it out but it the PingResult(...) always gives back "offline"
When I try the TestPing makro it works fine.
Do I have to do anything so the PingResult will give back the correct ping result? It works very fast, so I have the feeling that it doesn't actually do a ping.

thanks
Padre

John_w · May 21, 2009

I tested PingResult() before I posted the code and it works for me for online and offline hostnames. PingResult() simply checks whether the ping response contains the text "Reply from" and if so returns 'Online'. Your ping command might be returning a different response, therefore for me to see the response text, open a command window and enter the following commands:

ping -n 1 www.mrexcel.com > test.txt
notepad test.txt

Copy and paste the contents of notepad into this thread. Do another:

ping -n 1 www.microsoft.com > test.txt
notepad test.txt

Copy and paste the contents of notepad into this thread.

The mrexcel ping contains "Reply from 216.92.17.166: bytes=32 time=111ms TTL=52" and the microsoft one contains "Request timed out" for me.

PadreBinde · May 21, 2009

You were right, I use a German version of Windows so the text is "Antwort von". I corrected the source code and it works fine now.

Thank you so much.

Padre

roi_zazon · Jun 7, 2012

Recommend visit in:
http://www.mrexcel.com/forum/showthread.php?p=3178336#post3178336

i think its socks
in excel its better

kames · Apr 19, 2013

Thx John_W - great job!
I was having small problem in polish version of Office and Windows. My ping command was returning a different response, so I use:

If InStr(sResponse, "TTL")

and it's always work fine

Ping list of servers in Excel

PadreBinde

New Member

Excel Facts

John_w

MrExcel MVP

PadreBinde

New Member

John_w

MrExcel MVP

PadreBinde

New Member

roi_zazon

New Member

kames

New Member

Similar threads

Forum statistics

Share this page

Ping list of servers in Excel

PadreBinde

New Member

Excel Facts

John_w

MrExcel MVP

PadreBinde

New Member

John_w

MrExcel MVP

PadreBinde

New Member

roi_zazon

New Member

kames

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock