I'm trying to use Excel to process a list of IP addresses and return a
column of host names.

I have other tools that do this, but the list of addresses is usually parsed
in Excel, so I thought that it would save time to use Excel for the complete
task.

I've looked through many sites but have not come up with a solution. For
instance there may be a VBA script that does this, but I am not able to get
this to work in Excel:

http://vbnet.mvps.org/code/network/hostnamefromip.htm

I have other tools that do this, but the list of addresses is usually parsed
in Excel, so I thought that it would save time to use Excel for the complete
task.

Thanks for any help.

Ed

Hi Ed

here is the code you referenced amended that worked for me.
Note: Starts looking from A1 until 1st empty cell.

Option Explicit

'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''
' Copyright ©1996-2004 VBnet, Randy Birch, All Rights Reserved.
' Some pages may also contain other copyrights by the author.
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''
' Distribution: You can freely use this code in your own
' applications, but you may not reproduce
' or publish this code on any web site,
' online service, or distribute as source
' on any media without express permission.
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''
Private Const WSADescription_Len As Long = 256
Private Const WSASYS_Status_Len As Long = 128
Private Const WS_VERSION_REQD As Long = &H101
Private Const IP_SUCCESS As Long = 0
Private Const SOCKET_ERROR As Long = -1
Private Const AF_INET As Long = 2

Private Type WSADATA
wVersion As Integer
wHighVersion As Integer
szDescription(0 To WSADescription_Len) As Byte
szSystemStatus(0 To WSASYS_Status_Len) As Byte
imaxsockets As Integer
imaxudp As Integer
lpszvenderinfo As Long
End Type

Private Declare Function WSAStartup Lib "wsock32" _
(ByVal VersionReq As Long, _
WSADataReturn As WSADATA) As Long

Private Declare Function WSACleanup Lib "wsock32" () As Long

Private Declare Function inet_addr Lib "wsock32" _
(ByVal s As String) As Long

Private Declare Function gethostbyaddr Lib "wsock32" _
(haddr As Long, _
ByVal hnlen As Long, _
ByVal addrtype As Long) As Long

Private Declare Sub CopyMemory Lib "kernel32" _
Alias "RtlMoveMemory" _
(xDest As Any, _
xSource As Any, _
ByVal nbytes As Long)

Private Declare Function lstrlen Lib "kernel32" _
Alias "lstrlenA" _
(lpString As Any) As Long



Sub GetHostList()
Dim x As Long

x = 0
DoEvents
Do Until Cells(x + 1, 1) = ""
Cells(x + 1, 2) = GetHostNameFromIP(Cells(x + 1, 1))
x = x + 1
Loop

End Sub


Private Function SocketsInitialize() As Boolean

Dim WSAD As WSADATA

SocketsInitialize = WSAStartup(WS_VERSION_REQD, WSAD) = IP_SUCCESS

End Function


Private Sub SocketsCleanup()

If WSACleanup() < 0 Then
MsgBox "Windows Sockets error occurred in Cleanup."
vbExclamation
End If

End Sub


Private Function GetHostNameFromIP(ByVal sAddress As String) As String

Dim ptrHosent As Long
Dim hAddress As Long
Dim nbytes As Long

If SocketsInitialize() Then

'convert string address to long
hAddress = inet_addr(sAddress)

If hAddress < SOCKET_ERROR Then

'obtain a pointer to the HOSTENT structure
'that contains the name and address
'corresponding to the given network address.
ptrHosent = gethostbyaddr(hAddress, 4, AF_INET)

If ptrHosent < 0 Then

'convert address and
'get resolved hostname
CopyMemory ptrHosent, ByVal ptrHosent, 4
nbytes = lstrlen(ByVal ptrHosent)

If nbytes 0 Then
sAddress = Space$(nbytes)
CopyMemory ByVal sAddress, ByVal ptrHosent, nbytes
GetHostNameFromIP = sAddress
End If

Else
GetHostNameFromIP = "Call to gethostbyaddr failed."
End If 'If ptrHosent

SocketsCleanup

Else
GetHostNameFromIP = "String passed is an invalid IP."
End If 'If hAddress

Else
GetHostNameFromIP = "Sockets failed to initialize."
End If 'If SocketsInitialize

End Functio

--
Ivan F Moal

-----------------------------------------------------------------------
Ivan F Moala's Profile: http://www.excelforum.com/member.php...nfo&userid=195
View this thread: http://www.excelforum.com/showthread.php?threadid=27306

Here's the steps to reproduce in Excel:

Create a new userform
drop 2 textboxes and 1 commandbutton onto the form
rename the textboxes as Text1 and Text2
rename the Commandbutton as Command1

Run the form
Put the ip address into the first box, click the button.


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Ed Wurster" wrote in message
...
I'm trying to use Excel to process a list of IP addresses and return a
column of host names.

I have other tools that do this, but the list of addresses is usually
parsed
in Excel, so I thought that it would save time to use Excel for the
complete
task.

I've looked through many sites but have not come up with a solution. For
instance there may be a VBA script that does this, but I am not able to
get
this to work in Excel:

http://vbnet.mvps.org/code/network/hostnamefromip.htm

I have other tools that do this, but the list of addresses is usually
parsed
in Excel, so I thought that it would save time to use Excel for the
complete
task.

Thanks for any help.

Ed

Ivan F Moala wrote:
here is the code you referenced amended that worked for me.
Note: Starts looking from A1 until 1st empty cell.

snipped the rest...

Thanks for the help. I'll try this over the weekend.

Do I understand correctly that all of this would pasted to a macro, and then
I would run it?

Ed

Rob van Gelder wrote:
Here's the steps to reproduce in Excel:

Create a new userform
drop 2 textboxes and 1 commandbutton onto the form
rename the textboxes as Text1 and Text2
rename the Commandbutton as Command1

Run the form
Put the ip address into the first box, click the button.

Thanks for adding in a few tips. I'll try to make it work and post the
result.

Ed