Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 6
Default IP address to Hostname in Excel

Hi,
I am hoping someone can help me please..
I have a spreadsheet that is linked to a survey being hosted on our website.
The survey is about internet safety and one of the questions it ask is what
is your ISP? However it seems that not everyone here in Bahrain knows this
and their entries are not valid. However we trap the IP addess in the survey
results and these are pulled into the spreadsheet along with all the survey
answers.

column a col b col c col d
IP Address Sex Age Nationality etc..
193.188.105.25 M 32 Bahraini etc..
84.235.101.66 M 33 Lebanese
83.136.59.145 F 28 Bahraini

I want to find a way of converting the IP Address into an ISP name or at
least the hostname. Can anyone help?
Cheers in advance
Nick


---
frmsrcurl: http://msgroups.net/microsoft.public.excel.programming/
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: IP address to Hostname in Excel

Converting IP Addresses to Hostnames in Excel

1. Install the "Resolve IP Addresses to Hostnames" add-in for Excel from the Microsoft website.

2. Select the column containing the IP addresses.

3. Go to the "Data" tab in the Excel ribbon and click on "Resolve IP Addresses to Hostnames" in the "Get External Data" section.

4. In the "Resolve IP Addresses to Hostnames" dialog box, select the column containing the IP addresses and click "OK".

5. Excel will then convert the IP addresses to hostnames and display them in a new column.

Alternatively, you can use a formula to convert IP addresses to hostnames. Here's an example:

1. In a new column, enter the following formula:
Formula:
=NLOOKUP(A2,"http://www.dnsstuff.com/tools/ipall.ch?domain="
2. Replace "A2" with the cell containing the IP address you want to convert.

3. Press Enter and Excel will convert the IP address to a hostname.

Remember to download and install the add-in before attempting to convert IP addresses to hostnames in Excel.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default IP address to Hostname in Excel


You can get the PC Name by usding the environmental variable
COMPUTERNAME


computerName = environ("COMPUTERNAME")


You can get the IP address from the computername by doing a ipconfig
command to the host name. From a sheel prompt

1) Start - run - c:\windos\system32\cmd.exe
2) ipconfig


You can run these commands from a sheel command like this

ComputerName = Environ("ComputerName")

Shell "cmd /c C:\windows\system32\ipconfig c:\temp\ipconfig.txt", 0


You can then open the text file to get the results.


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=194661

http://www.thecodecage.com/forumz

  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 6
Default IP address to Hostname in Excel

Hi, Thanks for the reply.
However, I a not after my data, but the hostnames of IP addresses registered in a survey by people who have filled in a survey.

Cheers
Nick

---
frmsrcurl: http://msgroups.net/microsoft.public...tname-in-Excel
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default IP address to Hostname in Excel


what I did recently in C# was to ping the IP addrress which filled up
the ARP table. Then went into the ARP table to get the Host Names. It
is posible to do the same thing uisng VBA but will have to think about
the process.

I aslo can open an Excel file from C#. so I could write a C# program
which opens your excel file get the IP address, ping the addresses, and
then read the arp table.

Likewise, from VBA I could use the Shell command to ping each of the IP
addresses then read the arp table. To read the ARP table I would use a
DLL in the system32 folder to get the results. there also may be a
reference library that would read the arp table but would need to
investigate some more.


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=194661

http://www.thecodecage.com/forumz



  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 6
Default IP address to Hostname in Excel

