adress IP

Le
maud s
Bonjour,

je voudrais savoir si il existe un moyen de mettre en cellule A1 d une
feuille mon adresse IP public bien evidemment recuperer automatiquement je
ne parle pas de saisie manuelle.

j aimerais savoir le code VBA et si il existe une formule excel.

la formule m arrangerais aussi.


merci d avance

maud
Vidéos High-Tech et Jeu Vidéo
Téléchargements
Vos réponses Page 1 / 2
Gagnez chaque mois un abonnement Premium avec GNT : Inscrivez-vous !
Trier par : date / pertinence
Daniel.j
Le #6462811
Bonjour
Essaie cette macro....
A copier dans un module standard
Execute la macro "Test"

--
Daniel
FAQ MPFE
http://dj.joss.free.fr/faq.htm

VBAXL
http://dj.joss.free.fr/

=========== Auteur (???)

Option Explicit

Public Const MAX_WSADescription = 256
Public Const MAX_WSASYSStatus = 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.DLL" () As Long

Public Declare Function WSAStartup Lib "WSOCK32.DLL" _
(ByVal wVersionRequired As Long, lpWSADATA As WSADATA) As Long

Public Declare Function WSACleanup Lib "WSOCK32.DLL" () As Long

Public Declare Function gethostname Lib "WSOCK32.DLL" _
(ByVal szHost As String, ByVal dwHostLen As Long) As Long

Public Declare Function gethostbyname Lib "WSOCK32.DLL" _
(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

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

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

CopyMemory HOST, lpHost, Len(HOST)
CopyMemory dwIPAddr, HOST.hAddrList, 4

ReDim tmpIPAddr(1 To HOST.hLen)

CopyMemory tmpIPAddr(1), dwIPAddr, HOST.hLen

For i = 1 To HOST.hLen
sIPAddr = sIPAddr & tmpIPAddr(i) & "."
Next

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)
HiByte = wParam &H100 And &HFF&
End Function

