Click here to Skip to main content
15,867,568 members
Articles / All Topics
Technical Blog

Followup: DNS Lookup and Ping in Excel

Rate me:
Please Sign up or sign in to vote.
4.50/5 (2 votes)
18 Jan 2014CPOL1 min read 75K   1   2
Steve asked on my previous posting in regards to DNS Lookup in Excel if forward lookup could be done. (Find …Continue reading »

Steve asked on my previous posting in regards to DNS Lookup in Excel if forward lookup could be done. (Find the IP Address from the Hostname). Believe it or not I one up your request. How about DNS Forward and Reverse lookup as well as Ping!

  1. Press Alt-F11 in Excel to get to the VBA screen.
  2. Right click on the Project View
  3. Click Add Module
  4. Add the following snippet.
  5. Use:
    GetHostname("4.2.2.1") in any Excel cell.<br />
    or<br />
    Use: <code>GetIpAddress("www.google.com") in any Excel cell.<br />
    or
    Use:
    Ping("4.2.2.1") in any Excel cell.<br />

Note: If you have lengthy lists of IP addresses yoou plan on looking up keep in mind that the processs for looking up things against DNS is SLOW in comparison to a normal formula within Excel. Be wise with how you utilize this function.

In cases with duplicate addresses such as a ‘ip flow-cache’ output or ‘ip accounting’ output, you should probably create one lookup table in Excel on a separate tab with a deduplicated list of the IP Addresses with you will attempt loookup in DNS against. Then just use VLOOKUP(IP,HostLookupTable,Col,FALSE) to update the main page.

Once looked up I always copy and paste-as-text so excel doesn’t constantly lookup the list.

Credits: Many Thanks to AlonHircsh and Arkham79 for leaving little gem on experts-echange. It has been modified to include Arkham’s suggestion of including conversion of longIpAddress to stringIpAddress in the function.

' ###################################################################################
' ### Constants and Type Definitions
' ###
Private Const MIN_SOCKETS_REQD As Long = 1
Private Const WS_VERSION_REQD As Long = &H101
Private Const WS_VERSION_MAJOR As Long = WS_VERSION_REQD \ &H100 And &HFF&
Private Const WS_VERSION_MINOR As Long = WS_VERSION_REQD And &HFF&
Private Const SOCKET_ERROR As Long = -1
Private Const WSADESCRIPTION_LEN = 257
Private Const WSASYS_STATUS_LEN = 129
Private Const MAX_WSADescription = 256
Private Const MAX_WSASYSStatus = 128

Private Const IP_SUCCESS As Long = 0
Private Const IP_BUF_TOO_SMALL As Long = 11001
Private Const IP_DEST_NET_UNREACHABLE As Long = 11002
Private Const IP_DEST_HOST_UNREACHABLE As Long = 11003
Private Const IP_DEST_PROT_UNREACHABLE As Long = 11004
Private Const IP_DEST_PORT_UNREACHABLE As Long = 11005
Private Const IP_NO_RESOURCES As Long = 11006
Private Const IP_BAD_OPTION  As Long = 11007
Private Const IP_HW_ERROR As Long = 11008
Private Const IP_PACKET_TOO_BIG As Long = 11009
Private Const IP_REQ_TIMED_OUT As Long = 11010
Private Const IP_BAD_REQ As Long = 11011
Private Const IP_BAD_ROUTE As Long = 11012
Private Const IP_TTL_EXPIRED_TRANSIT As Long = 11013
Private Const IP_TTL_EXPIRED_REASSEM As Long = 11014
Private Const IP_PARAM_PROBLEM As Long = 11015
Private Const IP_SOURCE_QUENCH As Long = 11016
Private Const IP_OPTION_TOO_BIG As Long = 11017
Private Const IP_BAD_DESTINATION As Long = 11018
Private Const IP_GENERAL_FAILURE As Long = 11050

Private Const IP_FLAG_REVERSE As Long = 1
Private Const IP_FLAG_DF As Long = 2
Private Const IP_FLAG_REVERSE_DF As Long = 3

Private Const AF_UNSPEC = 0
Private Const AF_INET = 2
Private Const AF_NETBIOS = 17
Private Const AF_INET6 = 23

Private Type IN_ADDR
    s_b1 As Byte
    s_b2 As Byte
    s_b3 As Byte
    s_b4 As Byte
End Type

Private Type Hostent
    h_name As Long
    h_aliases As Long
    h_addrtype As Integer
    h_length As Integer
    h_addr_list As Long
End Type

Private Type IP_OPTION_INFORMATION
    TTL As Byte
    Tos As Byte
    Flags As Byte
    OptionsSize As Long
    OptionsData As String * 128
End Type

Private Type IP_ECHO_REPLY
    Address As IN_ADDR
    Status As Long
    RoundTripTime As Long
    DataSize As Integer
    Reserved As Integer
    data As Long
    Options As IP_OPTION_INFORMATION
End Type

Private Type WSAdata
    wVersion As Integer
    wHighVersion As Integer
    szDescription(0 To MAX_WSADescription) As Byte
    szSystemStatus(0 To MAX_WSASYSStatus) As Byte
    wMaxSockets As Integer
    wMaxUDPDG As Integer
    dwVendorInfo As Long
End Type

' ###################################################################################
' ### WINSOCK Native Function Imports
' ###
Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As Long)
Private Declare Sub GetPointer Lib "kernel32" Alias "RtlMoveMemory" (ByVal Destination As Long, Source As Any, ByVal Length As Long)
Private Declare Sub GetValue Lib "kernel32" Alias "RtlMoveMemory" (Destination As Any, ByVal Source As Long, ByVal Length As Long)
Private Declare Function lstrlenA Lib "kernel32" (ByVal Ptr As Any) As Long
Private Declare Function WSACleanup Lib "WSOCK32" () As Long
Private Declare Function WSAStartup Lib "WSOCK32" (ByVal wVersionRequired As Long, lpWSAdata As WSAdata) As Long
Private Declare Function inet_addr Lib "WSOCK32" (ByVal cp As String) As Long
Private Declare Function GetHostByAddr Lib "WSOCK32" Alias "gethostbyaddr" (haddr As Long, ByVal hnlen As Long, ByVal addrtype As Long) As Long
Private Declare Function GetHostByName Lib "WSOCK32" Alias "gethostbyname" (ByVal Hostname As String) As Long
Private Declare Function IcmpCreateFile Lib "icmp" () As Long
Private Declare Function IcmpCloseHandle Lib "icmp" (ByVal HANDLE As Long) As Boolean
Private Declare Function IcmpSendEcho Lib "icmp" ( _
    ByVal IcmpHandle As Long, _
    ByVal DestAddress As Long, ByVal RequestData As String, _
    ByVal RequestSize As Integer, _
    RequestOptns As IP_OPTION_INFORMATION, _
    ReplyBuffer As IP_ECHO_REPLY, _
    ByVal ReplySize As Long, _
    ByVal Timeout As Long _
    ) As Boolean