Thnaks Joel. I tried calling wsock32 but this is a 64 bit machine and I got errors on teh library.
I saw some code gethostnamefromIp but could not make it work in Excel.
I have 800+ IP addresses to check and output some sort of data.
I guess similar to an excel version of ping -a
where the output would be (as in this case (dhcp.trcy.mi.charter.com)

ping -a 24.236.213.225
Pinging 24-236-213-225.dhcp.trcy.mi.charter.com

---
frmsrcurl: http://msgroups.net/microsoft.public...tname-in-Excel
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default IP address to Hostname in Excel


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/sh...d.php?t=194661

http://www.thecodecage.com/forumz

  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,203
Default IP address to Hostname in Excel

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/sh...d.php?t=194661

http://www.thecodecage.com/forumz

.

  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,203
Default IP address to Hostname in Excel

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/sh...d.php?t=194661

http://www.thecodecage.com/forumz

.

  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,203
Default IP address to Hostname in Excel

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/sh...d.php?t=194661

http://www.thecodecage.com/forumz

.



  #11   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default IP address to Hostname in Excel


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/sh...d.php?t=194661

http://www.thecodecage.com/forumz

  #12   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 7,247
Default IP address to Hostname in Excel

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
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

  #13   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default IP address to Hostname in Excel


Chip : I think you may of found the error. the meory copy could give
an error if you copy an array that is longer then the amound of memory
delecared for the array. Varptr I have seen used to force an array to
be on a word or double word boundry so the byte count is correct. There
is a potential problem is you use memorycopy to copy words or double
words and the memory you are copying doesn't lie on an even boundry.

John: Which function in code is failing. I would expect most of the
code is good and ther is just a couple of lirary declarations that are
wrong.


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=194661

http://www.thecodecage.com/forumz

  #14   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 6
Default IP address to Hostname in Excel

Hi Guys,
Can I see how you modified the code to work through an excel spreadsheet? I want the output on one sheet but the list is on another in the same workbook. I have the code running without errors now, but as the functions are private I can't call them from within Excel.

Any help would be appreciated?

Kind regards, and VERY grateful...
Nick
Here is an interesting statistic from the survey a huge number of self declared internet experts, who have used the internet for more than 6 years, think their ISP is internet explorer or Firefox...

---
frmsrcurl: http://msgroups.net/microsoft.public...tname-in-Excel
  #15   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default IP address to Hostname in Excel


Put the code into a module VBA sheet and remove the word "PRIVATE".


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=194661

http://www.thecodecage.com/forumz



  #16   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 6
Default IP address to Hostname in Excel

Hi Joel
Is there a way to speed the program up? I have used a pivot table and reduced the IP list to 400 entries. Where there is no hostname I gt a blank, but it takes 10 minutes to resolve all 400. Does this sound right?
Cheers
Nick

---
frmsrcurl: http://msgroups.net/microsoft.public...tname-in-Excel
  #17   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default IP address to Hostname in Excel


Which instruction are you hanging up at? I tried a few invalid IP
addresses with my original posted program and didn't get any hangups. I
suspect some of your modifications are causing the problem. Can you
post your lasted code.

there arre a ffew tests wich you may of left out of the modified code

1) If ptrHosent < 0 Then
2) If nbytes 0 Then


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=194661

http://www.thecodecage.com/forumz

  #18   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 6
Default IP address to Hostname in Excel

Thanks i will give that a go, it actually took 45 mins for all 400, which can't be right.

Nick

---
frmsrcurl: http://msgroups.net/microsoft.public...tname-in-Excel
  #19   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 4
Default IP address to Hostname in Excel

On Monday, 12 April 2010 13:20:38 UTC+1, nicktruman wrote:
Thanks i will give that a go, it actually took 45 mins for all 400, which can't be right.

Nick

---
frmsrcurl: http://msgroups.net/microsoft.public...tname-in-Excel


I've found that all attempts to get a host-name from an IP address that use CopyMemory form within Excel just crash Excel. Using W10 anyway...

Other solutions rely on access to the command prompt which my environment does not have. Are there any other solutions?
  #20   Report Post  
Banned
 
Posts: 20
Default

Cảm Æ¡n bạn vì bÃ*i viết rất bổ Ã*ch vÃ* thú vị


  #21   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 162
Default IP address to Hostname in Excel

Randy Birch's original works fine for me in Win10. If you're using Excel64
you would of course need to adapt the APIs, pointers and pointer lengths.

Peter T


wrote in message
...
On Monday, 12 April 2010 13:20:38 UTC+1, nicktruman wrote:
Thanks i will give that a go, it actually took 45 mins for all 400, which
can't be right.

Nick