Public Function LoByte(ByVal wParam 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

'*********************
Sub test()
MsgBox GetIPAddress
MsgBox GetIPHostName
[A1] = GetIPAddress
End Sub
'**********************

"maud s" %
Bonjour,

je voudrais savoir si il existe un moyen de mettre en cellule A1 d une
feuille mon adresse IP public bien evidemment recuperer automatiquement je
ne parle pas de saisie manuelle.

j aimerais savoir le code VBA et si il existe une formule excel.

la formule m arrangerais aussi.


merci d avance

maud




Michel Angelosanto
Le #6462771
Etant chez free, cela me donne l'adresse IP de mon réseau (192.168.0.10) et
non pas mon adresse IP sur Internet.

"Daniel.j" news:
Bonjour
Essaie cette macro....
A copier dans un module standard
Execute la macro "Test"

--
Daniel
FAQ MPFE
http://dj.joss.free.fr/faq.htm

VBAXL
http://dj.joss.free.fr/

=========== > Auteur (???)

Option Explicit

Public Const MAX_WSADescription = 256
Public Const MAX_WSASYSStatus = 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.DLL" () As Long

Public Declare Function WSAStartup Lib "WSOCK32.DLL" _
(ByVal wVersionRequired As Long, lpWSADATA As WSADATA) As Long

Public Declare Function WSACleanup Lib "WSOCK32.DLL" () As Long

Public Declare Function gethostname Lib "WSOCK32.DLL" _
(ByVal szHost As String, ByVal dwHostLen As Long) As Long

Public Declare Function gethostbyname Lib "WSOCK32.DLL" _
(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

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

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

CopyMemory HOST, lpHost, Len(HOST)
CopyMemory dwIPAddr, HOST.hAddrList, 4

ReDim tmpIPAddr(1 To HOST.hLen)

CopyMemory tmpIPAddr(1), dwIPAddr, HOST.hLen

For i = 1 To HOST.hLen
sIPAddr = sIPAddr & tmpIPAddr(i) & "."
Next

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)
HiByte = wParam &H100 And &HFF&
End Function

Public Function LoByte(ByVal wParam 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

'*********************
Sub test()
MsgBox GetIPAddress
MsgBox GetIPHostName
[A1] = GetIPAddress
End Sub
'**********************

"maud s" %
Bonjour,

je voudrais savoir si il existe un moyen de mettre en cellule A1 d une
feuille mon adresse IP public bien evidemment recuperer automatiquement
je ne parle pas de saisie manuelle.

j aimerais savoir le code VBA et si il existe une formule excel.

la formule m arrangerais aussi.


merci d avance

maud







--
Michel Angelosanto, Bordeaux
http://angelosa.free.fr/


Daniel.j
Le #6465861
et celle la ?
Sub ip()
Set fs = CreateObject("Scripting.FileSystemObject")
Set sh = CreateObject("WScript.Shell")
On Error Resume Next
sh.Run "%comspec% /c ipconfig > C:ip1.txt", 0, True
sh.Run wscript.Path & "ipconfig /All /batch C:ip1.txt", 0, True
sh.Run "c:windowssystem32ipconfig /All /batch C:ip1.txt", 0, True
sh.Run "ipconfig /All /batch C:ip1.txt", 0, True
On Error GoTo 0

Do While Not fs.fileexists("C:ip1.txt")
Loop

'lire le fichier texte créé par le batch
Set fich = fs.OpenTextFile("c:ip1.txt", 1, False)
Do While Not fich.AtEndOfStream
txt = fich.readLine
If InStr(LCase(txt), "adresse ip") > 1 Or InStr(LCase(txt), "ip address") >
1 Then
txt = Right(txt, Len(txt) - InStr(txt, ":"))
MsgBox txt
End If
Loop
fich.Close

'ménage
fs.deletefile "c:ip1.txt"
Set sh = Nothing
Set fs = Nothing
End

--
Daniel
FAQ MPFE
http://dj.joss.free.fr/faq.htm

VBAXL
http://dj.joss.free.fr/


"Michel Angelosanto" uiRl%
Etant chez free, cela me donne l'adresse IP de mon réseau (192.168.0.10)
et non pas mon adresse IP sur Internet.

"Daniel.j" news:
Bonjour
Essaie cette macro....
A copier dans un module standard
Execute la macro "Test"

--
Daniel
FAQ MPFE
http://dj.joss.free.fr/faq.htm

VBAXL
http://dj.joss.free.fr/

=========== >> Auteur (???)

Option Explicit

Public Const MAX_WSADescription = 256
Public Const MAX_WSASYSStatus = 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.DLL" () As Long

Public Declare Function WSAStartup Lib "WSOCK32.DLL" _
(ByVal wVersionRequired As Long, lpWSADATA As WSADATA) As Long

Public Declare Function WSACleanup Lib "WSOCK32.DLL" () As Long

Public Declare Function gethostname Lib "WSOCK32.DLL" _
(ByVal szHost As String, ByVal dwHostLen As Long) As Long

Public Declare Function gethostbyname Lib "WSOCK32.DLL" _
(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

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

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

CopyMemory HOST, lpHost, Len(HOST)
CopyMemory dwIPAddr, HOST.hAddrList, 4

ReDim tmpIPAddr(1 To HOST.hLen)

CopyMemory tmpIPAddr(1), dwIPAddr, HOST.hLen

For i = 1 To HOST.hLen
sIPAddr = sIPAddr & tmpIPAddr(i) & "."
Next

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)
HiByte = wParam &H100 And &HFF&
End Function

Public Function LoByte(ByVal wParam 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

'*********************
Sub test()
MsgBox GetIPAddress
MsgBox GetIPHostName
[A1] = GetIPAddress
End Sub
'**********************

"maud s" %
Bonjour,

je voudrais savoir si il existe un moyen de mettre en cellule A1 d une
feuille mon adresse IP public bien evidemment recuperer automatiquement
je ne parle pas de saisie manuelle.

j aimerais savoir le code VBA et si il existe une formule excel.

la formule m arrangerais aussi.


merci d avance

maud







--
Michel Angelosanto, Bordeaux
http://angelosa.free.fr/




Michel Angelosanto
Le #6469741
Mon adresse IP n'apparait pas avec la commande ipconfig /all

Carte Ethernet Connexion au réseau local :

Suffixe DNS propre à la connexion. . . :
Adresse IPv6 de liaison locale. . : fe80::d459:1c3c:7ca2:9612%8
Adresse IPv4. . . . . . . . . . . : 192.168.0.10
Masque de sous-réseau. . . . . . . . . : 255.255.255.0
Passerelle par défaut. . . . . . . . . : 192.168.0.254
...

"Daniel.j" news:
et celle la ?
Sub ip()
Set fs = CreateObject("Scripting.FileSystemObject")
Set sh = CreateObject("WScript.Shell")
On Error Resume Next
sh.Run "%comspec% /c ipconfig > C:ip1.txt", 0, True
sh.Run wscript.Path & "ipconfig /All /batch C:ip1.txt", 0, True
sh.Run "c:windowssystem32ipconfig /All /batch C:ip1.txt", 0, True
sh.Run "ipconfig /All /batch C:ip1.txt", 0, True
On Error GoTo 0

Do While Not fs.fileexists("C:ip1.txt")
Loop

'lire le fichier texte créé par le batch
Set fich = fs.OpenTextFile("c:ip1.txt", 1, False)
Do While Not fich.AtEndOfStream
txt = fich.readLine
If InStr(LCase(txt), "adresse ip") > 1 Or InStr(LCase(txt), "ip address")
1 Then
txt = Right(txt, Len(txt) - InStr(txt, ":"))

MsgBox txt
End If
Loop
fich.Close

'ménage
fs.deletefile "c:ip1.txt"
Set sh = Nothing
Set fs = Nothing
End

--
Daniel
FAQ MPFE
http://dj.joss.free.fr/faq.htm

VBAXL
http://dj.joss.free.fr/


"Michel Angelosanto" uiRl%
Etant chez free, cela me donne l'adresse IP de mon réseau (192.168.0.10)
et non pas mon adresse IP sur Internet.

"Daniel.j" news:
Bonjour
Essaie cette macro....
A copier dans un module standard
Execute la macro "Test"

--
Daniel
FAQ MPFE
http://dj.joss.free.fr/faq.htm

VBAXL
http://dj.joss.free.fr/

=========== >>> Auteur (???)

Option Explicit

Public Const MAX_WSADescription = 256
Public Const MAX_WSASYSStatus = 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.DLL" () As Long

Public Declare Function WSAStartup Lib "WSOCK32.DLL" _
(ByVal wVersionRequired As Long, lpWSADATA As WSADATA) As Long

Public Declare Function WSACleanup Lib "WSOCK32.DLL" () As Long

Public Declare Function gethostname Lib "WSOCK32.DLL" _
(ByVal szHost As String, ByVal dwHostLen As Long) As Long

Public Declare Function gethostbyname Lib "WSOCK32.DLL" _
(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

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

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

CopyMemory HOST, lpHost, Len(HOST)
CopyMemory dwIPAddr, HOST.hAddrList, 4

ReDim tmpIPAddr(1 To HOST.hLen)

CopyMemory tmpIPAddr(1), dwIPAddr, HOST.hLen

For i = 1 To HOST.hLen
sIPAddr = sIPAddr & tmpIPAddr(i) & "."
Next

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)
HiByte = wParam &H100 And &HFF&
End Function

Public Function LoByte(ByVal wParam 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

'*********************
Sub test()
MsgBox GetIPAddress
MsgBox GetIPHostName
[A1] = GetIPAddress
End Sub
'**********************

"maud s" %
Bonjour,

je voudrais savoir si il existe un moyen de mettre en cellule A1 d une
feuille mon adresse IP public bien evidemment recuperer automatiquement
je ne parle pas de saisie manuelle.

j aimerais savoir le code VBA et si il existe une formule excel.

la formule m arrangerais aussi.


merci d avance

maud







--
Michel Angelosanto, Bordeaux
http://angelosa.free.fr/





--
Michel Angelosanto, Bordeaux
http://angelosa.free.fr/




Misange
Le #6469731
bonjour
pour connaître ton IP:
http://www.mon-ip.com/
Sinon je crois que ce que j'ai en magasin
http://www.excelabo.net/excel/sortirweb.php#adresseIP
de KPD-team doit correspondre à ce que Daniel t'a indiqué.


Misange migrateuse
XlWiki : Participez à un travail collaboratif sur excel !
http://xlwiki.free.fr/wiki
http://www.excelabo.net

Mon adresse IP n'apparait pas avec la commande ipconfig /all

Carte Ethernet Connexion au réseau local :

Suffixe DNS propre à la connexion. . . :
Adresse IPv6 de liaison locale. . : fe80::d459:1c3c:7ca2:9612%8
Adresse IPv4. . . . . . . . . . . : 192.168.0.10
Masque de sous-réseau. . . . . . . . . : 255.255.255.0
Passerelle par défaut. . . . . . . . . : 192.168.0.254
...

"Daniel.j" news:
et celle la ?
Sub ip()
Set fs = CreateObject("Scripting.FileSystemObject")
Set sh = CreateObject("WScript.Shell")
On Error Resume Next
sh.Run "%comspec% /c ipconfig > C:ip1.txt", 0, True
sh.Run wscript.Path & "ipconfig /All /batch C:ip1.txt", 0, True
sh.Run "c:windowssystem32ipconfig /All /batch C:ip1.txt", 0, True
sh.Run "ipconfig /All /batch C:ip1.txt", 0, True
On Error GoTo 0

Do While Not fs.fileexists("C:ip1.txt")
Loop

'lire le fichier texte créé par le batch
Set fich = fs.OpenTextFile("c:ip1.txt", 1, False)
Do While Not fich.AtEndOfStream
txt = fich.readLine
If InStr(LCase(txt), "adresse ip") > 1 Or InStr(LCase(txt), "ip
address") > 1 Then
txt = Right(txt, Len(txt) - InStr(txt, ":"))
MsgBox txt
End If
Loop
fich.Close

'ménage
fs.deletefile "c:ip1.txt"
Set sh = Nothing
Set fs = Nothing
End

--
Daniel
FAQ MPFE
http://dj.joss.free.fr/faq.htm

VBAXL
http://dj.joss.free.fr/


"Michel Angelosanto" news: uiRl%
Etant chez free, cela me donne l'adresse IP de mon réseau
(192.168.0.10) et non pas mon adresse IP sur Internet.

"Daniel.j" news:
Bonjour
Essaie cette macro....
A copier dans un module standard
Execute la macro "Test"

--
Daniel
FAQ MPFE
http://dj.joss.free.fr/faq.htm

VBAXL
http://dj.joss.free.fr/

=========== >>>> Auteur (???)

Option Explicit

Public Const MAX_WSADescription = 256
Public Const MAX_WSASYSStatus = 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.DLL" () As Long

Public Declare Function WSAStartup Lib "WSOCK32.DLL" _
(ByVal wVersionRequired As Long, lpWSADATA As WSADATA) As Long

Public Declare Function WSACleanup Lib "WSOCK32.DLL" () As Long

Public Declare Function gethostname Lib "WSOCK32.DLL" _
(ByVal szHost As String, ByVal dwHostLen As Long) As Long

Public Declare Function gethostbyname Lib "WSOCK32.DLL" _
(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

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

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

CopyMemory HOST, lpHost, Len(HOST)
CopyMemory dwIPAddr, HOST.hAddrList, 4

ReDim tmpIPAddr(1 To HOST.hLen)

CopyMemory tmpIPAddr(1), dwIPAddr, HOST.hLen

For i = 1 To HOST.hLen
sIPAddr = sIPAddr & tmpIPAddr(i) & "."
Next

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)
HiByte = wParam &H100 And &HFF&
End Function

Public Function LoByte(ByVal wParam 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

'*********************
Sub test()
MsgBox GetIPAddress
MsgBox GetIPHostName
[A1] = GetIPAddress
End Sub
'**********************

"maud s" %
Bonjour,

je voudrais savoir si il existe un moyen de mettre en cellule A1 d
une feuille mon adresse IP public bien evidemment recuperer
automatiquement je ne parle pas de saisie manuelle.

j aimerais savoir le code VBA et si il existe une formule excel.

la formule m arrangerais aussi.


merci d avance

maud







--
Michel Angelosanto, Bordeaux
http://angelosa.free.fr/











Michel (pen ar bed)
Le #6473201
Michel Angelosanto avait prétendu :
Etant chez free, cela me donne l'adresse IP de mon réseau (192.168.0.10) et
non pas mon adresse IP sur Internet.


Bonjour,

Si il y a un dns dynamique comme No-IP ou DynDns j'ai "bricolé" un bout
de code qui récupère en A1 l'IP du host dynamique
Peut être pas très élégant comme code mais chez moi ça fonctionne
;-)
M.
-------------------------------------------------------

Sub essai()
ChDir "C:WINDOWSSystem32"
Open "C:WINDOWSSystem32ip.bat" For Output As #1
Print #1, "ping nom_de_domaine > c:ip.txt"
Close #1
Open "C:ip.txt" For Input As #1
cpt = 1
While cpt < 4
Line Input #1, l1
cpt = cpt + 1
Wend
Close #1
m = Split(l1, "[")
m = Left(m(1), 14)
Range("A1").Select
ActiveCell.FormulaR1C1 = m
End Sub

Michel (pen ar bed)
Le #6473171
Michel (pen ar bed) avait écrit le 01/05/2008 :


:| >:|


Mauvais copié/collé j'ai oublié une ligne entre la 5 et 6
après le premier close #1
Shell "C:WINDOWSSystem32ip.bat"

M.

stephprod
Le #6535311
Merci à tous de votre aide mais j'ai essayé vos code et en aucun cas il me
mets automatique mon ip public dans la cellule A1


"Michel (pen ar bed)"
Michel (pen ar bed) avait écrit le 01/05/2008 :


:| >:|


Mauvais copié/collé j'ai oublié une ligne entre la 5 et 6
après le premier close #1
Shell "C:WINDOWSSystem32ip.bat"

M.





Michel (pen ar bed)
Le #6535851
Merci à tous de votre aide mais j'ai essayé vos code et en aucun cas il me
mets automatique mon ip public dans la cellule A1



Cela fonctionne uniquement si votre machine est liée à un DNS dynamique
seulement (pour ceux qui sont derrière un routeur, box ... et non pas
d'ip public directe)

si je prend votre non DNS de fournisseur (Orange) qui est
LRouen-151-73-62-150.w80-13.abo.wanadoo.fr
je récupère bien en A1 "80.13.40.150" qui est votre IP

M.

stephprod
Le #6536261
merci michel
alors je dois avoir un probleme d ignorance de ma part....
je configure quoi dans ma DNS configuration réseau?

merci d avance


"Michel (pen ar bed)"
Merci à tous de votre aide mais j'ai essayé vos code et en aucun cas il
me mets automatique mon ip public dans la cellule A1



Cela fonctionne uniquement si votre machine est liée à un DNS dynamique
seulement (pour ceux qui sont derrière un routeur, box ... et non pas d'ip
public directe)

si je prend votre non DNS de fournisseur (Orange) qui est
LRouen-151-73-62-150.w80-13.abo.wanadoo.fr
je récupère bien en A1 "80.13.40.150" qui est votre IP

M.





Publicité
Poster une réponse
Anonyme