View Full Version : Solved: IP Address
jwilder1
07-03-2005, 03:55 AM
Hello, everyone. Is there code that will access and copy the current IP address to a cell in a workbook?
MOS MASTER
07-03-2005, 05:10 AM
Hi, :yes
Doable not easy use Colo's code: click here (http://puremis.net/excel/code/079.shtml)
To get the result in the active cell adjust the sub: "TestingFunction" to:
Sub TestingFunction()
If SocketsInitialize() Then
ActiveCell.Value = _
"IP address of " & GetPcName & " is " & GetIPFromHostName(GetPcName)
End If
SocketsCleanup
End Sub
Enjoy! :whistle:
brettdj
07-03-2005, 05:33 AM
This code dumps your static IP to A1. It was a little messier than I thought it may be ..
Sub IPtest()
Dim wsh As Object, RegEx As Object, RegM As Object, fs As Object, fil As Object, TempFil As String
Set wsh = CreateObject("WScript.Shell")
Set fs = CreateObject("Scripting.FileSystemObject")
Set RegEx = CreateObject("vbscript.regexp")
TempFil = "C:\myip.txt"
wsh.Run "%comspec% /c ipconfig > " & TempFil, 0, True
RegEx.Pattern = "(\d{1,3}\.){3}\d{1,3}"
RegEx.MultiLine = True
Set fil = fs.GetFile(TempFil)
Set ts = fil.OpenAsTextStream(1)
tx = ts.ReadAll
Set RegM = RegEx.Execute(tx)
Range("a1") = RegM(0)
ts.Close
Kill TempFil
Set wsh = Nothing
Set fs = Nothing
Set RegM = Nothing
Set RegEx = Nothing
End Sub
Cheers
Dave
jwilder1
07-03-2005, 05:48 AM
Joost Verdaasdonk, your reply worked like a charm, haven't tried the other yet. Thanks to all
MOS MASTER
07-03-2005, 06:20 AM
You're Welcome! :beerchug:
Ps dave your code uses late binding so ForReading should be 1 and there are two dims missing.
So add:
Dim ts As Object, tx As String
And
Set ts = fil.OpenAsTextStream(1)
Works well btw! :whistle:
sheeeng
07-03-2005, 07:55 AM
This code dumps your static IP to A1. It was a little messier than I thought it may be ..
Sub IPtest()
Dim wsh As Object, RegEx As Object, RegM As Object, fs As Object, fil As Object, TempFil As String
Set wsh = CreateObject("WScript.Shell")
Set fs = CreateObject("Scripting.FileSystemObject")
Set RegEx = CreateObject("vbscript.regexp")
TempFil = "C:\myip.txt"
wsh.Run "%comspec% /c ipconfig > " & TempFil, 0, True
RegEx.Pattern = "(\d{1,3}\.){3}\d{1,3}"
RegEx.MultiLine = True
Set fil = fs.GetFile(TempFil)
Set ts = fil.OpenAsTextStream(1)
tx = ts.ReadAll
Set RegM = RegEx.Execute(tx)
Range("a1") = RegM(0)
ts.Close
Kill TempFil
Set wsh = Nothing
Set fs = Nothing
Set RegM = Nothing
Set RegEx = Nothing
End Sub
Cheers
Dave
Great Code..:thumb
I never thought of using XL to display IP.....
Learn a new thing today...:friends:
Thx...
sheeeng
07-03-2005, 08:03 AM
Hi, :yes
Doable not easy use Colo's code: click here (http://puremis.net/excel/code/079.shtml)
To get the result in the active cell adjust the sub: "TestingFunction" to:
Sub TestingFunction()
If SocketsInitialize() Then
ActiveCell.Value = _
"IP address of " & GetPcName & " is " & GetIPFromHostName(GetPcName)
End If
SocketsCleanup
End Sub
Enjoy! :whistle:
Another Marvelous Code...:thumb
Gotta understand it....
Thx :friends:
brettdj
07-03-2005, 06:18 PM
Hi sheeeng,
No problem :)
Colo's code is great as per normal but as I don't know much about API calls and they can be lengthy, I prefer to work within "normal" VBA.
My code goes to more effort than I orginally thought would be needed. The logic is straightforward enough, it runs the ipconfig statement from the command line using windows scripting and dumps it to a file, opens the file using file scripting and then uses a RegEx to parse it. Its rare indeed that I've had to access three objects in one small chunk of code
Cheers
Dave
sheeeng
07-03-2005, 06:49 PM
Hi sheeeng,
No problem :)
Colo's code is great as per normal but as I don't know much about API calls and they can be lengthy, I prefer to work within "normal" VBA.
My code goes to more effort than I orginally thought would be needed. The logic is straightforward enough, it runs the ipconfig statement from the command line using windows scripting and dumps it to a file, opens the file using file scripting and then uses a RegEx to parse it. Its rare indeed that I've had to access three objects in one small chunk of code
Cheers
Dave
Thanks Dave!
What is RegEx used for?
What does parsing does?
Thx.:friends:
brettdj
07-03-2005, 07:05 PM
Its a powerful object used to parse (selectively find, manipulate, replace etc) text
See http://www.vbaexpress.com/kb/getarticle.php?kb_id=68 (http://www.vbaexpress.com/kb/getarticle.php?kb_id=68&PHPSESSID=7a075a5afd4e8ed3618a90b78a9ded76) for some Excel examples
Ken Puls
07-03-2005, 08:17 PM
Dave!
KB Entry! KB Entry! ;)
MOS MASTER
07-03-2005, 08:30 PM
Dave!
KB Entry! KB Entry! ;)
Indeed, indeed...:yes
brettdj
07-03-2005, 11:45 PM
Done ....
Ivan F Moala
07-04-2005, 12:04 AM
For something like this, and if you need it (code) to be shorter, not that it makes a diff) then WMI is better suited, although I still prefer APIs.
Sub GetIPAddress()
Dim objWMIService As Object
Dim objColIPConfig As Object
Dim objIPConfig As Object
Dim i As Integer
'// Note "." is your Local computer in the below const, change for remote Pc by puting in its name
Const str = "winmgmts:\\" & "." & "\root\cimv2"
Const scr = "Select * from Win32_NetworkAdapterConfiguration where IPEnabled=TRUE"
Set objWMIService = GetObject(str)
Set objColIPConfig = objWMIService.ExecQuery(scr)
For Each objIPConfig In objColIPConfig
If Not IsNull(objIPConfig.IPAddress) Then
For i = LBound(objIPConfig.IPAddress) To UBound(objIPConfig.IPAddress)
MsgBox objIPConfig.IPAddress(i), vbApplicationModal, objIPConfig.Description(i)
Next
End If
Next
End Sub
brettdj
07-04-2005, 12:22 AM
Dang .... thats certainly neater than mine.
msmith
07-05-2005, 04:38 PM
Looks great Ivan....:beerchug:
Add a user input box and change the contant and its a little more user friendly!
Sub GetIPAddress()
Dim objWMIService As Object
Dim objColIPConfig As Object
Dim objIPConfig As Object
Dim i As Integer
Dim str As String
Dim computername As String
computername = Application.InputBox(prompt:="Please enter a computer name to obtain an IP address", Title:="Obtain IP Address")
str = "winmgmts:\\" & computername & "\root\cimv2"
Const scr = "Select * from Win32_NetworkAdapterConfiguration where IPEnabled=TRUE"
Set objWMIService = GetObject(str)
Set objColIPConfig = objWMIService.ExecQuery(scr)
For Each objIPConfig In objColIPConfig
If Not IsNull(objIPConfig.IPAddress) Then
For i = LBound(objIPConfig.IPAddress) To UBound(objIPConfig.IPAddress)
MsgBox objIPConfig.IPAddress(i), vbApplicationModal, objIPConfig.Description(i)
Next
End If
Next
End Sub
For something like this, and if you need it (code) to be shorter, not that it makes a diff) then WMI is better suited, although I still prefer APIs.
Sub GetIPAddress()
Dim objWMIService As Object
Dim objColIPConfig As Object
Dim objIPConfig As Object
Dim i As Integer
'// Note "." is your Local computer in the below const, change for remote Pc by puting in its name
Const str = "winmgmts:\\" & "." & "\root\cimv2"
Const scr = "Select * from Win32_NetworkAdapterConfiguration where IPEnabled=TRUE"
Set objWMIService = GetObject(str)
Set objColIPConfig = objWMIService.ExecQuery(scr)
For Each objIPConfig In objColIPConfig
If Not IsNull(objIPConfig.IPAddress) Then
For i = LBound(objIPConfig.IPAddress) To UBound(objIPConfig.IPAddress)
MsgBox objIPConfig.IPAddress(i), vbApplicationModal, objIPConfig.Description(i)
Next
End If
Next
End Sub
Ivan F Moala
07-05-2005, 07:58 PM
msmith
Thanks :)
Yes, add that .... I guess if you were to go the whole way then give the user
a default + handle cancel and also Errors .... so
Sub GetIPAddress()
Dim objWMIService As Object
Dim objColIPConfig As Object
Dim objIPConfig As Object
Dim i As Integer
Dim str As String
Dim Computername As String
Const scr = "Select * from Win32_NetworkAdapterConfiguration where IPEnabled=TRUE"
Computername = Application.InputBox( _
prompt:="Please enter a computer name to obtain an IP address" & vbCrLf & _
"Or Enter to use local computer", _
Default:=".", _
Title:="Obtain IP Address")
If Computername = "False" Then Exit Sub
str = "winmgmts:\\" & Computername & "\root\cimv2"
On Error GoTo ErrH
Set objWMIService = GetObject(str)
Set objColIPConfig = objWMIService.ExecQuery(scr)
For Each objIPConfig In objColIPConfig
If Not IsNull(objIPConfig.IPAddress) Then
For i = LBound(objIPConfig.IPAddress) To UBound(objIPConfig.IPAddress)
MsgBox objIPConfig.IPAddress(i), vbApplicationModal, objIPConfig.Description(i)
Next
End If
Next
CleanUp:
Set objWMIService = Nothing
Set objColIPConfig = Nothing
Exit Sub
ErrH:
MsgBox "Error " & Err.Number & ":=" & Err.Description, vbMsgBoxHelpButton, "WMI Error", Err.HelpFile, Err.HelpContext
Resume CleanUp
End Sub
Thanks for your input
martinr
12-09-2013, 10:05 PM
This VBA code (to return IP address) works fine with Window XP but not with windows 7 - does
anybody know how to achieve this in Windows 7?
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.