---
frmsrcurl:
http://msgroups.net/microsoft.public...tname-in-Excel


I've found that all attempts to get a host-name from an IP address that
use CopyMemory form within Excel just crash Excel. Using W10 anyway...

Other solutions rely on access to the command prompt which my environment
does not have. Are there any other solutions?



  #22   Report Post  
Banned
 
Posts: 20
Default

Quote:
Originally Posted by nicktruman View Post
Hi, I am hoping someone can help me please.. I have a spreadsheet that is linked to a survey being hosted on our website. The survey is about internet safety and one of the questions it ask is what is your ISP? However it seems that not everyone here in Bahrain knows this and their entries are not valid. However we trap the IP addess in the survey results and these are pulled into the spreadsheet along with all the survey answers. column a col b col c col d IP Address Sex Age Nationality etc.. 193.188.105.25 M 32 Bahraini etc.. 84.235.101.66 M 33 Lebanese 83.136.59.145 F 28 Bahraini I want to find a way of converting the IP Address into an ISP name or at least the hostname. Can anyone help? Cheers in advance Nick --- frmsrcurl: http://msgroups.net/microsoft.public.excel.programming/
Cảm Æ¡n bạn vì bÃ*i viết rất bổ Ã*ch vÃ* thú vị
  #23   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 4
Default IP address to Hostname in Excel

Sorry - I should have pointed out that the "CopyMemory" problem occurs on both 32 and 64 bit Office IF running on 64-bit Windows. I'm familiar with the PtrSafe construct but I've tried adjusting the data types (LongPtr, LongLong, etc) without success. I also emailed Randy who said he'd never tried to make it work on 64-bit OS. Another source said that anything using winsock32.dll on 64 bit OS won't work reliably, but I don't know if that is true. You can get a Name from an IP using WMI but if the remote computer isn't listening on WMI the code waits a long-time before giving up, which is no good if its along list of computers, and they may not all be running Windows!
  #24   Report Post  
Banned
 
Posts: 20
Default

Tất cả những thÃ*nh viên dÆ°á»›i 18 tuổi (hoặc được xem lÃ* trẻ vị thÃ*nh niên tại nÆ¡i bạn cÆ° trú) tham gia cá cược lÃ* hÃ*nh vi bất hợp pháp tại K8vn . Bất kỳ thÃ*nh viên nÃ*o dÆ°á»›i 18 tuổi không được mở tÃ*i khoản hay cược tại trang web. Nếu Casino K8 phát hiện bất kỳ hÃ*nh vi vi phạm quy định nÃ*y của khách hÃ*ng , bá»™ pháº*n liên quan sẽ tiến hÃ*nh đóng tÃ*i khoản cá cược đó.
VỠcá cược tại K8vn
trang K8vn chỉ lÃ* má»™t cách để giải trÃ*, xin quý khách không nên nghiện, dẫn đến tiêu cá»±c. Giải trÃ* K8 chịu trách nhiệm vá»›i khách hÃ*ng cung cấp giải trÃ* tốt nhất trong môi trÆ°á»ng tốt nhất, hy vá»ng quý khách ổn định tâm lý tránh ham mê cá» bạc vÃ* ảnh hưởng đến sá»± nghiệp vÃ* gia đình của riêng mình, tháº*m chÃ* ảnh hưởng đến ngÆ°á»i thân trong gia đình.

