A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Programming
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

IP address to Hostname in Excel



 
 
Thread Tools Display Modes
  #11  
Old April 11th 10, 09:53 PM posted to microsoft.public.excel.programming
Chip Pearson
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

Ads
  #12  
Old April 11th 10, 11:37 PM posted to microsoft.public.excel.programming
joel[_881_]
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

  #13  
Old April 12th 10, 05:28 AM posted to microsoft.public.excel.programming
nicktruman
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
  #14  
Old April 12th 10, 11:38 AM posted to microsoft.public.excel.programming
joel[_883_]
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

  #15  
Old April 12th 10, 12:23 PM posted to microsoft.public.excel.programming
nicktruman
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
  #16  
Old April 12th 10, 01:12 PM posted to microsoft.public.excel.programming
joel[_885_]
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

  #17  
Old April 12th 10, 01:20 PM posted to microsoft.public.excel.programming
nicktruman
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
  #18  
Old April 10th 17, 09:14 AM 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?
  #19  
Old April 10th 17, 10:53 AM
cacuoc334465c cacuoc334465c is offline
Banned
 
First recorded activity by ExcelBanter: Apr 2017
Posts: 20
Default

Cảm ơn bạn vì b*i viết rất bổ *ch v* thú vị
  #20  
Old April 10th 17, 11:25 AM posted to microsoft.public.excel.programming
Peter T[_7_]
external usenet poster
 
Posts: 131
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?



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

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 06:16 PM
Get IP address for a remote computer based on its hostname stabilo Excel Programming 1 February 14th 06 07: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:40 AM.


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