How to retrieve this computer's IP address?

Question

What is the least cumbersome (module-inclusion, code lenght, etc) way to retrieve the machine IP address (of the first interface open)? I know of some solutions using MSINET, but I believe we can do better. Don't reply

Function HomeIP() as Atring
HomeIP= "127.0.0.1"
End Function

because it's not that funny... or correct. The scenario is a question wiht a document ID feature I'm trying to build a reply for.

Answer 1

See: Perform IP Lookups and resolve addresses

Answer 2

You could execute the shell command ipconfig and parse the returned results?

Answer 3

A couple of examples I found:-

http://www.everythingaccess.com/tutorials.asp?ID=Get-all-IP-Addresses-of-your-machine

http://puremis.net/excel/code/079.shtml

EDIT

Here is the code from the first link with slight modification

Option Explicit

' VBA MODULE: Get all IP Addresses of your machine
' (c) 2005 Wayne Phillips (http://www.everythingaccess.com)
' Written 18/05/2005
'
' REQUIREMENTS: Windows 98 or above, Access 97 and above
'
' Please read the full tutorial here:
' http://www.everythingaccess.com/tutorials.asp?ID=Get-all-IP-Addresses-of-your-machine
'
' Please leave the copyright notices in place.
' Thank you.
'
'Option Compare Database

'A couple of API functions we need in order to query the IP addresses in this machine
Public Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As Long)
Public Declare Function GetIpAddrTable Lib "Iphlpapi" (pIPAdrTable As Byte, pdwSize As Long, ByVal Sort As Long) As Long

'The structures returned by the API call GetIpAddrTable...
Type IPINFO
    dwAddr As Long         ' IP address
    dwIndex As Long         ' interface index
    dwMask As Long         ' subnet mask
    dwBCastAddr As Long     ' broadcast address
    dwReasmSize As Long    ' assembly size
    Reserved1 As Integer
    Reserved2 As Integer
End Type

Public Function ConvertIPAddressToString(longAddr As Long) As String

    Dim IPBytes(3) As Byte
    Dim lngCount As Long

    'Converts a long IP Address to a string formatted 255.255.255.255
    'Note: Could use inet_ntoa instead

    CopyMemory IPBytes(0), longAddr, 4 ' IP Address is stored in four bytes (255.255.255.255)

    'Convert the 4 byte values to a formatted string
    While lngCount < 4

        ConvertIPAddressToString = ConvertIPAddressToString + _
                                    CStr(IPBytes(lngCount)) + _
                                    IIf(lngCount < 3, ".", "")

        lngCount = lngCount + 1

    Wend

End Function

Public Function GetFirstNonLocalIPAddress()

    Dim Ret As Long, Tel As Long
    Dim bytBuffer() As Byte
    Dim IPTableRow As IPINFO
    Dim lngCount As Long
    Dim lngBufferRequired As Long
    Dim lngStructSize As Long
    Dim lngNumIPAddresses As Long
    Dim strIPAddress As String

On Error GoTo ErrorHandler:

    Call GetIpAddrTable(ByVal 0&, lngBufferRequired, 1)

    If lngBufferRequired > 0 Then

        ReDim bytBuffer(0 To lngBufferRequired - 1) As Byte

        If GetIpAddrTable(bytBuffer(0), lngBufferRequired, 1) = 0 Then

            'We've successfully obtained the IP Address details...

            'How big is each structure row?...
            lngStructSize = LenB(IPTableRow)

            'First 4 bytes is a long indicating the number of entries in the table
            CopyMemory lngNumIPAddresses, bytBuffer(0), 4

            While lngCount < lngNumIPAddresses

                'bytBuffer contains the IPINFO structures (after initial 4 byte long)
                CopyMemory IPTableRow, _
                            bytBuffer(4 + (lngCount * lngStructSize)), _
                            lngStructSize

                strIPAddress = ConvertIPAddressToString(IPTableRow.dwAddr)

                If Not ((strIPAddress = "127.0.0.1")) Then

                    GetFirstNonLocalIPAddress = strIPAddress
                    Exit Function

                End If

                lngCount = lngCount + 1

            Wend

        End If

    End If

Exit Function

ErrorHandler:
    MsgBox "An error has occured in GetIPAddresses():" & vbCrLf & vbCrLf & _
            Err.Description & " (" & CStr(Err.Number) & ")"

End Function

Answer 4

Codeproject has a good article on how to do this with .net: http://www.codeproject.com/KB/cs/network.aspx

You could always create a console executable out of this, and call it from VBA.

RO

Answer 5

There is another easy way using ipconfig. http://www.vbaexpress.com/kb/getarticle.php?kb_id=537

Answer 6

Maybe

http://www.dailydoseofexcel.com/archives/2009/02/12/ego-charts/

Answer 7

nbtstat -n might do the job on XP anyway. Not sure about other Windows versions or about localisation in other languages. Partial sample output:

C:\Documents and Settings\colin>nbtstat -n

Local Area Connection: Node IpAddress: [192.168.1.100] Scope Id: []

            NetBIOS Local Name Table

etc.

Answer 8

Here's an adapted example from Technet:

Function GetIPAddress()
    Const strComputer As String = "."   ' Computer name. Dot means local computer
    Dim objWMIService, IPConfigSet, IPConfig, IPAddress, i
    Dim strIPAddress As String

    ' Connect to the WMI service
    Set objWMIService = GetObject("winmgmts:" _
        & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")

    ' Get all TCP/IP-enabled network adapters
    Set IPConfigSet = objWMIService.ExecQuery _
        ("Select * from Win32_NetworkAdapterConfiguration Where IPEnabled=TRUE")

    ' Get all IP addresses associated with these adapters
    For Each IPConfig In IPConfigSet
        IPAddress = IPConfig.IPAddress
        If Not IsNull(IPAddress) Then
            strIPAddress = strIPAddress & Join(IPAddress, ", ")
        End If
    Next

    GetIPAddress = strIPAddress
End Function

It requires that you have Microsoft WMI Scripting Library in the project's references.