How to get IP address with Excel VBA code using WMI



Date: 19/04/2013Procedure: How to get IP address with Excel VBA code using WMISource: LINKPermalink: LINKCreated by: HeelpBook StaffDocument Version: 1.0-684564-678009How to get IP address with Excel VBA code using WMIQuestionWhat is the least cumbersome (module-inclusion,?code lenght, etc.) way to retrieve the machine?IP address(of the first interface open)?AnswerIt requires that you have?Microsoft WMI Scripting Library?in the project’s references. You will use that using, in a cell, the formula?=GetIPAddress().Function GetIPAddress()Const strComputer As String = “.” ‘ Computer name. Dot means local computerDim objWMIService, IPConfigSet, IPConfig, IPAddress, iDim strIPAddress As String‘ Connect to the WMI serviceSet objWMIService = GetObject(“winmgmts:” _& “{impersonationLevel=impersonate}!\\” & strComputer & “\root\cimv2″)‘ Get all TCP/IP-enabled network adaptersSet IPConfigSet = objWMIService.ExecQuery _(“Select * from Win32_NetworkAdapterConfiguration Where IPEnabled=TRUE”)‘ Get all IP addresses associated with these adaptersFor Each IPConfig In IPConfigSetIPAddress = IPConfig.IPAddressIf Not IsNull(IPAddress) ThenstrIPAddress = strIPAddress & Join(IPAddress, “, “)End IfNextGetIPAddress = strIPAddressEnd FunctionAlternative SolutionPrivate Declare Sub CopyMemory Lib “kernel32″ Alias “RtlMoveMemory” (Destination As Any, Source As Any, ByVal Length As Long)Private Declare Function GetIpAddrTable Lib “Iphlpapi” (pIPAdrTable As Byte, pdwSize As Long, ByVal Sort As Long) As LongType IPINFOdwAddr As Long ‘ IP addressdwIndex As Long ‘ interface indexdwMask As Long ‘ subnet maskdwBCastAddr As Long ‘ broadcast addressdwReasmSize As Long ‘ assembly sizeReserved1 As IntegerReserved2 As IntegerEnd TypePublic Function ConvertIPAddressToString(longAddr As Long) As StringDim IPBytes(3) As ByteDim lngCount As Long‘Converts a long IP Address to a string formatted 255.255.255.255‘Note: Could use inet_ntoa insteadCopyMemory IPBytes(0), longAddr, 4 ‘ IP Address is stored in four bytes (255.255.255.255)‘Convert the 4 byte values to a formatted stringWhile lngCount < 4ConvertIPAddressToString = ConvertIPAddressToString + _CStr(IPBytes(lngCount)) + _IIf(lngCount < 3, “.”, “”)lngCount = lngCount + 1WendEnd FunctionPublic Function GetFirstNonLocalIPAddress()Dim Ret As Long, Tel As LongDim bytBuffer() As ByteDim IPTableRow As IPINFODim lngCount As LongDim lngBufferRequired As LongDim lngStructSize As LongDim lngNumIPAddresses As LongDim strIPAddress As StringOn Error GoTo ErrorHandler:Call GetIpAddrTable(ByVal 0&, lngBufferRequired, 1)If lngBufferRequired > 0 ThenReDim bytBuffer(0 To lngBufferRequired – 1) As ByteIf 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 tableCopyMemory lngNumIPAddresses, bytBuffer(0), 4While lngCount < lngNumIPAddresses‘bytBuffer contains the IPINFO structures (after initial 4 byte long)CopyMemory IPTableRow, _bytBuffer(4 + (lngCount * lngStructSize)), _lngStructSizestrIPAddress = ConvertIPAddressToString(IPTableRow.dwAddr)If Not ((strIPAddress = “127.0.0.1″)) ThenGetFirstNonLocalIPAddress = strIPAddressExit FunctionEnd IflngCount = lngCount + 1WendEnd IfEnd IfExit FunctionErrorHandler:MsgBox “An error has occured in GetIPAddresses():” & vbCrLf & vbCrLf & _Err.Description & ” (” & CStr(Err.Number) & “)”End FunctionExternal use (in a VBScript file)Copy the following code in a text file?(.txt)?and rename it in?(.vbs)…Function GetIPAddress()Dim objWMIService, IPConfigSet, IPConfig, IPAddress, iDim strIPAddress‘ Connect to the WMI serviceSet objWMIService = GetObject(“winmgmts:” _& “{impersonationLevel=impersonate}!\\.\root\cimv2″)‘ Get all TCP/IP-enabled network adaptersSet IPConfigSet = objWMIService.ExecQuery _(“Select * from Win32_NetworkAdapterConfiguration Where IPEnabled=TRUE”)‘ Get all IP addresses associated with these adaptersFor Each IPConfig In IPConfigSetIPAddress = IPConfig.IPAddressIf Not IsNull(IPAddress) ThenstrIPAddress = strIPAddress & Join(IPAddress, “, “)End IfNextGetIPAddress = strIPAddressmsgbox(GetIPAddress)End Function ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download