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.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- how to get ip address with excel vba code using wmi
- export a text file with comma and quote delimiters
- working with variables in excel vba furman university
- creating a smart document purdue university
- this is a text file named readme
- microsoft excel
- excel chapter 12 templates styles and macros
- visual basic error codes and messages
- comma semicolon and vba strings ntnu
Related searches
- free excel vba code samples
- excel vba code library
- how to get with murder
- how to get along with coworkers quiz
- how to find ip port
- how to find an address for free
- how to get along with difficult people
- how to get a decent mortgage with bad credit
- how to get excel for free
- how to find ip address of server
- how to get excel to calculate hours
- excel vba code samples