IP address to Hostname in Excel
in [Programming]
|
From: joel on 11 Apr 2010 07:12 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/showthread.php?t=194661 http://www.thecodecage.com/forumz
From: nicktruman on 11 Apr 2010 07:34 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.excel.programming/IP-address-to-Hostname-in-Excel
From: joel on 11 Apr 2010 08:08 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/showthread.php?t=194661 http://www.thecodecage.com/forumz
From: nicktruman on 11 Apr 2010 08:19 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.excel.programming/IP-address-to-Hostname-in-Excel
From: joel on 11 Apr 2010 10:10
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/showthread.php?t=194661 http://www.thecodecage.com/forumz |