How do I resolve IP Addresses to Hostnames in Excel

Factual Questions
1

In Excel, I’d like a way to take an IP address in one cell and get the associated hostname in another cell. Ideally the second cell would read “=resolv(A1)” or such.

I’m familiar with how to create custom formulas in Visual Basic.

Possibility one: I have an MS-DOS program to do what I want. So, if in Visual Basic I could send an argument to an MS-DOS program and receive it’s response I’d be set. But I don’t see a way to call an MS-DOS program (and receive the response). True?

Possibility two: I see that Visual Basic allows you to call DLLs and COM components. I’m not an expert on building those. I assume if I can find such a component that does reverse DNS resolution I could use it. Correct? Does anyone know where to find such a component? Is such functionality already built into Windows?

Possibility other: your call.

I am using Windows 2000 and Excel 2000.

Thanks.

2

doesn’t windows have gethostbyaddr() & gethostbyname() functions?

3

You probably don’t want to call a DOS program, because typically the shell commands used in VB/VBScript will orphan a lot of DOS windows and clutter up your system.

Windows does have a gethostbyaddr accessible in the wsock32.dll. You can find documentation and sample code on the Microsoft site searching for keywords like “gethostbyaddr” and “vb”. You’ll want some sample code because it’s a bit convoluted. The steps go something like this:

  1. given an IP address, use inet_addr function call (also in wsock32) to convert the IP to the proper format.
  2. use gethostbyaddr to resolve IP to domain name.
  3. use MemCopy to read the returned buffer.

I’ve skipped the traditional call to the Opal function in step #3 because it returns a void value on all modern systems.

4

Any similar way in Unix? Asides from endless calls to whois and nslookup?

5

The short answer is “yes”. The long answer depends on the language you’re using on Unix. There is a gethostbyaddr function in Perl with contortions similar to the VB implementation. Java has net.InetAddress. I think gethostbyaddr is also implemented in C/C++, but I don’t have details handy.

6

Thanks all. micco is right on the money; it is a bit convoluted. If anyone is interested, here’s the code:


Option Explicit

Private Const ERROR_SUCCESS       As Long = 0
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 MIN_SOCKETS_REQD    As Long = 1

Public WSError As Long

Private Type HOSTENT
    hName      As Long
    hAliases   As Long
    hAddrType  As Integer
    hLen       As Integer
    hAddrList  As Long
End Type

Private Type WSADATA
    wVersion As Integer
    wHighVersion As Integer
    szDescription As String * 257
    szSystemStatus As String * 129
    iMaxSockets As Long
    iMaxUdpDg As Long
    lpVendorInfo As Long
End Type

Private Declare Function gethostbyaddr Lib "wsock32.dll" _
    (ByRef dwHost As Long, ByVal hLen As Integer, ByVal aType As Integer) As Long

Private Declare Function WSAGetLastError Lib "wsock32.dll" () As Long
Private Declare Function WSAStartup Lib "wsock32.dll" _
    (ByVal wVersionRequired As Long, lpWSADATA As WSADATA) As Long
Private Declare Function WSACleanup Lib "wsock32.dll" () As Long

Private Declare Function inet_addr Lib "wsock32.dll" (ByVal szHost As String) As Long
Private Declare Function lstrlen Lib "kernel32" (ByVal lpString As Long) As Long
Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" _
    (hpvDest As Any, ByVal hpvSource As Any, ByVal cbCopy As Long)

Public Function ReverseLookup(ByRef sIPAddr As Variant) As String
Dim dwIPAddr    As Long
Dim lpHost      As Long
Dim HOST        As HOSTENT
Dim WSErr        As Long

    ReverseLookup = ""
    If Not SocketsInitialize() Then Exit Function

    If IsNull(sIPAddr) Then sIPAddr = ""
    dwIPAddr = inet_addr(CStr(sIPAddr))

    lpHost = gethostbyaddr(dwIPAddr, Len(dwIPAddr), 2)
    If lpHost = 0 Then
        GoTo Err_ReverseLookup_Winsock
    Else
        CopyMemory HOST, lpHost, Len(HOST)
        ReverseLookup = PointerToString(HOST.hName)
    End If

Exit_ReverseLookup:
    SocketsCleanup
    Exit Function

Err_ReverseLookup_Winsock:
    WSError = WSAGetLastError()
    GoTo Exit_ReverseLookup
End Function

Private Function PointerToString(lpString As Long) As String
Dim Buffer() As Byte
Dim nLen As Long

    If lpString Then
       nLen = lstrlen(lpString)
       If nLen Then
          ReDim Buffer(0 To (nLen - 1)) As Byte
          CopyMemory Buffer(0), ByVal lpString, nLen
          PointerToString = StrConv(Buffer, vbUnicode)
       End If
    End If
End Function

Private Function SocketsInitialize(Optional sErr As String) As Boolean
Dim WSAD As WSADATA
Dim sLoByte As String
Dim sHiByte As String

    SocketsInitialize = False

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

    If WSAD.iMaxSockets < MIN_SOCKETS_REQD Then
        sErr = "This application requires a minimum of " & CStr(MIN_SOCKETS_REQD) & _
            " supported sockets."
        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))
        sErr = "Sockets version " & sLoByte & "." & sHiByte & " is not supported by " _
            & "32-bit Windows Sockets."
        Exit Function
    End If
    SocketsInitialize = True
End Function

Private Function HiByte(ByVal wParam As Integer)
    HiByte = wParam \ &H1 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
        MsgBox "Socket error occurred in Cleanup."
    End If
End Sub