Casino K8 lÆ°u ý tá»›i tất cả ngÆ°á»i chÆ¡i:
Bất kỳ sá»± vi phạm nÃ*o của các tÃ*i khoản, sau khi kiểm tra Casino K8 có quyá»n đóng các tÃ*i khoản cá cược nêu trên.
Casino K8 lÆ°u ý tá»›i tất cả ngÆ°á»i chÆ¡i:
Casino K8 luôn mong muốn nâng cao chất lượng vÃ* Ä‘em đến nhiá»u chÆ°Æ¡ng trình giải trÃ* có chất lượng cao dÃ*nh cho thÃ*nh viên. Mục Ä‘Ã*ch của sá»± thiết láº*p nÃ*y lÃ* vì chúng tôi muốn mang đến sá»± đảm bảo, an toÃ*n, công bằng của các trò chÆ¡i mÃ* chúng tôi cung cấp. Chúng tôi mong muốn thÃ*nh viên dÃ*nh Ã*t thá»i gian trả lá»i câu há»i dÆ°á»›i đây để biết thông tin vá» chÆ¡i có trách nhiệm:
1.Bạn đang cảm thấy buồn chán nên tham gia chơi cá cược?
2. Sau khi bạn hoÃ*n thÃ*nh cược, bạn có cảm thấy tiá»n đã bị mất Ä‘i không hoặc tiếp tục cược cÃ*ng sá»›m cÃ*ng tốt?
3.Bạn sẽ cược liên tục cho tá»›i khi bạn hết tiá»n?
4. Bạn sẽ nói dối tháº*m chÃ* ăn cắp tiá»n để đánh bạc?
5. Bạn đã cố tình che dấu thá»i gian vÃ* tiá»n bạn để chi tiêu trong cá» bạc?
6. Bạn có đồng ý chi trả tiá»n cược cho các lÄ©nh vá»±c khác?
7. Bạn đã mất Ä‘i niá»m vui đối vá»›i ngÆ°á»i thân, bạn bè tháº*m chÃ* lÃ* sở thÃ*ch?
8. Giả sá»* số dÆ° trong tÃ*i khoản game của bạn đã cược hết không còn tiá»n, bạn có muốn đánh nhanh thắng nhanh lấy lại những gì đã mất?
Nếu hầu hết các lá»±a chá»n câu trả lá»i của bạn lÃ* €œCó€, bạn có thể đã bị nghiện cá» bạc, chúng tôi khuyên bạn nên:
€¢ Nên xem cá cược lÃ* má»™t thú tiêu khiển
€¢ Hạn chế sá»± mất mát má»™t cách liên tục
€¢ ChÆ¡i game có sá»± hiểu biết riêng của mình
€¢ Sắp xếp thá»i gian má»™t cách hợp lý
Bạn có thể đăng nháº*p vÃ*o địa chỉ www.gamcare.org.uk để tìm hiểu nhiá»u thông tin hÆ¡n cá» cách thức sắp sếp thá»i gian má»™t cách hợp lý khi tham gia cược.
Tá»± cô láº*p:
Äối vá»›i má»™t số khách hÃ*ng muốn tạm ngÆ°ng đánh bạc má»™t thá»i gian, chúng tôi sẽ cung cấp tÃ*nh năng nÃ*y cho bạn, bạn có thể tạm đóng tÃ*i khoản từ 6 tháng tá»›i 5 năm. Vui lòng click vÃ*o trang web €œLiên hệ chúng tôi €œ, bá»™ pháº*n Chat trá»±c tuyến của chúng tôi sẽ cung cấp cho bạn thêm thông tin cần thiết.
Khuyến cáo không mở nhiá»u tÃ*i khoản, tên biệt danh vÃ* lạm dụng máº*t khẩu .
Giám sát của phụ huynh:
Có rất nhiá»u phụ huynh vÃ* ngÆ°á»i giám há»™ yêu cầu trang web tiến hÃ*nh bãi bá», chúng tôi giá»›i thiệu các trang web sau đây:
€¢ Phần má»m bảo vệ trẻ em tránh xa các trang web không phù hợp: www.netnanny.com
€¢ Cho phép phụ huynh cÃ*i đặt trang web cách ly: www.cybersitter.com
  #25   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 162
Default IP address to Hostname in Excel


wrote in message
...
Sorry - I should have pointed out that the "CopyMemory" problem occurs on
both 32 and 64 bit Office IF running on 64-bit Windows. I'm familiar with
the PtrSafe construct but I've tried adjusting the data types (LongPtr,
LongLong, etc) without success. I also emailed Randy who said he'd never
tried to make it work on 64-bit OS. Another source said that anything using
winsock32.dll on 64 bit OS won't work reliably, but I don't know if that is
true. You can get a Name from an IP using WMI but if the remote computer
isn't listening on WMI the code waits a long-time before giving up, which is
no good if its along list of computers, and they may not all be running
Windows!

