# How do I resolve IP Addresses to Hostnames in Excel

**URL:** <https://boards.straightdope.com/t/how-do-i-resolve-ip-addresses-to-hostnames-in-excel/82120>\
**Category:** Factual Questions\
**Created:** [September 17, 2001, 7:33am UTC](https://boards.straightdope.com/t/how-do-i-resolve-ip-addresses-to-hostnames-in-excel/82120 "2001-09-17T07:33:05Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Bill\_H](https://avatars.discourse-cdn.com/v4/letter/b/a5b964/32.png) [@Bill\_H](https://boards.straightdope.com/u/Bill_H)\
**Post date:** [September 17, 2001, 7:33am UTC](https://boards.straightdope.com/t/how-do-i-resolve-ip-addresses-to-hostnames-in-excel/82120/1 "2001-09-17T07:33:05Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Joe\_Cool](https://avatars.discourse-cdn.com/v4/letter/j/ecb155/32.png) [@Joe\_Cool](https://boards.straightdope.com/u/Joe_Cool)\
**Post date:** [September 17, 2001, 11:38am UTC](https://boards.straightdope.com/t/how-do-i-resolve-ip-addresses-to-hostnames-in-excel/82120/2 "2001-09-17T11:38:38Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![micco](https://avatars.discourse-cdn.com/v4/letter/m/5f8ce5/32.png) [@micco](https://boards.straightdope.com/u/micco)\
**Post date:** [September 17, 2001, 1:47pm UTC](https://boards.straightdope.com/t/how-do-i-resolve-ip-addresses-to-hostnames-in-excel/82120/3 "2001-09-17T13:47:07Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![muttrox](https://avatars.discourse-cdn.com/v4/letter/m/a8b319/32.png) [@muttrox](https://boards.straightdope.com/u/muttrox)\
**Post date:** [September 17, 2001, 2:13pm UTC](https://boards.straightdope.com/t/how-do-i-resolve-ip-addresses-to-hostnames-in-excel/82120/4 "2001-09-17T14:13:09Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![micco](https://avatars.discourse-cdn.com/v4/letter/m/5f8ce5/32.png) [@micco](https://boards.straightdope.com/u/micco)\
**Post date:** [September 17, 2001, 2:28pm UTC](https://boards.straightdope.com/t/how-do-i-resolve-ip-addresses-to-hostnames-in-excel/82120/5 "2001-09-17T14:28:03Z")

</div>

> [@](#):
>
> \*Originally posted by muttrox \*  
> \*\*Any similar way in Unix? Asides from endless calls to whois and nslookup? \*\*

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.

---

<div class="post-metadata">

**Author:** ![Bill\_H](https://avatars.discourse-cdn.com/v4/letter/b/a5b964/32.png) [@Bill\_H](https://boards.straightdope.com/u/Bill_H)\
**Post date:** [September 18, 2001, 6:55pm UTC](https://boards.straightdope.com/t/how-do-i-resolve-ip-addresses-to-hostnames-in-excel/82120/6 "2001-09-18T18:55:38Z")

</div>

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

```auto

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

```