Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #41   Report Post  
Old November 18th 20, 10:25 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2020
Posts: 7
Default IP address to Hostname in Excel

On Wednesday, November 18, 2020 at 10:02:17 AM UTC, Peter T wrote:
"Andrey G" wrote in message
...
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

Sorry to take a while to get back, I got hijacked with work!

In your original code, as well as changing the lengths of the 3 pointers
from 4 to 8, also change
ptrAddress = ptrHosent + 12 ' 3x4
to
ptrAddress = ptrHosent + 24 ' 3x8

Peter T



No joy, I'm afraid - Excel is still crashing on the very same line.
I wonder if I made an error in declarations? The code I'd posted above is the exact code I'm using.
Andrey G

  #42   Report Post  
Old November 18th 20, 11:51 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2019
Posts: 72
Default IP address to Hostname in Excel


"Andrey G" wrote in message
On Wednesday, November 18, 2020 at 10:02:17 AM UTC, Peter T wrote:
"Andrey G" wrote in message
...
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

Sorry to take a while to get back, I got hijacked with work!

In your original code, as well as changing the lengths of the 3 pointers
from 4 to 8, also change
ptrAddress = ptrHosent + 12 ' 3x4
to
ptrAddress = ptrHosent + 24 ' 3x8

Peter T



No joy, I'm afraid - Excel is still crashing on the very same line.
I wonder if I made an error in declarations? The code I'd posted above is
the exact code I'm using.
Andrey G


I couldn't work with what you posted because it is incomplete, so I adapted
Randy Birch's originals from the links you posted and both working fine for
me in x64, with the 1224 change.

At a quick glance of what you posted 'addr' in the inet_ntoa API should be
As LongPtr not Long but there may well be others. Look for things like addr,
ptr, an h prefix for handle. These are typically what need to be changed
along with related API return types. Ensure your module is headed Option
Explicit and do debug/compile.

Note the 8-byte lengths and that 24 are specific for use with #Win64, not
#VBA7.

Peter T


  #43   Report Post  
Old November 22nd 20, 08:33 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2020
Posts: 7
Default IP address to Hostname in Excel

On Wednesday, November 18, 2020 at 10:51:23 PM UTC, Peter T wrote:
"Andrey G" wrote in message
On Wednesday, November 18, 2020 at 10:02:17 AM UTC, Peter T wrote:
"Andrey G" wrote in message
...
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
Sorry to take a while to get back, I got hijacked with work!

In your original code, as well as changing the lengths of the 3 pointers
from 4 to 8, also change
ptrAddress = ptrHosent + 12 ' 3x4
to
ptrAddress = ptrHosent + 24 ' 3x8

Peter T



No joy, I'm afraid - Excel is still crashing on the very same line.
I wonder if I made an error in declarations? The code I'd posted above is
the exact code I'm using.
Andrey G

I couldn't work with what you posted because it is incomplete, so I adapted
Randy Birch's originals from the links you posted and both working fine for
me in x64, with the 1224 change.

At a quick glance of what you posted 'addr' in the inet_ntoa API should be
As LongPtr not Long but there may well be others. Look for things like addr,
ptr, an h prefix for handle. These are typically what need to be changed
along with related API return types. Ensure your module is headed Option
Explicit and do debug/compile.

Note the 8-byte lengths and that 24 are specific for use with #Win64, not
#VBA7.

Peter T




Hi Peter,
I'm at the end of my tether now: I followed your instructions (at least I hope I did) and tried many times, but the stubborn thing is still crashing on exactly the same line!
As a final resort and a last attempt to make it work, I'm posting below my entire code in a hope that whatever is wrong might be noticeable to you.
------------------------------------------------------------------------------------------------------------------------------------
Option Explicit

'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''
' Copyright ©1996-2011 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 IP_SUCCESS As Long = 0
Private Const MAX_WSADescription As Long = 256
Private Const MAX_WSASYSStatus As Long = 128
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
Private Const SOCKET_ERROR As Long = -1
Private Const ERROR_SUCCESS As Long = 0

Private Type WSADATA
wVersion As Integer
wHighVersion As Integer
szDescription(0 To MAX_WSADescription) As Byte
szSystemStatus(0 To MAX_WSASYSStatus) As Byte
wMaxSockets As Long
wMaxUDPDG As Long
dwVendorInfo As Long
End Type

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 LongPtr) As LongPtr

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

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



Private Sub cmdGet_Click()

Dim sHostName As String

If SocketsInitialize() Then

'obtain and pass the host address to the function
Text1.Text = GetMachineName()
Text2.Text = GetIPFromHostName(Text1.Text)

SocketsCleanup

Else
MsgBox "Windows Sockets for 32 bit Windows " & _
"environments is not successfully responding."
End If

End Sub


Private Function SocketsInitialize() As Boolean

Dim WSAD As WSADATA
Dim success As Long

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 GetMachineName() As String

Dim sHostName As String * 256

If gethostname(sHostName, 256) = ERROR_SUCCESS Then
GetMachineName = Trim$(sHostName)
End If

