Solved: IP address

[justdriving]

I know that following VBA can read IP address after installing following references: -

• "Microsoft Script Control 1.0"
• "Microsoft Scripting Runtime"
• "Microsoft WMI Scripting V1.1 Library"

[vba]

Set objShell = WScript.CreateObject("WScript.Shell")
Set objExecObject = objShell.Exec("%comspec% /c ipconfig.exe")
Do Until objExecObject.StdOut.AtEndOfStream
strLine = objExecObject.StdOut.ReadLine()
strIP = InStr(strLine, "Address")
If strIP <> 0 Then
IPArray = Split(strLine, ":")
strIPAddress = IPArray(1)
End If
Loop

[/vba]

Now, I wanted to create a TEXT file log (.txt), in which I wanted to write IP address of user in a new line, everytime he opens or closes EXCEL file.

I am eagerly waiting for Ken to join this forum. I am optimistic he also can help me. Thank to all of you, who have read this post.

[Kenneth Hobs]

I would not use references.
[VBA]Sub IPConfig()
Dim objShell As Object, objExecObject As Object
Dim strLine As String, strIP As String, IPArray() As String, strIPAddress As String

Set objShell = CreateObject("WScript.Shell")
Set objExecObject = objShell.Exec("%comspec% /c ipconfig.exe")
Do Until objExecObject.StdOut.AtEndOfStream
strLine = objExecObject.StdOut.ReadLine()
strIP = InStr(strLine, "Address")
If strIP <> 0 Then
IPArray = Split(strLine, ":")
strIPAddress = IPArray(1)
End If
Loop
MsgBox strIPAddress
End Sub[/VBA]

Using WMI:
[VBA]'http://msdn.microsoft.com/en-us/library/windows/desktop/aa394595%28v=vs.85%29.aspx
Sub WMIip()
Dim objWMIService As Object, colItems As Variant, objItem As Object
Set objWMIService = GetObject("winmgmts:\\.\root\CIMV2")
Set colItems = objWMIService.ExecQuery( _
"Select IpAddress From Win32_NetworkAdapterConfiguration Where IPEnabled=TRUE")

For Each objItem In colItems
MsgBox objItem.ipaddress(0)
Exit For
Next objItem
End Sub[/VBA]

You can use this method to append text to a txt file.
[VBA]Function AppendToTXTFile(strFile As String, strData As String) As Boolean
Dim iHandle As Integer
iHandle = FreeFile
Open strFile For Append Access Write As #iHandle
Print #iHandle, strData
Close #iHandle
AppendToTXTFile = True
End Function[/VBA]

[Paul_Hossler]

Another way

[VBA]
Option Explicit
Private Const WS_VERSION_REQD = &H101
Private Const WS_VERSION_MAJOR = WS_VERSION_REQD \ &H100 And &HFF&
Private Const WS_VERSION_MINOR = WS_VERSION_REQD And &HFF&
Private Const MIN_SOCKETS_REQD = 1
Private Const SOCKET_ERROR = -1
Private Const WSADescription_Len = 256
Private Const WSASYS_Status_Len = 128

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
iMaxUdpDg As Integer
lpszVendorInfo As Long
End Type

Private Type HOSTENT
hName As Long
hAliases As Long
hAddrType As Integer
hLength As Integer
hAddrList As Long
End Type
Private Declare Function WSAGetLastError Lib "WSOCK32.DLL" () As Long
Private Declare Function WSAStartup Lib "WSOCK32.DLL" (ByVal wVersionRequired As Integer, lpWSAData As WSADATA) As Long
Private Declare Function WSACleanup Lib "WSOCK32.DLL" () As Long
Private Declare Function gethostname Lib "WSOCK32.DLL" (ByVal hostname$, ByVal HostLen As Long) As Long
Private Declare Function gethostbyname Lib "WSOCK32.DLL" (ByVal hostname$) As Long
Private Declare Sub RtlMoveMemory Lib "kernel32" (hpvDest As Any, ByVal hpvSource&, ByVal cbCopy&)

Sub test()
MsgBox GetIP

End Sub
Function GetIP() As String
Dim hostname As String * 256
Dim hostent_addr As Long
Dim host As HOSTENT
Dim hostip_addr As Long
Dim temp_ip_address() As Byte
Dim i As Integer
Dim ip_address As String
SocketsInitialize
If gethostname(hostname, 256) = SOCKET_ERROR Then
MsgBox "Windows Sockets error " & Str(WSAGetLastError())
Exit Function
Else
hostname = Trim$(hostname)
End If
hostent_addr = gethostbyname(hostname)
If hostent_addr = 0 Then
MsgBox "Winsock.dll is not responding."
Exit Function
End If
RtlMoveMemory host, hostent_addr, LenB(host)
RtlMoveMemory hostip_addr, host.hAddrList, 4
'get all of the IP address if machine is multi-homed
ReDim temp_ip_address(1 To host.hLength)
RtlMoveMemory temp_ip_address(1), hostip_addr, host.hLength

For i = 1 To host.hLength
ip_address = ip_address & temp_ip_address(i) & "."
Next

ip_address = Mid$(ip_address, 1, Len(ip_address) - 1)
SocketsCleanup

GetIP = ip_address
End Function

Private Sub SocketsInitialize()
Dim WSAD As WSADATA
Dim iReturn As Integer
Dim sLowByte As String, sHighByte As String, sMsg As String
iReturn = WSAStartup(WS_VERSION_REQD, WSAD)
If iReturn <> 0 Then
MsgBox "Winsock.dll is not responding."
End
End If
If LObyte(WSAD.wversion) < WS_VERSION_MAJOR Or (LObyte(WSAD.wversion) = _
WS_VERSION_MAJOR And HIbyte(WSAD.wversion) < WS_VERSION_MINOR) Then
sHighByte = Trim$(Str$(HIbyte(WSAD.wversion)))
sLowByte = Trim$(Str$(LObyte(WSAD.wversion)))
sMsg = "Windows Sockets version " & sLowByte & "." & sHighByte
sMsg = sMsg & " is not supported by winsock.dll "
MsgBox sMsg
End
End If
'iMaxSockets is not used in winsock 2. So the following check is only
'necessary for winsock 1. If winsock 2 is requested,
'the following check can be skipped.
If WSAD.iMaxSockets < MIN_SOCKETS_REQD Then
sMsg = "This application requires a minimum of "
sMsg = sMsg & Trim$(Str$(MIN_SOCKETS_REQD)) & " supported sockets."
MsgBox sMsg
End
End If
End Sub
Private Sub SocketsCleanup()
Dim lReturn As Long
lReturn = WSACleanup()
If lReturn <> 0 Then
MsgBox "Socket error " & Trim$(Str$(lReturn)) & " occurred in Cleanup "
End
End If
End Sub

Private Function HIbyte(ByVal wParam As Integer)
HIbyte = wParam \ &H100 And &HFF&
End Function
Private Function LObyte(ByVal wParam As Integer)
LObyte = wParam And &HFF&
End Function
[/VBA]

Paul

[justdriving]

Many thanks, Mr. Ken.
Many thanks, Mr. Paul.