I can only reiterate Randy's original works fine for me in Win10 + Excel64
the with the usual adaptations for the APIs. Without knowing what you've
changed hard to suggest why yours crashes.

All the APIs should be declared with PtrSafe.
The gethostbyaddr function should return a LongPtr and its hAddr argument
should be a LongPtr

In Randy's GetHostNameFromIP function ptrHosent and hAddress should be
LongPtr

If you need to cater for Office 2007 or earlier you'll need to include the
differences for both APIs and declared variables (in your own functions)
under
#IfVBA7
ptrSafe LongPtr version..
#Else
original code.
#End If

In passing don't declare as LongLong unless you're coding exclusively for
Office64 or under #Win64.Under VBA7 LongPtr is a Long in x32 and a LongLong
in x64.

Another tip, look for arguments in APIs starting with h (or hwnd) or include
ptr. Handles and pointers are LongPtr (ie LongLong in x64) and variables
that pass or return such values should be declared as LongPtr. APIs that
return similar will return a LongPtr, and should be returned to
appropriately declared variables.

Head your module Option Explicit and do Debug / compile in both x32 and x64

In 32 bit Office Randy's original should work fine as-is even in 64bit
Windows. Make sure you copy it directly from his site. All you need to do is
change the two text-box references to strings.

Peter T




  #26   Report Post  
Junior Member
 
Posts: 3
Default

Cho nát luôn
http://notepad.vn/share/weerizr23
  #27   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 4
Default IP address to Hostname in Excel

Hi Peter - yep - that works - brilliant. I'm indebted to you sir! Many thanks indeed :)
  #28   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default IP address to Hostname in Excel

I also emailed Randy who said he'd never tried to make it work on 64-bit OS.



Actually, I said I did not have a box with Office 64 installed; I do have 64 bit win10 and your code / my code worked perfectly in Excel 32-bit. I could not test it in Excel 64 bit. HTH.

Randy
  #29   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 162
Default IP address to Hostname in Excel

wrote in message
Hi Peter - yep - that works - brilliant. I'm indebted to you sir! Many
thanks indeed :)


I'm glad you got it working and thanks for the feedback, However any thanks
should really be addressed to Randy B for his amazing resource :)

Peter T




  #30   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 4
Default IP address to Hostname in Excel

Agreed - thanks Randy and apologies for the confusion re Windows/Office versions :)

Simon


  #31   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 7
Default IP address to Hostname in Excel

On Friday, April 14, 2017 at 12:39:40 AM UTC+1, wrote:
Agreed - thanks Randy and apologies for the confusion re Windows/Office versions :)

Simon


Dear all,
At work, I've inherited the file that's using Randy Birch's code from he http://vbnet.mvps.org/index.html?cod...byhostname.htm.
Recently, all machines have been up-dated to Office 64 bit and the code stopped working.
I've tried everything from Peter T suggestions above, but, still, as soon as the code gets to line: CopyMemory ptrName, ByVal ptrName, 4, Excel crashes.
I feel particularly stupid, as it looks that some people in this conversation have actually managed to adjust original code to 64 bit, following Peter T's advice.
Any help will be greatly appreciated!

Andrey G
  #32   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 88
Default IP address to Hostname in Excel

"Andrey G" wrote in message
On Friday, April 14, 2017 at 12:39:40 AM UTC+1,
wrote:
Agreed - thanks Randy and apologies for the confusion re Windows/Office
versions :)

Simon


Dear all,
At work, I've inherited the file that's using Randy Birch's code from
he http://vbnet.mvps.org/index.html?cod...byhostname.htm.
Recently, all machines have been up-dated to Office 64 bit and the code
stopped working.
I've tried everything from Peter T suggestions above, but, still, as soon
as the code gets to line: CopyMemory ptrName, ByVal ptrName, 4, Excel
crashes.
I feel particularly stupid, as it looks that some people in this
conversation have actually managed to adjust original code to 64 bit,
following Peter T's advice.
Any help will be greatly appreciated!