End Function


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

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

GetIPFromHostName = GetInetStrFromPtr(ptrIPAddress2)

End If

End Function


Private Function GetStrFromPtrA(ByVal lpszA As LongPtr) As String

GetStrFromPtrA = String$(lstrlenA(ByVal lpszA), 0)
Call lstrcpyA(ByVal GetStrFromPtrA, ByVal lpszA)

End Function


Private Function GetInetStrFromPtr(Address As LongPtr) As String

GetInetStrFromPtr = GetStrFromPtrA(inet_ntoa(Address))

End Function

------------------------------------------------------------------------------------------------------------------------------------
Many thanks,
Andrey G
  #44   Report Post  
Old Yesterday, 03:37 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2019
Posts: 72
Default IP address to Hostname in Excel

"Andrey G" wrote in message
...
On Wednesday, November 18, 2020 at 10:51:23 PM UTC, Peter T wrote:

[snip]
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
Sorry to take a while to get back, I got hijacked with work!

In your original code, as well as changing the lengths of the 3
pointers
from 4 to 8, also change
ptrAddress = ptrHosent + 12 ' 3x4
to
ptrAddress = ptrHosent + 24 ' 3x8

Peter T



No joy, I'm afraid - Excel is still crashing on the very same line.
I wonder if I made an error in declarations? The code I'd posted above
is
the exact code I'm using.
Andrey G

I couldn't work with what you posted because it is incomplete, so I
adapted
Randy Birch's originals from the links you posted and both working fine
for
me in x64, with the 1224 change.

At a quick glance of what you posted 'addr' in the inet_ntoa API should be
As LongPtr not Long but there may well be others. Look for things like
addr,
ptr, an h prefix for handle. These are typically what need to be changed
along with related API return types. Ensure your module is headed Option
Explicit and do debug/compile.

Note the 8-byte lengths and that 24 are specific for use with #Win64, not
#VBA7.

Peter T



Hi Peter,
I'm at the end of my tether now: I followed your instructions (at least I
hope I did) and tried many times, but the stubborn thing is still crashing
on exactly the same line!
As a final resort and a last attempt to make it work, I'm posting below my
entire code in a hope that whatever is wrong might be noticeable to you.

[snip]

Change the return type of the gethostbyname API from As Long to LongPtr

This API is not documented in Win32API_PtrSafe.txt and not directly obvious
from its name what it should return. However it needs to match the variable
it returns to, which Randy Birch helpfully documented is an address and
hence a LongLong in Win64 (or LongPtr which morphs to a LongLong in x64):
Dim ptrHosent As LongPtr ' address of HOSENT structure

If this doesn't fix it for you upload it to a file sharing site and post
back. Actually post back anyway if only to let us know it's working!

Peter T





  #45   Report Post  
Old Today, 10:50 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2020
Posts: 7
Default IP address to Hostname in Excel

On Monday, November 23, 2020 at 2:37:27 PM UTC, Peter T wrote:
"Andrey G" wrote in message
...
On Wednesday, November 18, 2020 at 10:51:23 PM UTC, Peter T wrote:

[snip]
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
Sorry to take a while to get back, I got hijacked with work!

In your original code, as well as changing the lengths of the 3
pointers
from 4 to 8, also change
ptrAddress = ptrHosent + 12 ' 3x4
to
ptrAddress = ptrHosent + 24 ' 3x8

Peter T


No joy, I'm afraid - Excel is still crashing on the very same line.
I wonder if I made an error in declarations? The code I'd posted above
is
the exact code I'm using.
Andrey G

I couldn't work with what you posted because it is incomplete, so I
adapted
Randy Birch's originals from the links you posted and both working fine
for
me in x64, with the 1224 change.

At a quick glance of what you posted 'addr' in the inet_ntoa API should be
As LongPtr not Long but there may well be others. Look for things like
addr,
ptr, an h prefix for handle. These are typically what need to be changed
along with related API return types. Ensure your module is headed Option
Explicit and do debug/compile.

Note the 8-byte lengths and that 24 are specific for use with #Win64, not
#VBA7.

Peter T



Hi Peter,
I'm at the end of my tether now: I followed your instructions (at least I
hope I did) and tried many times, but the stubborn thing is still crashing
on exactly the same line!
As a final resort and a last attempt to make it work, I'm posting below my
entire code in a hope that whatever is wrong might be noticeable to you.
[snip]

Change the return type of the gethostbyname API from As Long to LongPtr

This API is not documented in Win32API_PtrSafe.txt and not directly obvious
from its name what it should return. However it needs to match the variable
it returns to, which Randy Birch helpfully documented is an address and
hence a LongLong in Win64 (or LongPtr which morphs to a LongLong in x64):
Dim ptrHosent As LongPtr ' address of HOSENT structure
If this doesn't fix it for you upload it to a file sharing site and post
back. Actually post back anyway if only to let us know it's working!

Peter T



Peter, you're a genius!
Last change made it work - thank you very, very much!
Much obliged and all the very best,
Andrey G


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 10:12 PM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017