IP address to Hostname in Excel

[nicktruman]

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

 

[nicktruman]

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

Cheer
Nick

 

[nicktruman]

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 -
where the output would be (as in this case (dhcp.trcy.mi.charter.com

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

 

[JLatham]

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.

 

[JLatham]

Never mind the request for the URL, found it:
http://vbnet.mvps.org/code/network/hostnamefromip.htm

 

[JLatham]

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

 

[Chip Pearson]

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

 

[nicktruman]

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

 

[nicktruman]

Hi Joe
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
Cheer
Nick

 

[nicktruman]

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

Nick