IP address to Hostname in Excel
in [Programming]
|
From: JLatham on 11 Apr 2010 13:56 Joel, Nice find. I looked for something like that and was not successful. Do you have the URL for the site? I've modified it to work off of an Excel sheet vs through a form and tested with the 32-bit version of Office/Excel under both Vista Home Premium x64 and Windows 7 Ultimate x64 and it works fine. However, in a virtual machine running Windows 7 Pro x64 with the Office/Excel 2010 Beta, the code won't compile and markes all API declarations as errors with this message: Compiler error: The code in this project must be updated for use on 64-bit systems. Please review and update Declare statements and then mark them with the PtrSafe attribute. Guess I'll have to dig into the x64 API's and see if I can't figure out how to change that section, plus I'll have to research the "PtrSafe" attribute, which the Help in 2010 VBA gave no reference to and couldn't find it on on-line help either. "joel" wrote: > > I found code on the web that works fine. I added a routing at the end > to test the code. > > > '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' > ' Copyright ©1996-2009 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 > > > > Private Sub Command1_Click() > > Text2.Text = GetHostNameFromIP(Text1.Text) > > 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 > MsgBox "Call to gethostbyaddr failed." > End If 'If ptrHosent > > SocketsCleanup > > Else > MsgBox "String passed is an invalid IP." > End If 'If hAddress > > Else > MsgBox "Sockets failed to initialize." > End If 'If SocketsInitialize > > End Function > > Sub test() > MsgBox (GetHostNameFromIP("192.168.1.30")) > > End Sub > > > -- > joel > ------------------------------------------------------------------------ > joel's Profile: 229 > View this thread: http://www.thecodecage.com/forumz/showthread.php?t=194661 > > http://www.thecodecage.com/forumz > > . >
From: JLatham on 11 Apr 2010 14:13 Never mind the request for the URL, found it: http://vbnet.mvps.org/code/network/hostnamefromip.htm "joel" wrote: > > I found code on the web that works fine. I added a routing at the end > to test the code. > > > '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' > ' Copyright ©1996-2009 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 > > > > Private Sub Command1_Click() > > Text2.Text = GetHostNameFromIP(Text1.Text) > > 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 > MsgBox "Call to gethostbyaddr failed." > End If 'If ptrHosent > > SocketsCleanup > > Else > MsgBox "String passed is an invalid IP." > End If 'If hAddress > > Else > MsgBox "Sockets failed to initialize." > End If 'If SocketsInitialize > > End Function > > Sub test() > MsgBox (GetHostNameFromIP("192.168.1.30")) > > End Sub > > > -- > joel > ------------------------------------------------------------------------ > joel's Profile: 229 > View this thread: http://www.thecodecage.com/forumz/showthread.php?t=194661 > > http://www.thecodecage.com/forumz > > . >
From: JLatham on 11 Apr 2010 14:57 I found the cure in a 'pure' 64-bit world. The VM I have set up (in VMWare's Player) uses 64-bit Windows 7 Ultimate and the 64-bit Beta version of Office/Excel. To get the code to run in that environment you have to change all of the Private Declare statements to Private Declare PtrSafe and then it compiles and runs just fine in the 64-bit world (but not in 32-bit Excel 2007, which doesn't seem to recognize "PtrSafe" at all). "JLatham" wrote: > Joel, > Nice find. I looked for something like that and was not successful. Do you > have the URL for the site? > > I've modified it to work off of an Excel sheet vs through a form and tested > with the 32-bit version of Office/Excel under both Vista Home Premium x64 and > Windows 7 Ultimate x64 and it works fine. However, in a virtual machine > running Windows 7 Pro x64 with the Office/Excel 2010 Beta, the code won't > compile and markes all API declarations as errors with this message: > > Compiler error: > The code in this project must be updated for use on 64-bit systems. > Please review and update Declare statements and then mark them with > the PtrSafe attribute. > > Guess I'll have to dig into the x64 API's and see if I can't figure out how > to change that section, plus I'll have to research the "PtrSafe" attribute, > which the Help in 2010 VBA gave no reference to and couldn't find it on > on-line help either. > > "joel" wrote: > > > > > I found code on the web that works fine. I added a routing at the end > > to test the code. > > > > > > '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' > > ' Copyright ©1996-2009 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 > > > > > > > > Private Sub Command1_Click() > > > > Text2.Text = GetHostNameFromIP(Text1.Text) > > > > 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 > > MsgBox "Call to gethostbyaddr failed." > > End If 'If ptrHosent > > > > SocketsCleanup > > > > Else > > MsgBox "String passed is an invalid IP." > > End If 'If hAddress > > > > Else > > MsgBox "Sockets failed to initialize." > > End If 'If SocketsInitialize > > > > End Function > > > > Sub test() > > MsgBox (GetHostNameFromIP("192.168.1.30")) > > > > End Sub > > > > > > -- > > joel > > ------------------------------------------------------------------------ > > joel's Profile: 229 > > View this thread: http://www.thecodecage.com/forumz/showthread.php?t=194661 > > > > http://www.thecodecage.com/forumz > > > > . > >
From: joel on 11 Apr 2010 16:12 I did a google search so I wasn't suprized that you easily found the same site. I would think the fix in excel is to change the declarations of Long to Double. Long would be 32 bits and double would be 64 bits. Excel 2007 is still in a 32 bit world and the DLL in windows 7 are 64 bit. -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/showthread.php?t=194661 http://www.thecodecage.com/forumz
From: Chip Pearson on 11 Apr 2010 16:53 Nice code. Randy Birch's site is a treasure trove. One thing caught my eye, though. >szDescription(0 To WSADescription_Len) As Byte >szSystemStatus(0 To WSASYS_Status_Len) As Byte Since these are 0-based arrays, shouldn't you subtract 1 from the _Len variables? E.g., szDescription(0 To WSADescription_Len - 1) As Byte szSystemStatus(0 To WSASYS_Status_Len - 1) As Byte Cordially, Chip Pearson Microsoft Most Valuable Professional, Excel, 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com On Sun, 11 Apr 2010 14:10:11 +0000, joel <joel.499cg3(a)thecodecage.com> wrote: > >I found code on the web that works fine. I added a routing at the end >to test the code. > > >'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' >' Copyright �1996-2009 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 > > > >Private Sub Command1_Click() > >Text2.Text = GetHostNameFromIP(Text1.Text) > >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 >MsgBox "Call to gethostbyaddr failed." >End If 'If ptrHosent > >SocketsCleanup > >Else >MsgBox "String passed is an invalid IP." >End If 'If hAddress > >Else >MsgBox "Sockets failed to initialize." >End If 'If SocketsInitialize > >End Function > >Sub test() >MsgBox (GetHostNameFromIP("192.168.1.30")) > >End Sub
First
|
Prev
|
Next
|
Last
Pages: 1 2 3 4 Prev: loop through every other and 3rd cell in range Next: Select Case |