NSLookup in Excel, IP to Hostname

@m_travis

The question you linked to has a related question.  Did you look at the related question?  It might be identical to your question in this thread.

https://www.experts-exchange.com/questions/24168988/I-need-the-opposit-information-I-have-an-excel-worksheet-with-a-couple-thousand-IP-addresses-and-want-to-find-out-who-they-belong-to-Do-you-have-an-modification-to-this-VB-script-that-can-help-me.html

@m_travis

I'm objecting to your deletion request.  You did not respond to my latest comment (http:#32823765).

It is an identical question but without a solution just and explanation on why most for the time the IP address will not resolve to a hostname, but i am still looking for the coded solution.

>> i am still looking for the coded solution

This is from the linked question.
"If you put the IP addresses in a flat file you could issue the command:

  -- see snippet below

This will read each line in the file addrlist.txt, issue a nslookup with the type set to ptr for each address in addrlist.txt and appened the results to the end of the file results.txt."

If you launched that script and then read the results into some VBA code in your workbook, it seems that would give you the information you seek.

for /f %a in (addrlist.txt)  do nslookup -type=ptr %a >> results.txt

Select allOpen in new window

I saw that code for dos. I have no issues with nslookup in dos. My issue is with excel since I am pulling a large volume of logs from an IPS device in cvs format.

Here is a link to an API invocation solution:
http://allfaq.org/forums/t/172546.aspx

aikimark solution looks like it will work, I need time to test it then I will accept his solution. I do appreciate the effort to answer this question.

Thank you for your help.

i put the code in personal under a new module. In the work sheet I put "=GetHostName(xxx.xxx.xxx.xxx)" in a cell and get an error. I also tried the same function but instead of passing the IP in standard form I tried passing the IP in decimal form and still get an error. I am not sure what i am doing wrong.

I just tested it and found that it needs the following:1. Add the following in the General Declarations sectionPublic Const AF_INET As Long = 2Public Const ERROR_SUCCESS As Long = 0Public Declare Function inet_addr Lib "wsock32" _  (ByVal s As String) As Long2. Invoke the newly declared function to properly convert the IP address from a string into the expected format.Example:GetHostName(inet_addr("209.85.225.105"))

Reference: http://vbnet.mvps.org/index.html?code/network/hostnamefromip.htm

I added the code and tried to execute via =GetHostName(inet_addr("208.68.139.38")), and got and error
not sure what i am doing wrong

what error did you get?

Invalid name error

Everything is working now. Thank you aikimark for all the help. I put the final code in the bottom for others to use.

Option Explicit

Public Const MIN_SOCKETS_REQD As Long = 1
Private Declare Function inet_addr Lib "wsock32" _
  (ByVal s As String) As Long
Public Const AF_INET As Long = 2
Public Const ERROR_SUCCESS As Long = 0
Public Const WS_VERSION_REQD As Long = &H101
Public Const WS_VERSION_MAJOR As Long = WS_VERSION_REQD \ &H100 And &HFF&
Public Const WS_VERSION_MINOR As Long = WS_VERSION_REQD And &HFF&
Public Const SOCKET_ERROR As Long = -1
Public Const WSADESCRIPTION_LEN = 257
Public Const WSASYS_STATUS_LEN = 129
Public Const MAX_WSADescription = 256
Public Const MAX_WSASYSStatus = 128
Public 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
Declare Function WSACleanup Lib "wsock32" () As Long
Declare Function WSAStartup Lib "wsock32" (ByVal wVersionRequired As Long, lpWSADATA As WSAData) As Long
Declare Function gethostbyaddr Lib "wsock32.dll" (haddr As Long, ByVal hnlen As Long, ByVal addrtype As Long) As Long
Declare Function lstrlenA Lib "kernel32" (ByVal Ptr As Any) As Long
Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (hpvDest As Any, hpvSource As Any, ByVal cbCopy As Long)
Public Function GetHostName(ByVal Address As String) As String
    Dim lLength As Long, lRet As Long
    If Not SocketsInitialize() Then Exit Function
    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)
            CopyMemory ByVal GetHostName, ByVal lRet, lLength
        End If
    Else
        GetHostName = ""
    End If
    SocketsCleanup
End Function
Public Function HiByte(ByVal wParam As Integer)
    HiByte = wParam \ &H100 And &HFF&
End Function
Public Function LoByte(ByVal wParam As Integer)
    LoByte = wParam And &HFF&
End Function
Public Sub SocketsCleanup()
    If WSACleanup() <> ERROR_SUCCESS Then
        MsgBox "Socket error occurred in Cleanup."
    End If
End Sub
Public 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
        MsgBox "The 32-bit Windows Socket is not responding."
        SocketsInitialize = False
        Exit Function
    End If
    If WSAD.wMaxSockets < MIN_SOCKETS_REQD Then
        MsgBox "This application requires a minimum of " & CStr(MIN_SOCKETS_REQD) & " supported sockets."
        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))
        MsgBox "Sockets version " & sLoByte & "." & sHiByte & " is not supported by 32-bit Windows Sockets."
        SocketsInitialize = False
        Exit Function
    End If
    'must be OK, so lets do it
    SocketsInitialize = True
End Function

Select allOpen in new window

thank you for all the help aikimark