Andrey G


What were my 'suggestions above'...?

It looks like you're attempting to copy a pointer which in x64 will be an
8-bit LongLong, or a 4-bit Long in x32

Try declaring this conditional constant, either as public in a normal module
or as private in the module you call CopyMemory

#If Win64 Then
Public Const gcPtrBytes As Long = 8
#Else
Public Const gcPtrBytes As Long = 4
#End If

and
CopyMemory ptrName, ByVal ptrName, gcPtrBytes

Only a guess with not much to go on!

Peter T




  #33   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 538
Default IP address to Hostname in Excel

Peter T wrote:

"Andrey G" wrote in message
On Friday, April 14, 2017 at 12:39:40 AM UTC+1,
wrote:
Agreed - thanks Randy and apologies for the confusion re
Windows/Office versions :)

Simon


Dear all,
At work, I've inherited the file that's using Randy Birch's code from
he
http://vbnet.mvps.org/index.html?cod...byhostname.htm.
Recently, all machines have been up-dated to Office 64 bit and the code
stopped working.
I've tried everything from Peter T suggestions above, but, still, as
soon as the code gets to line: CopyMemory ptrName, ByVal ptrName, 4,
Excel crashes.
I feel particularly stupid, as it looks that some people in this
conversation have actually managed to adjust original code to 64 bit,
following Peter T's advice.
Any help will be greatly appreciated!

Andrey G


What were my 'suggestions above'...?

It looks like you're attempting to copy a pointer which in x64 will be
an 8-bit LongLong, or a 4-bit Long in x32


<pedantic=on 8-byte LongLong, 4-byte Long </pedantic=off

Try declaring this conditional constant, either as public in a normal
module or as private in the module you call CopyMemory

#If Win64 Then
Public Const gcPtrBytes As Long = 8
#Else
Public Const gcPtrBytes As Long = 4
#End If

and
CopyMemory ptrName, ByVal ptrName, gcPtrBytes

Only a guess with not much to go on!

Peter T


API calls declared PtrSafe?

#If Win64 (or #If Vba7)
Declare Sub PtrSafe CopyMemory etc.

--
- I just hope you know what you're doing.
- I hope that same thing, on occasion.
  #34   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 7
Default IP address to Hostname in Excel

On Tuesday, November 10, 2020 at 5:24:49 PM UTC, Peter T wrote:
"Andrey G" wrote in message
On Friday, April 14, 2017 at 12:39:40 AM UTC+1,
wrote:
Agreed - thanks Randy and apologies for the confusion re Windows/Office
versions :)

Simon


Dear all,
At work, I've inherited the file that's using Randy Birch's code from
he http://vbnet.mvps.org/index.html?cod...byhostname.htm.
Recently, all machines have been up-dated to Office 64 bit and the code
stopped working.
I've tried everything from Peter T suggestions above, but, still, as soon
as the code gets to line: CopyMemory ptrName, ByVal ptrName, 4, Excel
crashes.
I feel particularly stupid, as it looks that some people in this
conversation have actually managed to adjust original code to 64 bit,
following Peter T's advice.
Any help will be greatly appreciated!

Andrey G

What were my 'suggestions above'...?

It looks like you're attempting to copy a pointer which in x64 will be an
8-bit LongLong, or a 4-bit Long in x32

Try declaring this conditional constant, either as public in a normal module
or as private in the module you call CopyMemory

#If Win64 Then
Public Const gcPtrBytes As Long = 8
#Else
Public Const gcPtrBytes As Long = 4
#End If

and
CopyMemory ptrName, ByVal ptrName, gcPtrBytes

Only a guess with not much to go on!

Peter T


Peter,
Firstly, thank you very much for a prompt reply - I was afraid that this thread might be dead.
When I said "your suggestions" I meant: 1. All APIs were declared with PtrSafe 2. Arguments in APIs starting with h (or hwnd) or include
ptr declared as LongPtr.
Unfortunately, API calls are not my strong point.
I will try to change the pointer to 8 bit, as you advised, when I get to work PC.