' ###################################################################################
' ### Private Utility Functions
' ###
Private Function HiByte(ByVal wParam As Integer)
    HiByte = wParam \ &H100 And &HFF&
End Function

Private Function LoByte(ByVal wParam As Integer)
    LoByte = wParam And &HFF&
End Function

Private Sub SocketsCleanup()
    If WSACleanup() <> ERROR_SUCCESS Then
        'Failed to cleanup sockets.
    End If
End Sub

Private Function SocketsInitialize() As Boolean
    Dim WSAD As WSAdata
    Dim sLoByte As String
    Dim sHiByte As String

    If WSAStartup(WS_VERSION_REQD, WSAD) <> ERROR_SUCCESS Then
        'The 32-bit Windows Socket is not responding.
        SocketsInitialize = False
        Exit Function
    End If

    If WSAD.wMaxSockets < MIN_SOCKETS_REQD Then
        SocketsInitialize = False
        Exit Function
    End If

    If LoByte(WSAD.wVersion) < WS_VERSION_MAJOR Or (LoByte(WSAD.wVersion) = WS_VERSION_MAJOR And HiByte(WSAD.wVersion) < WS_VERSION_MINOR) Then
        sHiByte = CStr(HiByte(WSAD.wVersion))
        sLoByte = CStr(LoByte(WSAD.wVersion))
        SocketsInitialize = False
        Exit Function
    End If
    'must be OK, so lets do it
    SocketsInitialize = True
