Help find out TCP/IQ Address

[sanclare]

Hi All,

Can anyone help out.

I require a VBA Macro or an Excel Formula routine to
find out my computers IQ/TCP Address.

Can this be done using Excel

with regards to all
sanclare@vsnl.net

 

[Colo]

Hi sanclare, welcome! Try something like this.

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN>
<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Const</SPAN> IP_SUCCESS <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN> = 0
<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Const</SPAN> WS_VERSION_REQD <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN> = &H101
<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Const</SPAN> MIN_SOCKETS_REQD <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN> = 1
<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Const</SPAN> SOCKET_ERROR <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN> = -1
<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Const</SPAN> INADDR_NONE <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN> = &HFFFFFFFF
<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Const</SPAN> MAX_WSADescription <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN> = 256
<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Const</SPAN> MAX_WSASYSStatus <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN> = 128
<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Type</SPAN> WSADATA
    wVersion <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
    wHighVersion <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
    szDescription(0 To MAX_WSADescription) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN>
    szSystemStatus(0 To MAX_WSASYSStatus) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN>
    wMaxSockets <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    wMaxUDPDG <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    dwVendorInfo <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Type</SPAN>
<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Declare</SPAN> <SPAN style="color:#00007F">Function</SPAN> gethostbyname <SPAN style="color:#00007F">Lib</SPAN> "WSOCK32.DLL" _
                                       (<SPAN style="color:#00007F">ByVal</SPAN> hostname <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Declare</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CopyMemory <SPAN style="color:#00007F">Lib</SPAN> "kernel32" _
                               Alias "RtlMoveMemory" _
                               (xDest <SPAN style="color:#00007F">As</SPAN> Any, _
                                xSource <SPAN style="color:#00007F">As</SPAN> Any, _
                                <SPAN style="color:#00007F">ByVal</SPAN> nbytes <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>)
<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Declare</SPAN> <SPAN style="color:#00007F">Function</SPAN> WSAStartup <SPAN style="color:#00007F">Lib</SPAN> "WSOCK32.DLL" _
                                    (<SPAN style="color:#00007F">ByVal</SPAN> wVersionRequired <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, _
                                     lpWSADATA <SPAN style="color:#00007F">As</SPAN> WSADATA) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Declare</SPAN> <SPAN style="color:#00007F">Function</SPAN> WSACleanup <SPAN style="color:#00007F">Lib</SPAN> "WSOCK32.DLL" () <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Declare</SPAN> <SPAN style="color:#00007F">Function</SPAN> inet_addr <SPAN style="color:#00007F">Lib</SPAN> "WSOCK32.DLL" _
                                   (<SPAN style="color:#00007F">ByVal</SPAN> s <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Declare</SPAN> <SPAN style="color:#00007F">Function</SPAN> GetComputerName <SPAN style="color:#00007F">Lib</SPAN> "kernel32" Alias "GetComputerNameA" _
                                         (<SPAN style="color:#00007F">ByVal</SPAN> Buffer <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, _
                                          Size <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>


<SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Function</SPAN> GetIPFromHostName(<SPAN style="color:#00007F">ByVal</SPAN> sHostName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#007F00">'converts a host name to an IP address.</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> nbytes <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> ptrHosent <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>  <SPAN style="color:#007F00">'address of hostent structure</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> ptrName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>    <SPAN style="color:#007F00">'address of name pointer</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> ptrAddress <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>    <SPAN style="color:#007F00">'address of address pointer</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> ptrIPAddress <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> sAddress <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    sAddress = Space$(4)
    ptrHosent = gethostbyname(sHostName & vbNullChar)
    <SPAN style="color:#00007F">If</SPAN> ptrHosent <> 0 <SPAN style="color:#00007F">Then</SPAN>
        ptrName = ptrHosent
        ptrAddress = ptrHosent + 12
        <SPAN style="color:#007F00">'get the IP address</SPAN>
        CopyMemory ptrName, <SPAN style="color:#00007F">ByVal</SPAN> ptrName, 4
        CopyMemory ptrAddress, <SPAN style="color:#00007F">ByVal</SPAN> ptrAddress, 4
        CopyMemory ptrIPAddress, <SPAN style="color:#00007F">ByVal</SPAN> ptrAddress, 4
        CopyMemory <SPAN style="color:#00007F">ByVal</SPAN> sAddress, <SPAN style="color:#00007F">ByVal</SPAN> ptrIPAddress, 4
        GetIPFromHostName = IPToText(sAddress)
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN>

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Function</SPAN> IPToText(<SPAN style="color:#00007F">ByVal</SPAN> IPAddress <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    IPToText = <SPAN style="color:#00007F">CStr</SPAN>(Asc(IPAddress)) & "." & _
               <SPAN style="color:#00007F">CStr</SPAN>(Asc(Mid$(IPAddress, 2, 1))) & "." & _
               <SPAN style="color:#00007F">CStr</SPAN>(Asc(Mid$(IPAddress, 3, 1))) & "." & _
               <SPAN style="color:#00007F">CStr</SPAN>(Asc(Mid$(IPAddress, 4, 1)))
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN>

<SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Sub</SPAN> SocketsCleanup()
    <SPAN style="color:#00007F">If</SPAN> WSACleanup() <> 0 <SPAN style="color:#00007F">Then</SPAN>
        MsgBox "Windows Sockets error occurred in Cleanup.", vbExclamation
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Function</SPAN> SocketsInitialize() <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> WSAD <SPAN style="color:#00007F">As</SPAN> WSADATA
    SocketsInitialize = WSAStartup(WS_VERSION_REQD, WSAD) = IP_SUCCESS
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN>

<SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Function</SPAN> GetPcName() <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> strBuf <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> * 16, strPcName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, lngPc <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    lngPc = GetComputerName(strBuf, Len(strBuf))
    <SPAN style="color:#00007F">If</SPAN> lngPc <> 0 <SPAN style="color:#00007F">Then</SPAN>
        strPcName = Left(strBuf, InStr(strBuf, vbNullChar) - 1)
        GetPcName = strPcName
    <SPAN style="color:#00007F">Else</SPAN>
        GetPcName = vbNullString
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN>

<SPAN style="color:#00007F">Sub</SPAN> Test()
    <SPAN style="color:#00007F">If</SPAN> SocketsInitialize() <SPAN style="color:#00007F">Then</SPAN>
        MsgBox "IP address of " & GetPcName & " is " & GetIPFromHostName(GetPcName)
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    SocketsCleanup
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

</FONT>