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”.
Press the “Win” key and the “R” key at the same time, type “cmd”, and then press the “OK” button.
Enter “ping ○○○○” (the host name or IP address of the destination).
Press the “Enter” key.
Press the ⌘ and Space keys at the same time to open Spotlight search, then type “Terminal” to open Terminal.
Enter ping ○○○○ (the hostname or IP address of the destination).
Press the Enter key.
How to run the ping command from Excel and record the response results
- Enter the IP address for which you want to check the response in column A of Excel.
- Open “Visual Basic” from the “Development” tab of Excel.
If you do not see the “Development” tab, please refer to this article first.
- 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
- Close the Visual Basic screen and select “Macro”, also in the Development tab.
- 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.
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.
I am Japanese, and my computer is set up in Japanese. Because I am Japanese and my computer is set up in Japanese, there may be some differences in the names of buttons and windows.
I try to keep the information on this site (tamocolony) up-to-date, but please be aware that the information on this site may not be the most up-to-date, or the information itself may be incorrect. We take no responsibility for the content of this site. If you have any questions about an article or need to make corrections, please contact us via the Contact Us page.