averiguar ip por excel

"Doors" wrote in message
news:

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

Saludos!!

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!!!

"Héctor Miguel" wrote:

hola, chicos !

>> 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] :-(

A ver si esto te sirve... lo checkee en Excel 2003 y anda!

==
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.

hola, Doors !

... 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.

MUCHAS GRACIAS A TODOS POR SU AYUDA !!!!!!! YA PUDE!! : )

"jsilva69" wrote:

A ver si esto te sirve... lo checkee en Excel 2003 y anda!

==>
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.