Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
IP Address
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
IP Address
"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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
IP Address
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
IP Address
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
IP Address
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
IP Address
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
IP Address
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
IP Address
"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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
IP Address
"jaf" wrote:
IPCONFIG reports it as "Link-local IPv6 Address " It's the MAC address of your PCs LAN card or CNR. Not exactly. Yes, it is a "link local" IPv6 address. And yes, the lower 64 bits are the link "interface id". The "interface id" is derived from the MAC address. But it is not the MAC address exactly, in part depending on the standard followed by the link interface. In Rick's case, it does appear to be the 64-bit MAC address (almost). But for 48-bit MAC addresses, the "interface id" in the IPv6 address is the two halves (24 bits each) separated by 0xFFFE. (And there are the "link local" IPv6 addresses for point-to-point interfaces that might not have a MAC address.) But in case, the 64-bit "interface id" differs from the MAC address (or modified MAC address) by changing one bit from 0 to 1. My point is: do not try to use the IPv6 "interface id" in the "link local" address as MAC address directly. PS: Rick, I wonder why you are seeing IPv6 addresses. Is this a Vista thing? I do not see IPv6 on my interfaces, using Win XP SP3. But that might be because I am relying on automatic configuration (DHCP) from a local router. As I said, IPv6 is not widely used, at least not by default. ----- original message ----- "jaf" wrote in message ... 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
IP Address
Hi Joe,
As I said, IPCONFIG (in Vista) reported..... John "JoeU2004" wrote in message ... "jaf" wrote: IPCONFIG reports it as "Link-local IPv6 Address " It's the MAC address of your PCs LAN card or CNR. Not exactly. Yes, it is a "link local" IPv6 address. And yes, the lower 64 bits are the link "interface id". The "interface id" is derived from the MAC address. But it is not the MAC address exactly, in part depending on the standard followed by the link interface. In Rick's case, it does appear to be the 64-bit MAC address (almost). But for 48-bit MAC addresses, the "interface id" in the IPv6 address is the two halves (24 bits each) separated by 0xFFFE. (And there are the "link local" IPv6 addresses for point-to-point interfaces that might not have a MAC address.) But in case, the 64-bit "interface id" differs from the MAC address (or modified MAC address) by changing one bit from 0 to 1. My point is: do not try to use the IPv6 "interface id" in the "link local" address as MAC address directly. PS: Rick, I wonder why you are seeing IPv6 addresses. Is this a Vista thing? I do not see IPv6 on my interfaces, using Win XP SP3. But that might be because I am relying on automatic configuration (DHCP) from a local router. As I said, IPv6 is not widely used, at least not by default. ----- original message ----- "jaf" wrote in message ... 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
IP Address
On Aug 20, 7:16*pm, "jaf" wrote:
Hi Joe, As I said, IPCONFIG (in Vista) reported..... John "JoeU2004" wrote in l... "jaf" wrote: IPCONFIG reports it as *"Link-local IPv6 Address " It's the MAC address of your PCs LAN card or CNR. Not exactly. *Yes, it is a "link local" IPv6 address. *And yes, the lower 64 bits are the link "interface id". *The "interface id" is derived from the MAC address. *But it is not the MAC address exactly, in part depending on the standard followed by the link interface. In Rick's case, it does appear to be the 64-bit MAC address (almost). *But for 48-bit MAC addresses, the "interface id" in the IPv6 address is the two halves (24 bits each) separated by 0xFFFE. (And there are the "link local" IPv6 addresses for point-to-point interfaces that might not have a MAC address.) But in case, the 64-bit "interface id" differs from the MAC address (or modified MAC address) by changing one bit from 0 to 1. My point is: *do not try to use the IPv6 "interface id" in the "link local" address as MAC address directly. PS: *Rick, I wonder why you are seeing IPv6 addresses. *Is this a Vista thing? *I do not see IPv6 on my interfaces, using Win XP SP3. *But that might be because I am relying on automatic configuration (DHCP) from a local router. *As I said, IPv6 is not widely used, at least not by default. ----- original message ----- "jaf" wrote in message ... 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 news:X8ydnUZgZppHDBDXnZ2dnUVZ_i2dnZ2d@giganews. com... 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- Hide quoted text - - Show quoted text - A Network question in the Excel section. Wow!! I love networking. To answer the question for the 2nd or 3rd time up top. That is a Machine address. All devices connected to a network have a machine address. That is more important than an ip address depending on what you are trying to accomplish. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
IP Address
"jaf" wrote:
As I said, IPCONFIG (in Vista) reported..... Unfortunately(?) I don't have Vista to look at. But what you said was IPCONFIG reports "link-local IPv6 address". That much is correct. Then you added: "It's the MAC address of your PCs LAN card or CNR". Since that is without quotes, I assumed that is your interpretation, not something Vista reported. Am I wrong? In any case, it is __not__ the MAC address. It is an IP-layer address that is __derived__ from a MAC address in the manner that I explained. ----- original message ----- "jaf" wrote in message ... Hi Joe, As I said, IPCONFIG (in Vista) reported..... John "JoeU2004" wrote in message ... "jaf" wrote: IPCONFIG reports it as "Link-local IPv6 Address " It's the MAC address of your PCs LAN card or CNR. Not exactly. Yes, it is a "link local" IPv6 address. And yes, the lower 64 bits are the link "interface id". The "interface id" is derived from the MAC address. But it is not the MAC address exactly, in part depending on the standard followed by the link interface. In Rick's case, it does appear to be the 64-bit MAC address (almost). But for 48-bit MAC addresses, the "interface id" in the IPv6 address is the two halves (24 bits each) separated by 0xFFFE. (And there are the "link local" IPv6 addresses for point-to-point interfaces that might not have a MAC address.) But in case, the 64-bit "interface id" differs from the MAC address (or modified MAC address) by changing one bit from 0 to 1. My point is: do not try to use the IPv6 "interface id" in the "link local" address as MAC address directly. PS: Rick, I wonder why you are seeing IPv6 addresses. Is this a Vista thing? I do not see IPv6 on my interfaces, using Win XP SP3. But that might be because I am relying on automatic configuration (DHCP) from a local router. As I said, IPv6 is not widely used, at least not by default. ----- original message ----- "jaf" wrote in message ... 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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
IP Address
"Ty" wrote:
That is a Machine address. Yes and no, depending on how you are (mis)using the terminology. There is no such thing as a "machine address", at least not for networking purposes. There are link interface addresses, and there are upper protocol layer addresses -- in this case, the IP layer. Various people refer to the link-layer address and/or the IP address as a "machine address". Some authors apply the term to both addresses in the same text(!). The term is ambiguous and poorly used. In any case, fe80::40f4:21bf:bd1a:34e8 is an IP-layer address -- specifically, the IPv6 layer. It is not a link interface address (aka MAC address). All devices connected to a network have a machine address. That is more important than an ip address depending on what you are trying to accomplish. All interfaces connected to a network have one or more link-layer addresses -- but usually only one. fe80::40f4:21bf:bd1a:34e8 is __not__ one of them. In any case, a link-layer address is no more nor less important than an IP address. To avoid launching (deeper) into Interworking 101, suffice it to that you could not post your comments without both link-layer and IP (probably IPv4) addresses, among others. ----- original message ----- "Ty" wrote in message ... On Aug 20, 7:16 pm, "jaf" wrote: Hi Joe, As I said, IPCONFIG (in Vista) reported..... John "JoeU2004" wrote in l... "jaf" wrote: IPCONFIG reports it as "Link-local IPv6 Address " It's the MAC address of your PCs LAN card or CNR. Not exactly. Yes, it is a "link local" IPv6 address. And yes, the lower 64 bits are the link "interface id". The "interface id" is derived from the MAC address. But it is not the MAC address exactly, in part depending on the standard followed by the link interface. In Rick's case, it does appear to be the 64-bit MAC address (almost). But for 48-bit MAC addresses, the "interface id" in the IPv6 address is the two halves (24 bits each) separated by 0xFFFE. (And there are the "link local" IPv6 addresses for point-to-point interfaces that might not have a MAC address.) But in case, the 64-bit "interface id" differs from the MAC address (or modified MAC address) by changing one bit from 0 to 1. My point is: do not try to use the IPv6 "interface id" in the "link local" address as MAC address directly. PS: Rick, I wonder why you are seeing IPv6 addresses. Is this a Vista thing? I do not see IPv6 on my interfaces, using Win XP SP3. But that might be because I am relying on automatic configuration (DHCP) from a local router. As I said, IPv6 is not widely used, at least not by default. ----- original message ----- "jaf" wrote in message ... 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 news:X8ydnUZgZppHDBDXnZ2dnUVZ_i2dnZ2d@giganews. com... 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- Hide quoted text - - Show quoted text - A Network question in the Excel section. Wow!! I love networking. To answer the question for the 2nd or 3rd time up top. That is a Machine address. All devices connected to a network have a machine address. That is more important than an ip address depending on what you are trying to accomplish. |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
IP Address
Errata....
I wrote: In any case, a link-layer address is no more nor less important than an IP address. Okay, I'll concede that one might argue that the link-layer address is more important, because Ty qualified his statement with "depending on what you are trying to accomplish". Most people think of "networking" as synonymous with "TCP/IP networking", a term that is usually intended to encompass all of the "Internet Procotol Suite", once known as the (D)ARPA protocols. In that context, both addresses are equally important. But there are other network-layer protocols besides IP(v4 and v6). The point is: all network layer require the link-layer address, but not all network layers use IP addresses. In that sense, someone (not me) might say that the link-layer address may be considered more important. ----- original message ----- "JoeU2004" wrote in message ... "Ty" wrote: That is a Machine address. Yes and no, depending on how you are (mis)using the terminology. There is no such thing as a "machine address", at least not for networking purposes. There are link interface addresses, and there are upper protocol layer addresses -- in this case, the IP layer. Various people refer to the link-layer address and/or the IP address as a "machine address". Some authors apply the term to both addresses in the same text(!). The term is ambiguous and poorly used. In any case, fe80::40f4:21bf:bd1a:34e8 is an IP-layer address -- specifically, the IPv6 layer. It is not a link interface address (aka MAC address). All devices connected to a network have a machine address. That is more important than an ip address depending on what you are trying to accomplish. All interfaces connected to a network have one or more link-layer addresses -- but usually only one. fe80::40f4:21bf:bd1a:34e8 is __not__ one of them. In any case, a link-layer address is no more nor less important than an IP address. To avoid launching (deeper) into Interworking 101, suffice it to that you could not post your comments without both link-layer and IP (probably IPv4) addresses, among others. ----- original message ----- "Ty" wrote in message ... On Aug 20, 7:16 pm, "jaf" wrote: Hi Joe, As I said, IPCONFIG (in Vista) reported..... John "JoeU2004" wrote in l... "jaf" wrote: IPCONFIG reports it as "Link-local IPv6 Address " It's the MAC address of your PCs LAN card or CNR. Not exactly. Yes, it is a "link local" IPv6 address. And yes, the lower 64 bits are the link "interface id". The "interface id" is derived from the MAC address. But it is not the MAC address exactly, in part depending on the standard followed by the link interface. In Rick's case, it does appear to be the 64-bit MAC address (almost). But for 48-bit MAC addresses, the "interface id" in the IPv6 address is the two halves (24 bits each) separated by 0xFFFE. (And there are the "link local" IPv6 addresses for point-to-point interfaces that might not have a MAC address.) But in case, the 64-bit "interface id" differs from the MAC address (or modified MAC address) by changing one bit from 0 to 1. My point is: do not try to use the IPv6 "interface id" in the "link local" address as MAC address directly. PS: Rick, I wonder why you are seeing IPv6 addresses. Is this a Vista thing? I do not see IPv6 on my interfaces, using Win XP SP3. But that might be because I am relying on automatic configuration (DHCP) from a local router. As I said, IPv6 is not widely used, at least not by default. ----- original message ----- "jaf" wrote in message ... 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 news:X8ydnUZgZppHDBDXnZ2dnUVZ_i2dnZ2d@giganews. com... 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- Hide quoted text - - Show quoted text - A Network question in the Excel section. Wow!! I love networking. To answer the question for the 2nd or 3rd time up top. That is a Machine address. All devices connected to a network have a machine address. That is more important than an ip address depending on what you are trying to accomplish. |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
IP Address
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I avoid excel change absolute address to relative address | Excel Discussion (Misc queries) | |||
long address list, name-address-city, listed vertically, how do y. | Excel Discussion (Misc queries) | |||
How do I import Office address book to Outlook Express address bo. | Excel Discussion (Misc queries) | |||
How to insert an address from Outlook 2003 address book ? | Excel Programming | |||
LINKING Address cells from an EXCEL spreadsheet to fill MapQuest Address Info | Excel Programming |