averiguar ip por excel
Doors
Amigos, una consulta, es posible averiguar la direccion ip de una pc por
medio de excel???
Gracias!!!
KL
news:4D4DF136-2665-4E4F...@microsoft.com...
Mira a ver si te vale esto:
Saludos,
KL
Doors
no hay direccion ip, no se porque......
Gracias por tu ayuda!!!!
Sub test()
Dim IP_Address: IP_Address = GetIP()
If IP_Address = "0.0.0.0" Or IP_Address = "" Then
MsgBox "No IP Address found.", , ""
Else
ActiveWorkbook.Sheets("Hoja1").Range("d26").Value = "IP address"
End If
End Sub
Function GetIP()
Dim ws: Set ws = CreateObject("WScript.Shell")
Dim fso: Set fso = CreateObject("Scripting.FileSystemObject")
Dim TmpFile: TmpFile = fso.GetSpecialFolder(2) & "/ip.txt"
Dim ThisLine, IP
If ws.Environment("SYSTEM")("OS") = "" Then
ws.Run "winipcfg /batch " & TmpFile, 0, True
Else
ws.Run "%comspec% /c ipconfig > " & TmpFile, 0, True
End If
With fso.GetFile(TmpFile).OpenAsTextStream
Do While Not .AtEndOfStream
ThisLine = .readline
If InStr(ThisLine, "Address") <> 0 Then
IP = Mid(ThisLine, InStr(ThisLine, ":") + 2)
End If
Loop
.Close
End With
'WinXP (NT? 2K?) leaves a carriage return at the end of line
If IP <> "" Then
If Asc(Right(IP, 1)) = 13 Then IP = Left(IP, Len(IP) - 1)
End If
GetIP = IP
fso.GetFile(TmpFile).Delete
Set fso = Nothing
Set ws = Nothing
End Function
KL
"Doors" <Do...@discussions.microsoft.com> wrote in message
news:B1B8AFE3-7986-49B1...@microsoft.com...
> Hola KL, estuve probando esta funcion pero me envia un mensaje que indica
> que
> no hay direccion ip, no se porque......
Pues a mi ese tampoco me ha funcionado, pero el segundo (con API's) - si.
Saludos,
KL
Doors
Apis, me da un error en la declaracion de las funciones.
Héctor Miguel
>> Doors escribio en el mensje ...
>> Hola KL, estuve probando esta funcion pero
>> me envia un mensaje que indica que no hay direccion ip, no se porque...
> Pues a mi ese tampoco me ha funcionado, pero el segundo (con API's) - si.
> Saludos,
> KL
otra alternativa [y solo por no dejarla en el tintero] :D
pueba con la siguiente macro,,,
-> estableciendo PRIMERO una referencia en el proyecto de macros de tu libro...
-> en el editor de vba / -menu- herramientas / referencias..
-> a la biblioteca de objetos de: -> Microsoft Internet Controls
Sub Identifica_IP()
Dim IP As String
With CreateObject("InternetExplorer.Application")
.Navigate URL:="www.showmyip.com"
Do While .Busy Or .ReadyState <> 4
DoEvents
Loop
IP = .Document.Body.InnerText
.Quit
End With
ActiveCell = Left(IP, InStr(IP, vbCrLf) - 1)
End Sub
si cualquier duda [o informacion adicional]... comentas ?
saludos,
hector.
p.d. es probable que pudieras lograr mayor 'rapidez' haciendo algunas llamadas a las API's de windows
o [quizas] 'leyendo' datos y componentes en el registro de windows...
[aunque quizas tengan otras 'implicaciones', sobre todo si vas a estar cambiando de equipos] :-(
KL
> Kl, perodna que te moleste tanto, pero aun asi no me funciona, el de las
> Apis, me da un error en la declaracion de las funciones.
En la parte superior del codigo debes vigilar las lineas rotas
(concretamente la declaracion de la ultima funcion - GetComputerName).
Prueba el codigo que te pongo a continuacion.
Saludos,
KL
Private Const IP_SUCCESS As Long = 0
Private Const WS_VERSION_REQD As Long = &H101
Private Const MIN_SOCKETS_REQD As Long = 1
Private Const SOCKET_ERROR As Long = -1
Private Const INADDR_NONE As Long = &HFFFFFFFF
Private Const MAX_WSADescription As Long = 256
Private Const MAX_WSASYSStatus As Long = 128
Private Type WSADATA
wVersion As Integer
wHighVersion As Integer
szDescription(0 To MAX_WSADescription) As Byte
szSystemStatus(0 To MAX_WSASYSStatus) As Byte
wMaxSockets As Long
wMaxUDPDG As Long
dwVendorInfo As Long
End Type
Private Declare Function gethostbyname Lib "WSOCK32.DLL" _
(ByVal hostname As String) As Long
Private Declare Sub CopyMemory Lib "kernel32" _
Alias "RtlMoveMemory" (xDest As Any, xSource As Any, _
ByVal nbytes As Long)
Private Declare Function WSAStartup Lib "WSOCK32.DLL" _
(ByVal wVersionRequired As Long, _
lpWSADATA As WSADATA) As Long
Private Declare Function WSACleanup Lib "WSOCK32.DLL" () As Long
Private Declare Function inet_addr Lib "WSOCK32.DLL" _
(ByVal s As String) As Long
Private Declare Function GetComputerName Lib "kernel32" _
Alias "GetComputerNameA" (ByVal Buffer As String, _
Size As Long) As Long
Sub TestingFunction()
If SocketsInitialize() Then
MsgBox GetIPFromHostName(GetPcName), , "IP address of " & GetPcName
End If
SocketsCleanup
End Sub
Private Function GetIPFromHostName(ByVal sHostName As String) As String
'converts a host name to an IP address.
Dim nbytes As Long
Dim ptrHosent As Long 'address of hostent structure
Dim ptrName As Long 'address of name pointer
Dim ptrAddress As Long 'address of address pointer
Dim ptrIPAddress As Long
Dim sAddress As String
sAddress = Space$(4)
ptrHosent = gethostbyname(sHostName & vbNullChar)
If ptrHosent <> 0 Then
ptrName = ptrHosent
ptrAddress = ptrHosent + 12
'get the IP address
CopyMemory ptrName, ByVal ptrName, 4
CopyMemory ptrAddress, ByVal ptrAddress, 4
CopyMemory ptrIPAddress, ByVal ptrAddress, 4
CopyMemory ByVal sAddress, ByVal ptrIPAddress, 4
GetIPFromHostName = IPToText(sAddress)
End If
End Function
Private Function IPToText(ByVal IPAddress As String) As String
IPToText = CStr(Asc(IPAddress)) & "." & _
CStr(Asc(Mid$(IPAddress, 2, 1))) & "." & _
CStr(Asc(Mid$(IPAddress, 3, 1))) & "." & _
CStr(Asc(Mid$(IPAddress, 4, 1)))
End Function
Private Sub SocketsCleanup()
If WSACleanup() <> 0 Then
MsgBox "Windows Sockets error occurred in Cleanup.", vbExclamation
End If
End Sub
Private Function SocketsInitialize() As Boolean
Dim WSAD As WSADATA
SocketsInitialize = WSAStartup(WS_VERSION_REQD, WSAD) = IP_SUCCESS
End Function
Private Function GetPcName() As String
Dim strBuf As String * 16, strPcName As String, lngPc As Long
lngPc = GetComputerName(strBuf, Len(strBuf))
If lngPc <> 0 Then
strPcName = Left(strBuf, InStr(strBuf, vbNullChar) - 1)
GetPcName = strPcName
Else
GetPcName = vbNullString
End If
End Function
Doors
Gracias a ambos por su colaboración,pero aun no me ha funcionado, en la de
KL siempre me sigue dando ese problema y en la de hector me da una ip que no
es la que tengo en mim maquina : (, en la referencia que mencionas de
Microsoft Internet Controls, no esta , yo tengo la version 2003, habra algun
inconveniente??
Gracias a ambos por sus respuestas y la ayuda que siempre me han brindado!!!
jsilva69
=======================================
Public Const MAX_WSADescription As Long = 256
Public Const MAX_WSASYSStatus As Long = 128
Public Const ERROR_SUCCESS As Long = 0
Public Const WS_VERSION_REQD As Long = &H101
Public Const WS_VERSION_MAJOR As Long = WS_VERSION_REQD \ &H100 And
&HFF&
Public Const WS_VERSION_MINOR As Long = WS_VERSION_REQD And &HFF&
Public Const MIN_SOCKETS_REQD As Long = 1
Public Const SOCKET_ERROR As Long = -1
Public Type HOSTENT
hName As Long
hAliases As Long
hAddrType As Integer
hLen As Integer
hAddrList As Long
End Type
Public Type WSADATA
wVersion As Integer
wHighVersion As Integer
szDescription(0 To MAX_WSADescription) As Byte
szSystemStatus(0 To MAX_WSASYSStatus) As Byte
wMaxSockets As Integer
wMaxUDPDG As Integer
dwVendorInfo As Long
End Type
Public Declare Function WSAGetLastError Lib "wsock32" () As Long
Public Declare Function WSAStartup Lib "wsock32" (ByVal
wVersionRequired As Long, lpWSADATA As WSADATA) As Long
Public Declare Function WSACleanup Lib "wsock32" () As Long
Public Declare Function gethostname Lib "wsock32" (ByVal szHost As
String, ByVal dwHostLen As Long) As Long
Public Declare Function gethostbyname Lib "wsock32" _
(ByVal szHost As String) As Long
Public Declare Sub CopyMemory Lib "kernel32" _
Alias "RtlMoveMemory" (hpvDest As Any, _
ByVal hpvSource As Long, _
ByVal cbCopy As Long)
Public Function GetIPAddress() As String
Dim sHostName As String * 256
Dim lpHost As Long
Dim HOST As HOSTENT
Dim dwIPAddr As Long
Dim tmpIPAddr() As Byte
Dim i As Integer
Dim sIPAddr As String
If Not SocketsInitialize() Then
GetIPAddress = ""
Exit Function
End If
'gethostname returns the name of the local host into
'the buffer specified by the name parameter. The host
'name is returned as a null-terminated string. The
'form of the host name is dependent on the Windows
'Sockets provider - it can be a simple host name, or
'it can be a fully qualified domain name. However, it
'is guaranteed that the name returned will be successfully
'parsed by gethostbyname and WSAAsyncGetHostByName.
'In actual application, if no local host name has been
'configured, gethostname must succeed and return a token
'host name that gethostbyname or WSAAsyncGetHostByName
'can resolve.
If gethostname(sHostName, 256) = SOCKET_ERROR Then
GetIPAddress = ""
MsgBox "Windows Sockets error " & Str$(WSAGetLastError()) & _
" has occurred. Unable to successfully get Host Name."
SocketsCleanup
Exit Function
End If
'gethostbyname returns a pointer to a HOSTENT structure
'- a structure allocated by Windows Sockets. The HOSTENT
'structure contains the results of a successful search
'for the host specified in the name parameter.
'The application must never attempt to modify this
'structure or to free any of its components. Furthermore,
'only one copy of this structure is allocated per thread,
'so the application should copy any information it needs
'before issuing any other Windows Sockets function calls.
'gethostbyname function cannot resolve IP address strings
'passed to it. Such a request is treated exactly as if an
'unknown host name were passed. Use inet_addr to convert
'an IP address string the string to an actual IP address,
'then use another function, gethostbyaddr, to obtain the
'contents of the HOSTENT structure.
sHostName = Trim$(sHostName)
lpHost = gethostbyname(sHostName)
If lpHost = 0 Then
GetIPAddress = ""
MsgBox "Windows Sockets are not responding. " & _
"Unable to successfully get Host Name."
SocketsCleanup
Exit Function
End If
'to extract the returned IP address, we have to copy
'the HOST structure and its members
CopyMemory HOST, lpHost, Len(HOST)
CopyMemory dwIPAddr, HOST.hAddrList, 4
'create an array to hold the result
ReDim tmpIPAddr(1 To HOST.hLen)
CopyMemory tmpIPAddr(1), dwIPAddr, HOST.hLen
'and with the array, build the actual address,
'appending a period between members
For i = 1 To HOST.hLen
sIPAddr = sIPAddr & tmpIPAddr(i) & "."
Next
'the routine adds a period to the end of the
'string, so remove it here
GetIPAddress = Mid$(sIPAddr, 1, Len(sIPAddr) - 1)
SocketsCleanup
End Function
Public Function GetIPHostName() As String
Dim sHostName As String * 256
If Not SocketsInitialize() Then
GetIPHostName = ""
Exit Function
End If
If gethostname(sHostName, 256) = SOCKET_ERROR Then
GetIPHostName = ""
MsgBox "Windows Sockets error " & Str$(WSAGetLastError()) & _
" has occurred. Unable to successfully get Host
Name."
SocketsCleanup
Exit Function
End If
GetIPHostName = Left$(sHostName, InStr(sHostName, Chr(0)) - 1)
SocketsCleanup
End Function
Public Function HiByte(ByVal wParam As Integer) As Byte
'note: VB4-32 users should declare this function As Integer
HiByte = (wParam And &HFF00&) \ (&H100)
End Function
Public Function LoByte(ByVal wParam As Integer) As Byte
'note: VB4-32 users should declare this function As Integer
LoByte = wParam And &HFF&
End Function
Public Sub SocketsCleanup()
If WSACleanup() <> ERROR_SUCCESS Then
MsgBox "Socket error occurred in Cleanup."
End If
End Sub
Public Function SocketsInitialize() As Boolean
Dim WSAD As WSADATA
Dim sLoByte As String
Dim sHiByte As String
If WSAStartup(WS_VERSION_REQD, WSAD) <> ERROR_SUCCESS Then
MsgBox "The 32-bit Windows Socket is not responding."
SocketsInitialize = False
Exit Function
End If
If WSAD.wMaxSockets < MIN_SOCKETS_REQD Then
MsgBox "This application requires a minimum of " & _
CStr(MIN_SOCKETS_REQD) & " supported sockets."
SocketsInitialize = False
Exit Function
End If
If LoByte(WSAD.wVersion) < WS_VERSION_MAJOR Or _
(LoByte(WSAD.wVersion) = WS_VERSION_MAJOR And _
HiByte(WSAD.wVersion) < WS_VERSION_MINOR) Then
sHiByte = CStr(HiByte(WSAD.wVersion))
sLoByte = CStr(LoByte(WSAD.wVersion))
MsgBox "Sockets version " & sLoByte & "." & sHiByte & _
" is not supported by 32-bit Windows Sockets."
SocketsInitialize = False
Exit Function
End If
'must be OK, so lets do it
SocketsInitialize = True
End Function
===========================================
Espero que te sirva
Julio.
Héctor Miguel
> ... aun no me ha funcionado, en la de KL siempre me sigue dando ese problema
> y en la de hector me da una ip que no es la que tengo en mim maquina : (
> en la referencia que mencionas de Microsoft Internet Controls, no esta
> yo tengo la version 2003, habra algun inconveniente? ...
en excel 2003 puedes cambiar la direccion donde consultas que IP *reporta* tu CONEXION ACTUAL
este ejemplo me ha funcionado para 2003 [y no requiere de la libreria de objetos "Internet Controls"]:
Sub Identifica_IP()
With CreateObject("InternetExplorer.Application")
.Navigate URL:="http://www.whatismyip.org/"
Do While .Busy Or .ReadyState <> 4
DoEvents
Loop
ActiveCell = .Document.Body.InnerText
.Quit
End With
End Sub
[hasta donde se]... la direccion IP que reportan las API's y/o consultas en el registro de windows...
NO ES *necesariamente* con la que *sales* hacia la web
la que reportan las paginas *consultadas*... SI -?-
si cualquier duda... comentas ?
saludos,
hector.
Doors
Héctor Miguel
[solo] no pierdan de vista que las direcciones IP *reportadas* por los varios metodos podrian ser distintas entre si...
[dependiendo de que sea lo que *realmente* se necesitara *validar/comprobar/...* mediante alguna direccion IP] -?-
esta es la direccion que reporta OE para jsilva: -> 216.244.232.1
[me gustaria conocer que le reportan las API's ?
esta es la direccion que reporta OE para mis mensajes *en esta sesion*: -> 189.164.119.101
IP dinamica por conexion ADSL [y es la misma que reporta la pagina: "http://www.whatismyip.org/"]
sin embargo, la que reportan las API's del codigo de julio es: -> 169.254.151.59
[probado en xl-2003]
saludos,
hector.