End Function

' ###################################################################################
' ### Exposed Excel Worksheet Functions
' ###
Public Function GetHostName(ByVal Address As String) As String
    Dim lLength As Long
    Dim lRet As Long

    If Not SocketsInitialize() Then
        GetHostName = "WINSOCK_FAILURE"
        Exit Function
    End If

    lRet = GetHostByAddr(inet_addr(Address), 4, AF_INET)
    If lRet <> 0 Then
        CopyMemory lRet, ByVal lRet, 4
        lLength = lstrlenA(lRet)
        If lLength > 0 Then
            GetHostName = Space$(lLength + 1)
            CopyMemory ByVal GetHostName, ByVal lRet, lLength
        End If
    Else
        GetHostName = ""
    End If

    SocketsCleanup
End Function

Public Function GetIpAddress(ByVal Hostname As String) As String
    Dim hFile As Long
    Dim hHostent As Hostent
    Dim lRet As Long
    Dim AddrList As Long
    Dim ptrAddr As Long
    Dim ptrStrIp As Long
    Dim strIpAddress As String
    Dim Addr As IN_ADDR

    If Not SocketsInitialize() Then
        GetIpAddress = "WINSOCK_FAILURE"
        Exit Function
    End If

    'Get Host IP Address by Name
    lRet = GetHostByName(Hostname + String(64 - Len(Hostname), 0))
    If lRet <> SOCKET_ERROR Then

        GetValue hHostent.h_name, GetHostByName(Hostname + String(64 - Len(Hostname), 0)), Len(hHostent)
        GetValue ptrAddr, ByVal hHostent.h_addr_list, 4

        For ptrAddr = ptrAddr To (ptrAddr + lstrlenA(hHostent.h_addr_list) - 1) Step 4
            GetValue Addr, ptrAddr, 4 'Debug: cast as IP Address
            GetIpAddress = GetIpAddress + " " + CStr(Addr.s_b1) + "." _
                                              + CStr(Addr.s_b2) + "." _
                                              + CStr(Addr.s_b3) + "." _
                                              + CStr(Addr.s_b4)
        Next

        GetIpAddress = LTrim(GetIpAddress)
    End If
    SocketsCleanup
End Function