Cheers,
Andrey
  #35   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 538
Default IP address to Hostname in Excel

Andrey G wrote:

Firstly, thank you very much for a prompt reply - I was afraid that this
thread might be dead.


Best thing to do is to make a new thread, and mention the subject of the
thread you want to reference (e.g. "see also the thread from January 2009,
subject 'Why can't I?'"). Replying to a 3-year-old thread is not the way to
go.

I will try to change the pointer to 8 bit


Argh! Bytes! 8 bytes! 64 bits.

--
I curse you to lives without meaning.


  #36   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 88
Default IP address to Hostname in Excel

"Auric__" wrote in message

It looks like you're attempting to copy a pointer which in x64 will be
an 8-bit LongLong, or a 4-bit Long in x32


<pedantic=on 8-byte LongLong, 4-byte Long </pedantic=off


Good catch!

PT


  #37   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 88
Default IP address to Hostname in Excel

"Auric__" wrote in message Andrey G wrote:

Firstly, thank you very much for a prompt reply - I was afraid that this
thread might be dead.


Best thing to do is to make a new thread, and mention the subject of the
thread you want to reference (e.g. "see also the thread from January 2009,
subject 'Why can't I?'"). Replying to a 3-year-old thread is not the way
to
go.

I will try to change the pointer to 8 bit


Argh! Bytes! 8 bytes! 64 bits.



Oh dear, yes indeed bytes not bits. Hope you noticed I did at get it right,
implicitly at least, with the var-name 'gcPtrBytes' I suggested:)

Also (Andrey), again perhaps slightly pedantic, what you will be changing is
the last argument for CopyMemory, namely the length or number of bytes to
copy, from 4 in x32 to 8 in x64.

Peter T


  #38   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 7
Default IP address to Hostname in Excel

On Wednesday, November 11, 2020 at 11:38:24 AM UTC, Peter T wrote:
"Auric__" wrote in message Andrey G wrote:

Firstly, thank you very much for a prompt reply - I was afraid that this
thread might be dead.


Best thing to do is to make a new thread, and mention the subject of the
thread you want to reference (e.g. "see also the thread from January 2009,
subject 'Why can't I?'"). Replying to a 3-year-old thread is not the way
to
go.

I will try to change the pointer to 8 bit


Argh! Bytes! 8 bytes! 64 bits.

Oh dear, yes indeed bytes not bits. Hope you noticed I did at get it right,
implicitly at least, with the var-name 'gcPtrBytes' I suggested:)

Also (Andrey), again perhaps slightly pedantic, what you will be changing is
the last argument for CopyMemory, namely the length or number of bytes to
copy, from 4 in x32 to 8 in x64.

Peter T





Hi Peter,

I've tried running code with the last argument for CopyMemory changed, but Excel still crashed. Please have a look below at the details of the problem:


Environment:
Windows 10 Enterprise, 64-bit
Office Professional Plus 2016, 64-bit


'Declarations

Private Declare PtrSafe Function gethostbyname Lib "wsock32.dll" _
(ByVal hostname As String) As Long

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

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

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

Private Declare PtrSafe Function WSACleanup Lib "wsock32.dll" () As Long

Private Declare PtrSafe Function inet_ntoa Lib "wsock32.dll" _
(ByVal addr As Long) As Long

Private Declare PtrSafe Function lstrcpyA Lib "kernel32" _
(ByVal RetVal As String, _
ByVal Ptr As Long) As Long

Private Declare PtrSafe Function gethostname Lib "wsock32.dll" _
(ByVal szHost As String, _
ByVal dwHostLen As Long) As Long



' String sHostName is passed to GetIPFromHostName function correctly


Private Function GetIPFromHostName(ByVal sHostName As String) As String

'converts a host name to an IP address

Dim nbytes As LongPtr
Dim ptrHosent As LongPtr 'address of HOSENT structure
Dim ptrName As LongPtr 'address of name pointer
Dim ptrAddress As LongPtr 'address of address pointer
Dim ptrIPAddress As LongPtr
Dim ptrIPAddress2 As LongPtr

ptrHosent = gethostbyname(sHostName & vbNullChar)

If ptrHosent < 0 Then

'assign pointer addresses and offset

'Null-terminated list of addresses for the host.
'The Address is offset 12 bytes from the start of
'the HOSENT structure. Note: Here we are retrieving
'only the first address returned. To return more than
'one, define sAddress as a string array and loop through
'the 4-byte ptrIPAddress members returned. The last
'item is a terminating null. All addresses are returned
'in network byte order.
ptrAddress = ptrHosent + 12

'get the IP address
CopyMemory ptrAddress, ByVal ptrAddress, 8 '<----- Excel crashes on this line!
CopyMemory ptrIPAddress, ByVal ptrAddress, 8
CopyMemory ptrIPAddress2, ByVal ptrIPAddress, 8

GetIPFromHostName = GetInetStrFromPtr(ptrIPAddress2)

End If

End Function


Crash details:
Faulting application name: EXCEL.EXE, version: 16.0.5071.1000, time stamp: 0x5f629c9d Faulting module name: ntdll.dll, version: 10.0.18362.1139, time stamp: 0x468a1bf2 Exception code: 0xc0000028

KR
Andrey
  #39   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 88
Default IP address to Hostname in Excel

"Andrey G" wrote in message
Hi Peter,

I've tried running code with the last argument for CopyMemory changed, but
Excel still crashed. Please have a look below at the details of the
problem:

[snip]
'get the IP address
CopyMemory ptrAddress, ByVal ptrAddress, 8 '<----- Excel crashes
on this line!
CopyMemory ptrIPAddress, ByVal ptrAddress, 8
CopyMemory ptrIPAddress2, ByVal ptrIPAddress, 8

GetIPFromHostName = GetInetStrFromPtr(ptrIPAddress2)

End If

End Function


Hmm, not sure where the root of the problem is, though for me it crashes on
the second CopyMemory, I need to get the rest of the original example. I'll
try and look at it over the W/E.

Peter T


  #40   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 7
Default IP address to Hostname in Excel

On Thursday, November 12, 2020 at 11:37:20 AM UTC, Peter T wrote:
"Andrey G" wrote in message
Hi Peter,

I've tried running code with the last argument for CopyMemory changed, but
Excel still crashed. Please have a look below at the details of the
problem:

[snip]
'get the IP address
CopyMemory ptrAddress, ByVal ptrAddress, 8 '<----- Excel crashes
on this line!
CopyMemory ptrIPAddress, ByVal ptrAddress, 8
CopyMemory ptrIPAddress2, ByVal ptrIPAddress, 8

GetIPFromHostName = GetInetStrFromPtr(ptrIPAddress2)

End If

End Function

Hmm, not sure where the root of the problem is, though for me it crashes on
the second CopyMemory, I need to get the rest of the original example. I'll
try and look at it over the W/E.

Peter T



Hi Peter,
Thank you very much for looking into it!
Just to be absolutely clear:
As I'd said I've inherited the file, using original code from he http://vbnet.mvps.org/index.html?cod...byhostname.htm
Above I've posted simplified original code from he http://vbnet.mvps.org/index.html?cod...k/iplookup.htm
In my case, Excel crashes on both occasions on CopyMemory line of Function GetIPFromHostName().
KR
Andrey
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Hostname from IP address NickT Excel Worksheet Functions 1 April 9th 10 02:31 PM
How do I avoid excel change absolute address to relative address Miguel Excel Discussion (Misc queries) 3 May 10th 07 11:18 PM
Converting list of IP Addresses to list of Hostname M. Eteum Excel Worksheet Functions 0 March 23rd 06 07:16 PM
Get IP address for a remote computer based on its hostname stabilo Excel Programming 1 February 14th 06 08:08 PM
LINKING Address cells from an EXCEL spreadsheet to fill MapQuest Address Info Duane S. Meyer Excel Programming 0 August 30th 03 12:16 AM


All times are GMT +1. The time now is 04:25 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"