Hi,

How can I get my the IP address of a computer using VBA? I was hoping it'd
be through environ() but it seems not...

Sam

"Sam Wilson" wrote in message
...
How can I get my the IP address of a computer using VBA? I was hoping it'd
be through environ() but it seems not...

Hi Sam,

I've posted an example of how to do this he

http://appspro.com/Tips/NetworkTips.htm#NT2

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Professional Excel Development - Second Edition *
http://www.appspro.com/Books/ProExcelDev.htm

Not as straight forward as I'd guessed!

"Rob Bovey" wrote:

"Sam Wilson" wrote in message
...
How can I get my the IP address of a computer using VBA? I was hoping it'd
be through environ() but it seems not...

Hi Sam,

I've posted an example of how to do this he

http://appspro.com/Tips/NetworkTips.htm#NT2

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Professional Excel Development - Second Edition *
http://www.appspro.com/Books/ProExcelDev.htm

Sam,

Provided the PCs are XP or later, this might do what you want:

'------------------------------------

Sub GetIPaddy()

Dim strIPAddress As String
Dim strComputerName As String

strComputerName = "."

Set objWMIService = GetObject("winmgmts:\\" & strComputerName &
"\root\cimv2")

Set colItems = objWMIService.ExecQuery _
("Select * From Win32_NetworkAdapterConfiguration Where IPEnabled =
True")

For Each objItem In colItems
For Each objAddress In objItem.IPAddress
strIPAddress = objAddress
Next
Next

MsgBox strIPAddress

Set colItems = Nothing
Set objWMIService = Nothing

End Sub


'------------------------------------

Steve Yandl



"Sam Wilson" wrote in message
...
Not as straight forward as I'd guessed!

"Rob Bovey" wrote:

"Sam Wilson" wrote in message
...
How can I get my the IP address of a computer using VBA? I was hoping
it'd
be through environ() but it seems not...

Hi Sam,

I've posted an example of how to do this he

http://appspro.com/Tips/NetworkTips.htm#NT2

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Professional Excel Development - Second Edition *
http://www.appspro.com/Books/ProExcelDev.htm

Hey! I just finished up some code using the same approach (and, from the
variable names, it looks like from the same source). I took a different end
route than you did, however, choosing to make my code a function instead.
Here is that code...

Function GetMyIPaddress() As String()
Dim X As Long, IPaddress() As String, Query As String
Dim Addresses() As String, objWMIService As Object
Dim ipConfigSet As Object, ipConfig As Object
Addresses = Split("")
Query = "Select IPAddress from Win32_NetworkAdapterConfiguration "
Set objWMIService = GetObject("winmgmts:\\.\root\cimv2")
Set ipConfigSet = objWMIService.ExecQuery(Query)
For Each ipConfig In ipConfigSet
If Not IsNull(ipConfig.IPaddress) Then
For X = LBound(ipConfig.IPaddress) To UBound(ipConfig.IPaddress)
'If ipConfig.IPaddress(X) Like "*#*.*#*.*#*.*#*" Then
ReDim Preserve Addresses(UBound(Addresses) + 1)
Addresses(UBound(Addresses)) = ipConfig.IPaddress(X)
'End If
Next
End If
Next
GetMyIPaddress = Addresses
End Function

The code returns a String array containing all the IP address on the system.
In my case, it found two...

192.168.1.3
fe80::40f4:21bf:bd1a:34e8

I don't know what that 2nd one is (I have no real experience with networks),
but the code returned it, so it must be 'something'<g. Anyway, if the OP
wants only ###.###.###.### type IP addresses returned, the code is set up to
do that... he should just remove the apostrophes (comment symbols) from in
front of the two commented out lines in order to activate them... doing that
will make the function only return "normal" looking (at least to me) IP
addresses. To retrieve all the IP addresses on the system (either with or
without those commented out lines), code similar to this should be used...

Dim X As Long, IPaddresses() As String
IPaddresses = GetMyIPaddress
For X = 0 To UBound(IPaddresses)
Debug.Print IPaddresses(X)
Next

where the Debug.Print line would be replaced by the active code the OP wants
to use.

--
Rick (MVP - Excel)


"Steve Yandl" wrote in message
...
Sam,

Provided the PCs are XP or later, this might do what you want:

'------------------------------------

Sub GetIPaddy()

Dim strIPAddress As String
Dim strComputerName As String

strComputerName = "."

Set objWMIService = GetObject("winmgmts:\\" & strComputerName &
"\root\cimv2")

Set colItems = objWMIService.ExecQuery _
("Select * From Win32_NetworkAdapterConfiguration Where IPEnabled =
True")

For Each objItem In colItems
For Each objAddress In objItem.IPAddress
strIPAddress = objAddress
Next
Next

MsgBox strIPAddress

Set colItems = Nothing
Set objWMIService = Nothing

End Sub


'------------------------------------

Steve Yandl



"Sam Wilson" wrote in message
...
Not as straight forward as I'd guessed!

"Rob Bovey" wrote:

"Sam Wilson" wrote in message
...
How can I get my the IP address of a computer using VBA? I was hoping
it'd
be through environ() but it seems not...

Hi Sam,

I've posted an example of how to do this he

http://appspro.com/Tips/NetworkTips.htm#NT2

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Professional Excel Development - Second Edition *
http://www.appspro.com/Books/ProExcelDev.htm

Hi Rick,
IPCONFIG reports it as "Link-local IPv6 Address "

It's the MAC address of your PCs LAN card or CNR.

Now we all can hack into your pc.
Thanks

8<)

John




"Rick Rothstein" wrote in message ...
Hey! I just finished up some code using the same approach (and, from the
variable names, it looks like from the same source). I took a different end
route than you did, however, choosing to make my code a function instead.
Here is that code...

Function GetMyIPaddress() As String()
Dim X As Long, IPaddress() As String, Query As String
Dim Addresses() As String, objWMIService As Object
Dim ipConfigSet As Object, ipConfig As Object
Addresses = Split("")
Query = "Select IPAddress from Win32_NetworkAdapterConfiguration "
Set objWMIService = GetObject("winmgmts:\\.\root\cimv2")
Set ipConfigSet = objWMIService.ExecQuery(Query)
For Each ipConfig In ipConfigSet
If Not IsNull(ipConfig.IPaddress) Then
For X = LBound(ipConfig.IPaddress) To UBound(ipConfig.IPaddress)
'If ipConfig.IPaddress(X) Like "*#*.*#*.*#*.*#*" Then
ReDim Preserve Addresses(UBound(Addresses) + 1)
Addresses(UBound(Addresses)) = ipConfig.IPaddress(X)
'End If
Next
End If
Next
GetMyIPaddress = Addresses
End Function

The code returns a String array containing all the IP address on the system.
In my case, it found two...

192.168.1.3
fe80::40f4:21bf:bd1a:34e8

I don't know what that 2nd one is (I have no real experience with networks),
but the code returned it, so it must be 'something'<g. Anyway, if the OP
wants only ###.###.###.### type IP addresses returned, the code is set up to
do that... he should just remove the apostrophes (comment symbols) from in
front of the two commented out lines in order to activate them... doing that
will make the function only return "normal" looking (at least to me) IP
addresses. To retrieve all the IP addresses on the system (either with or
without those commented out lines), code similar to this should be used...

Dim X As Long, IPaddresses() As String
IPaddresses = GetMyIPaddress
For X = 0 To UBound(IPaddresses)
Debug.Print IPaddresses(X)
Next

where the Debug.Print line would be replaced by the active code the OP wants
to use.

--
Rick (MVP - Excel)


"Steve Yandl" wrote in message
...
Sam,

Provided the PCs are XP or later, this might do what you want:

'------------------------------------

Sub GetIPaddy()

Dim strIPAddress As String
Dim strComputerName As String

strComputerName = "."

Set objWMIService = GetObject("winmgmts:\\" & strComputerName &
"\root\cimv2")

Set colItems = objWMIService.ExecQuery _
("Select * From Win32_NetworkAdapterConfiguration Where IPEnabled =
True")

For Each objItem In colItems
For Each objAddress In objItem.IPAddress
strIPAddress = objAddress
Next
Next

MsgBox strIPAddress

Set colItems = Nothing
Set objWMIService = Nothing

End Sub


'------------------------------------

Steve Yandl



"Sam Wilson" wrote in message
...
Not as straight forward as I'd guessed!

"Rob Bovey" wrote:

"Sam Wilson" wrote in message
...
How can I get my the IP address of a computer using VBA? I was hoping
it'd
be through environ() but it seems not...

Hi Sam,

I've posted an example of how to do this he

http://appspro.com/Tips/NetworkTips.htm#NT2

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Professional Excel Development - Second Edition *
http://www.appspro.com/Books/ProExcelDev.htm

"Rick Rothstein" wrote:
The code returns a String array containing all the IP address on the
system. In my case, it found two...
192.168.1.3
fe80::40f4:21bf:bd1a:34e8

I don't know what that 2nd one is

The first adress is an IPv4 address.

The second address is an IPv6 address, a "link local" address. Every link
interface (that has IPv6 enabled) is assigned its own "link local" address
automagically.

If you do not need IPv6 (most networks are IPv4-only), you should find some
way to disable it. Besides generating excess network traffic, IPv6 can be a
security risk. Although IPv6 was intended to be a more-secure network
protocol, first, most implementations do not include the security features,
and, second, there are "holes" in the protocol.

The good news is: since most networks do not support IPv6 routing, the
security risk might be ameliorated by the fact that you are on a trustworthy
intranet behind a firewall that probably does not have IPv6 support enabled.

PS: Don't forget to ignore 127.000.000.001, the IPv4 loopback address. And
beware of multiple IPv4 address on computers with multiple active link
interfaces, e.g. wired and wireless.


----- original message -----

"Rick Rothstein" wrote in message
...
Hey! I just finished up some code using the same approach (and, from the
variable names, it looks like from the same source). I took a different
end route than you did, however, choosing to make my code a function
instead. Here is that code...

Function GetMyIPaddress() As String()
Dim X As Long, IPaddress() As String, Query As String
Dim Addresses() As String, objWMIService As Object
Dim ipConfigSet As Object, ipConfig As Object
Addresses = Split("")
Query = "Select IPAddress from Win32_NetworkAdapterConfiguration "
Set objWMIService = GetObject("winmgmts:\\.\root\cimv2")
Set ipConfigSet = objWMIService.ExecQuery(Query)
For Each ipConfig In ipConfigSet
If Not IsNull(ipConfig.IPaddress) Then
For X = LBound(ipConfig.IPaddress) To UBound(ipConfig.IPaddress)
'If ipConfig.IPaddress(X) Like "*#*.*#*.*#*.*#*" Then
ReDim Preserve Addresses(UBound(Addresses) + 1)
Addresses(UBound(Addresses)) = ipConfig.IPaddress(X)
'End If
Next
End If
Next
GetMyIPaddress = Addresses
End Function

The code returns a String array containing all the IP address on the
system. In my case, it found two...

192.168.1.3
fe80::40f4:21bf:bd1a:34e8

I don't know what that 2nd one is (I have no real experience with
networks), but the code returned it, so it must be 'something'<g. Anyway,
if the OP wants only ###.###.###.### type IP addresses returned, the code
is set up to do that... he should just remove the apostrophes (comment
symbols) from in front of the two commented out lines in order to activate
them... doing that will make the function only return "normal" looking (at
least to me) IP addresses. To retrieve all the IP addresses on the system
(either with or without those commented out lines), code similar to this
should be used...

Dim X As Long, IPaddresses() As String
IPaddresses = GetMyIPaddress
For X = 0 To UBound(IPaddresses)
Debug.Print IPaddresses(X)
Next

where the Debug.Print line would be replaced by the active code the OP
wants to use.

--
Rick (MVP - Excel)


"Steve Yandl" wrote in message
...
Sam,

Provided the PCs are XP or later, this might do what you want:

'------------------------------------

Sub GetIPaddy()

Dim strIPAddress As String
Dim strComputerName As String

strComputerName = "."

Set objWMIService = GetObject("winmgmts:\\" & strComputerName &
"\root\cimv2")

Set colItems = objWMIService.ExecQuery _
("Select * From Win32_NetworkAdapterConfiguration Where IPEnabled
= True")

For Each objItem In colItems
For Each objAddress In objItem.IPAddress
strIPAddress = objAddress
Next
Next

MsgBox strIPAddress

Set colItems = Nothing
Set objWMIService = Nothing

End Sub


'------------------------------------

Steve Yandl



"Sam Wilson" wrote in message
...
Not as straight forward as I'd guessed!

"Rob Bovey" wrote:

"Sam Wilson" wrote in message
...
How can I get my the IP address of a computer using VBA? I was hoping
it'd
be through environ() but it seems not...

Hi Sam,

I've posted an example of how to do this he

http://appspro.com/Tips/NetworkTips.htm#NT2

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Professional Excel Development - Second Edition *
http://www.appspro.com/Books/ProExcelDev.htm

Check out
http://www.source-code.biz/snippets/vbasic/8.htm

If this post helps click Yes
---------------
Jacob Skaria


"Sam Wilson" wrote:

Hi,

How can I get my the IP address of a computer using VBA? I was hoping it'd
be through environ() but it seems not...

Sam

Quick UDF to do the trick:
http://www.EXCELGAARD.dk/Lib/UDFs/IP/


Sam Wilson wrote:
Hi,

How can I get my the IP address of a computer using VBA? I was hoping
it'd be through environ() but it seems not...

Sam