'Returns Error code as String or Round-Trip Response Time in msecs.
Public Function Ping(ByVal Hostname As String, _
                     Optional TTL As Long = 255, _
                     Optional msTimeout As Long = 2000, _
                     Optional packetLength As Long = 32, _
                     Optional DoNotFragment As Boolean = True) As Variant
    Dim hFile As Long
    Dim hHostent As Hostent
    Dim AddrList As Long
    Dim Address As Long
    Dim strIpAddress As String
    Dim OptInfo As IP_OPTION_INFORMATION
    Dim EchoReply As IP_ECHO_REPLY
    Dim StatusString As String
    Dim lRet As Long

    If Not SocketsInitialize() Then
        Ping = "WINSOCK_INIT_FAIL"
        Exit Function
    End If

    'Get Host IP Address by Name
    lRet = GetHostByName(Hostname + String(64 - Len(Hostname), 0))
    If lRet <> SOCKET_ERROR Then
        GetValue hHostent.h_name, lRet, Len(hHostent)
        GetValue AddrList, hHostent.h_addr_list, 4
        GetValue Address, AddrList, 4
    Else
        Ping = "NS_SOCKET_ERROR"
        Exit Function
    End If

    'Attempt to Create File Handle to store response in.
    hFile = IcmpCreateFile()
    If hFile = 0 Then
        Ping = "FILE_HANDLE_FAILURE"
        Call WSACleanup 'Terminate WinSock
        Exit Function
    Else
        'Set Options
        OptInfo.TTL = TTL 'Sets Time-to-Live (Limits Router Hops between host and destination)
        OptInfo.Tos = 0 'Silently ignored by WinSock. See: http://support.microsoft.com/kb/248611
        If DoNotFragment Then
            OptInfo.Flags = IP_FLAG_DF
        Else
            OptInfo.Flags = 0
        End If

        'Send ICMP Echo
        If IcmpSendEcho(hFile, Address, String(packetLength, "A"), packetLength, OptInfo, EchoReply, Len(EchoReply) + 8, msTimeout) Then
            strIpAddress = CStr(EchoReply.Address.s_b1) + "." _
                         + CStr(EchoReply.Address.s_b2) + "." _
                         + CStr(EchoReply.Address.s_b3) + "." _
                         + CStr(EchoReply.Address.s_b4)
        End If

        Select Case EchoReply.Status
                    Case IP_SUCCESS
                'The ICMP echo was delivered successfully with the proper response.
                Ping = EchoReply.RoundTripTime

            Case IP_BUF_TOO_SMALL
                'The reply buffer was too small.
                Ping = strIpAddress & ":BUF_TOO_SMALL"

            Case IP_DEST_NET_UNREACHABLE
                'The destination network was unreachable.
                Ping = strIpAddress & ":DEST_NET_UNREACHABLE"

            Case IP_DEST_HOST_UNREACHABLE
                'The destination host was unreachable.
                Ping = strIpAddress & ":DEST_HOST_UNREACHABLE"

            Case IP_DEST_PROT_UNREACHABLE
                'The destination protocol was unreachable.
                Ping = strIpAddress & ":DEST_PROT_UNREACHABLE"

            Case IP_DEST_PORT_UNREACHABLE
                'The destination port was unreachable.
                Ping = strIpAddress & ":DEST_PORT_UNREACHABLE"

            Case IP_NO_RESOURCES
                'Insufficient IP resources were available.
                Ping = strIpAddress & ":NO_RESOURCES"

            Case IP_BAD_OPTION
                'A bad IP option was specified.
                Ping = strIpAddress & ":BAD_OPTION"

            Case IP_HW_ERROR
                'A hardware error occurred.
                Ping = strIpAddress & ":HW_ERROR"

            Case IP_PACKET_TOO_BIG
                'The packet was too big.
                Ping = strIpAddress & ":PACKET_TOO_BIG"

            Case IP_REQ_TIMED_OUT
                'The request timed out.
                Ping = strIpAddress & ":REQ_TIMED_OUT"

            Case IP_BAD_REQ
                'A bad request.
                Ping = strIpAddress & ":BAD_REQ"

            Case IP_BAD_ROUTE
                'A bad route.
                Ping = strIpAddress & ":BAD_ROUTE"

            Case IP_TTL_EXPIRED_TRANSIT
                'The time to live (TTL) expired in transit.
                Ping = strIpAddress & ":TTL_EXPIRED_TRANSIT"

            Case IP_TTL_EXPIRED_REASSEM
                'The time to live expired during fragment reassembly.
                Ping = strIpAddress & ":TTL_EXPIRED_REASSEM"

            Case IP_PARAM_PROBLEM
                'A parameter problem.
                Ping = strIpAddress & ":PARAM_PROBLEM"

            Case IP_SOURCE_QUENCH
                'Datagrams are arriving too fast to be processed and datagrams may have been discarded.
                Ping = strIpAddress & ":SOURCE_QUENCH"

            Case IP_OPTION_TOO_BIG
                'An IP option was too big.
                Ping = strIpAddress & ":OPTION_TOO_BIG"

            Case IP_BAD_DESTINATION
                'A bad destination.
                Ping = strIpAddress & ":BAD_DESTINATION"

            Case IP_GENERAL_FAILURE
                'A general failure. This error can be returned for some malformed ICMP packets.
                Ping = "GENERAL_FAILURE"

            Case Else
                'An unknown error occured.
                Ping = "UNKNOWN_FAILURE"
        End Select
    End If

    Call IcmpCloseHandle(hFile) 'Close File Handle

    SocketsCleanup
End Function

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
United States United States
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionInput sanitization Pin
member1911-Aug-17 16:57
member1911-Aug-17 16:57 
QuestionGetting address from other cell Pin
eulergui14-Nov-15 13:23
eulergui14-Nov-15 13:23 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.