How to record ping response results in Excel

When you are working as an internal SE, network errors may occur, and in order to isolate the problem, you need to check if you are connected to the network by using the ping command.

Since internal SEs use private IP addresses, there is a limit to the number of IP addresses they can use. This is also useful to check if the IP address is really being used.

In this article, I’ll show you how to record the success or failure of ping communication by writing the IP address in Excel.

How does ping communication work?

The method of sending a ping varies depending on the OS; you can enter the host name or IP address after the ping, but if you want to send a test ping at home, you can use the URL of your homepage, such as “yahoo.co.jp” or “tamocolony.com”.

How to run the ping command from Excel and record the response results

1. Enter the IP address for which you want to check the response in column A of Excel.
2. Open “Visual Basic” from the “Development” tab of Excel.
   
   If you do not see the “Development” tab, please refer to this article first.
3. Double-click on “ThisWorkbook” and paste the code below.
   

   Sub ping()
       For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
           cmd = "cmd.exe /c ping " & Cells(i, 1)
           Set wsh = CreateObject("WScript.Shell")
           If wsh.Run(cmd, vbNormalFocus, True) Then
               Cells(i, 2).Value = "失敗"
           Else
               Cells(i, 2).Value = "成功"
           End If
               Set wsh = Nothing
       Next
   End Sub

4. Close the Visual Basic screen and select “Macro”, also in the Development tab.
   
5. There is a macro called “ThisWorkbook.ping”, select it and click the [Execute] button, and the result of the ping response will be written in column B of Excel.

conclusion

There is a wonderful software called ExPing in Windows, and you can extract the response results of this software into Excel.

However, there are some advantages to pasting the list of IP addresses into Excel from the beginning, such as ease of processing, so please make effective use of it.

https://www.xdomain.ne.jp/