PDA

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?