arrowtech
asked on
Nslookup in Excel
I have an excel spreadsheet with about 300 lines in it, each with a hostname on it.
I want excel to do an nslookup on each host and make a cell next to the hostname the IP address that is returned.
I believe this might be able to be done with some sort of VB shennanigans, but I have no VB skills?
Is this easy enough for someone to hook me up with a cut and paste job?
A
I want excel to do an nslookup on each host and make a cell next to the hostname the IP address that is returned.
I believe this might be able to be done with some sort of VB shennanigans, but I have no VB skills?
Is this easy enough for someone to hook me up with a cut and paste job?
A
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Of course not! Send royalty payments to my Pay Pal account.
Kevin
Kevin
ASKER
Holy Crap - I hope you just had that lying around and didn't code it up for me!
That is absolutely incredible, thankyou so much for that!
A
That is absolutely incredible, thankyou so much for that!
A
Holy code library! I did. And you're welcome.
Kevin
Kevin
Jim,
If you are going to use the code, please replace this function as I found a bug in it:
Public Function GetIPAddressFromString( _
ByVal IPAddress As String _
) As Long
' Return the long form of the string IP address.
Dim Octets As Variant
Dim HexString As String
Dim Index As Long
Octets = Split(IPAddress, ".")
If UBound(Octets) <> 3 Then Exit Function
For Index = 0 To 3
If Not IsNumeric(Octets(Index)) Then Exit Function
Next Index
GetIPAddressFromString = Octets(0) + Octets(1) * 256 ^ 1 + Octets(2) * 256 ^ 2 + Octets(3) * 256 ^ 3
End Function
A - this fix won't affect your solution but you should still modify the code anyway in case you use it again in the future.
Kevin
If you are going to use the code, please replace this function as I found a bug in it:
Public Function GetIPAddressFromString( _
ByVal IPAddress As String _
) As Long
' Return the long form of the string IP address.
Dim Octets As Variant
Dim HexString As String
Dim Index As Long
Octets = Split(IPAddress, ".")
If UBound(Octets) <> 3 Then Exit Function
For Index = 0 To 3
If Not IsNumeric(Octets(Index)) Then Exit Function
Next Index
GetIPAddressFromString = Octets(0) + Octets(1) * 256 ^ 1 + Octets(2) * 256 ^ 2 + Octets(3) * 256 ^ 3
End Function
A - this fix won't affect your solution but you should still modify the code anyway in case you use it again in the future.
Kevin
Kevin,
It just so happened that I was in the process of building a function similar to these: Get an IP Address for the Local Host. It involves calling the 'gethostname' and 'gethostbyname' routines from the WINSOCK library to return all of the valid IP addresses for the Local Host. It will be nice to see if your routines can improve the process.
Thanks!
Jim
It just so happened that I was in the process of building a function similar to these: Get an IP Address for the Local Host. It involves calling the 'gethostname' and 'gethostbyname' routines from the WINSOCK library to return all of the valid IP addresses for the Local Host. It will be nice to see if your routines can improve the process.
Thanks!
Jim
Anyway of doing the reverse looking at the ip and getting the webaddress?
Wow, Kevin! You don't mind if I borrow some of this, do you?
Nice!
:)
Jim