ping ip et solution de nom de serveur

Bonjour.
Adapté d'un forum germanique. Les résultats apparaissent dans un msgbox. A
adapter à tes besoins. La macro à exécuter est "test". Mets dans un module :

Public Const MIN_SOCKETS_REQD As Long = 1
Public Const SOCKET_ERROR As Long = -1
Public Const WSADESCRIPTION_LEN = 257
Public Const WSASYS_STATUS_LEN = 129
Public Const MAX_WSADescription = 256
Public Const MAX_WSASYSStatus = 128
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 Type HOSTENT
hName As Long
hAliases As Long
hAddrType As Integer
hLen As Integer
hAddrList As Long
End Type
Private Const AF_INET As Long = 2
Private Declare Function WSAStartupInfo Lib "WSOCK32" _
Alias "WSAStartup" (ByVal wVersionRequested As Integer, _
lpWSADATA As WSAData) As Long
Private Declare Function WSACleanup Lib "WSOCK32" _
() As Long
Private Declare Function WSAStartup Lib "WSOCK32" _
(ByVal wVersionRequired As Long, _
lpWSADATA As WSAData) As Long
Private Declare Function gethostbyaddr Lib "WSOCK32" _
(szHost As Any, ByVal dwHostLen As Integer, _
dwSocketType As Integer) As Long
Private Declare Function inet_addr Lib "WSOCK32" _
(ByVal cp As String) As Long
Private Declare Function gethostbyname Lib "WSOCK32" _
(ByVal szHost As String) As Long
Private Declare Sub CopyMemory Lib "kernel32" _
Alias "RtlMoveMemory" (hpvDest As Any, _
ByVal hpvSource As Long, ByVal cbCopy As Long)


Sub test()
Dim Testhost As String
Dim IP As String
Testhost = InputBox("Entre le nom du serveur", _
"IP abfragen", "t-online.de")
If Testhost = "" Then Exit Sub
IP = IP_von_Hostname(Testhost)
If IP = "" Then
MsgBox "Serveur introuvable"
Exit Sub
End If
MsgBox IP, , "IP de " & Testhost
Testhost = Hostname_von_IP(IP)
If Testhost = "" Then
MsgBox "Serveur introuvable"
Exit Sub
End If
MsgBox Testhost, , "Hostname von " & IP
End Sub


Public Function IP_von_Hostname(ByVal Hoststring As String) _
As String
Dim strHostname As String * 256
Dim lp_to_Hostent As Long
Dim udtHost As HOSTENT
Dim lngIP As Long
Dim buffer(1 To 4) As Byte
Dim a As Long
If Not Initialisierung() Then Exit Function
strHostname = Hoststring & vbNullChar
lp_to_Hostent = gethostbyname(strHostname)
If lp_to_Hostent = 0 Then
WSACleanup
Exit Function
End If
With udtHost
CopyMemory udtHost, lp_to_Hostent, Len(udtHost)
CopyMemory lngIP, .hAddrList, 4
CopyMemory buffer(1), lngIP, 4
For a = 1 To 4
IP_von_Hostname = IP_von_Hostname _
& buffer(a) & "."
Next
End With
IP_von_Hostname = Left$(IP_von_Hostname, _
Len(IP_von_Hostname) - 1)
WSACleanup
End Function


Public Function Hostname_von_IP(ByVal IP_String As String) _
As String
Dim lngNetwByteOrder As Long
Dim lp_to_Hostent As Long
Dim udtHost As HOSTENT
Dim buffer(1 To 4) As Byte
If Not Initialisierung() Then Exit Function
lngNetwByteOrder = inet_addr(IP_String)
CopyMemory buffer(1), VarPtr(lngNetwByteOrder), 4
lp_to_Hostent = gethostbyaddr(buffer(1), 4, AF_INET)
If lp_to_Hostent = 0 Then WSACleanup: Exit Function
CopyMemory udtHost, lp_to_Hostent, Len(udtHost)
Hostname_von_IP = String(256, 0)
CopyMemory ByVal Hostname_von_IP, udtHost.hName, 255
Hostname_von_IP = Left$(Hostname_von_IP, _
InStr(1, Hostname_von_IP, vbNullChar) - 1)
WSACleanup
End Function


Public Function Initialisierung() As Boolean
Dim udtWSAData As WSAData
If WSAStartup(MIN_SOCKETS_REQD, udtWSAData) = SOCKET_ERROR Then
Initialisierung = False
Exit Function
End If
Initialisierung = True
End Function

Daniel

<sbtlg@yahoo.fr> a écrit dans le message de news:
1183640735.469190.200460@o61g2000hsh.googlegroups.com...
Bonjour,

une petite question je voudrais savoir s'il est possible de faire un
ping ou autre commande pour recuperer par rapport au nom de serveur
l'adresse ip de ce dernier dans excel.

dans mon classeur dans une page serveur j'ai 1800 nom en colonne B et
j'aimerais afficher l'ip en colonne N.

Merci de votre aide.

bionne journée.

On 5 juil, 15:29, "Daniel" <dZZZcolarde...@free.fr> wrote:

Bonjour.
Adapté d'un forum germanique. Les résultats apparaissent dans un msgb ox. A
adapter à tes besoins. La macro à exécuter est "test". Mets dans un module :

Public Const MIN_SOCKETS_REQD As Long = 1
Public Const SOCKET_ERROR As Long = -1
Public Const WSADESCRIPTION_LEN = 257
Public Const WSASYS_STATUS_LEN = 129
Public Const MAX_WSADescription = 256
Public Const MAX_WSASYSStatus = 128
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 Type HOSTENT
hName As Long
hAliases As Long
hAddrType As Integer
hLen As Integer
hAddrList As Long
End Type
Private Const AF_INET As Long = 2
Private Declare Function WSAStartupInfo Lib "WSOCK32" _
Alias "WSAStartup" (ByVal wVersionRequested As Integer, _
lpWSADATA As WSAData) As Long
Private Declare Function WSACleanup Lib "WSOCK32" _
() As Long
Private Declare Function WSAStartup Lib "WSOCK32" _
(ByVal wVersionRequired As Long, _
lpWSADATA As WSAData) As Long
Private Declare Function gethostbyaddr Lib "WSOCK32" _
(szHost As Any, ByVal dwHostLen As Integer, _
dwSocketType As Integer) As Long
Private Declare Function inet_addr Lib "WSOCK32" _
(ByVal cp As String) As Long
Private Declare Function gethostbyname Lib "WSOCK32" _
(ByVal szHost As String) As Long
Private Declare Sub CopyMemory Lib "kernel32" _
Alias "RtlMoveMemory" (hpvDest As Any, _
ByVal hpvSource As Long, ByVal cbCopy As Long)

Sub test()
Dim Testhost As String
Dim IP As String
Testhost = InputBox("Entre le nom du serveur", _
"IP abfragen", "t-online.de")
If Testhost = "" Then Exit Sub
IP = IP_von_Hostname(Testhost)
If IP = "" Then
MsgBox "Serveur introuvable"
Exit Sub
End If
MsgBox IP, , "IP de " & Testhost
Testhost = Hostname_von_IP(IP)
If Testhost = "" Then
MsgBox "Serveur introuvable"
Exit Sub
End If
MsgBox Testhost, , "Hostname von " & IP
End Sub

Public Function IP_von_Hostname(ByVal Hoststring As String) _
As String
Dim strHostname As String * 256
Dim lp_to_Hostent As Long
Dim udtHost As HOSTENT
Dim lngIP As Long
Dim buffer(1 To 4) As Byte
Dim a As Long
If Not Initialisierung() Then Exit Function
strHostname = Hoststring & vbNullChar
lp_to_Hostent = gethostbyname(strHostname)
If lp_to_Hostent = 0 Then
WSACleanup
Exit Function
End If
With udtHost
CopyMemory udtHost, lp_to_Hostent, Len(udtHost)
CopyMemory lngIP, .hAddrList, 4
CopyMemory buffer(1), lngIP, 4
For a = 1 To 4
IP_von_Hostname = IP_von_Hostname _
& buffer(a) & "."
Next
End With
IP_von_Hostname = Left$(IP_von_Hostname, _
Len(IP_von_Hostname) - 1)
WSACleanup
End Function

Public Function Hostname_von_IP(ByVal IP_String As String) _
As String
Dim lngNetwByteOrder As Long
Dim lp_to_Hostent As Long
Dim udtHost As HOSTENT
Dim buffer(1 To 4) As Byte
If Not Initialisierung() Then Exit Function
lngNetwByteOrder = inet_addr(IP_String)
CopyMemory buffer(1), VarPtr(lngNetwByteOrder), 4
lp_to_Hostent = gethostbyaddr(buffer(1), 4, AF_INET)
If lp_to_Hostent = 0 Then WSACleanup: Exit Function
CopyMemory udtHost, lp_to_Hostent, Len(udtHost)
Hostname_von_IP = String(256, 0)
CopyMemory ByVal Hostname_von_IP, udtHost.hName, 255
Hostname_von_IP = Left$(Hostname_von_IP, _
InStr(1, Hostname_von_IP, vbNullChar) - 1)
WSACleanup
End Function

Public Function Initialisierung() As Boolean
Dim udtWSAData As WSAData
If WSAStartup(MIN_SOCKETS_REQD, udtWSAData) = SOCKET_ERROR Then
Initialisierung = False
Exit Function
End If
Initialisierung = True
End Function

Daniel

<s...@yahoo.fr> a écrit dans le message de news:
1183640735.469190.200...@o61g2000hsh.googlegroups.com...
Bonjour,

une petite question je voudrais savoir s'il est possible de faire un
ping ou autre commande pour recuperer par rapport au nom de serveur
l'adresse ip de ce dernier dans excel.

dans mon classeur dans une page serveur j'ai 1800 nom en colonne B et
j'aimerais afficher l'ip en colonne N.

Merci de votre aide.

bionne journée.

Bonjour Daniel,

et merci pour cette fonction et la macro vais essayé d'adapter cela
mais c pas gagné.

Bye.

On 5 juil, 17:02, s...@yahoo.fr wrote:

On 5 juil, 15:29, "Daniel" <dZZZcolarde...@free.fr> wrote:

Bonjour.
Adapté d'un forum germanique. Les résultats apparaissent dans un ms gbox. A
adapter à tes besoins. La macro à exécuter est "test". Mets dans un module :

Public Const MIN_SOCKETS_REQD As Long = 1
Public Const SOCKET_ERROR As Long = -1
Public Const WSADESCRIPTION_LEN = 257
Public Const WSASYS_STATUS_LEN = 129
Public Const MAX_WSADescription = 256
Public Const MAX_WSASYSStatus = 128
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 Type HOSTENT
hName As Long
hAliases As Long
hAddrType As Integer
hLen As Integer
hAddrList As Long
End Type
Private Const AF_INET As Long = 2
Private Declare Function WSAStartupInfo Lib "WSOCK32" _
Alias "WSAStartup" (ByVal wVersionRequested As Integer, _
lpWSADATA As WSAData) As Long
Private Declare Function WSACleanup Lib "WSOCK32" _
() As Long
Private Declare Function WSAStartup Lib "WSOCK32" _
(ByVal wVersionRequired As Long, _
lpWSADATA As WSAData) As Long
Private Declare Function gethostbyaddr Lib "WSOCK32" _
(szHost As Any, ByVal dwHostLen As Integer, _
dwSocketType As Integer) As Long
Private Declare Function inet_addr Lib "WSOCK32" _
(ByVal cp As String) As Long
Private Declare Function gethostbyname Lib "WSOCK32" _
(ByVal szHost As String) As Long
Private Declare Sub CopyMemory Lib "kernel32" _
Alias "RtlMoveMemory" (hpvDest As Any, _
ByVal hpvSource As Long, ByVal cbCopy As Long)

Sub test()
Dim Testhost As String
Dim IP As String
Testhost = InputBox("Entre le nom du serveur", _
"IP abfragen", "t-online.de")
If Testhost = "" Then Exit Sub
IP = IP_von_Hostname(Testhost)
If IP = "" Then
MsgBox "Serveur introuvable"
Exit Sub
End If
MsgBox IP, , "IP de " & Testhost
Testhost = Hostname_von_IP(IP)
If Testhost = "" Then
MsgBox "Serveur introuvable"
Exit Sub
End If
MsgBox Testhost, , "Hostname von " & IP
End Sub

Public Function IP_von_Hostname(ByVal Hoststring As String) _
As String
Dim strHostname As String * 256
Dim lp_to_Hostent As Long
Dim udtHost As HOSTENT
Dim lngIP As Long
Dim buffer(1 To 4) As Byte
Dim a As Long
If Not Initialisierung() Then Exit Function
strHostname = Hoststring & vbNullChar
lp_to_Hostent = gethostbyname(strHostname)
If lp_to_Hostent = 0 Then
WSACleanup
Exit Function
End If
With udtHost
CopyMemory udtHost, lp_to_Hostent, Len(udtHost)
CopyMemory lngIP, .hAddrList, 4
CopyMemory buffer(1), lngIP, 4
For a = 1 To 4
IP_von_Hostname = IP_von_Hostname _
& buffer(a) & "."
Next
End With
IP_von_Hostname = Left$(IP_von_Hostname, _
Len(IP_von_Hostname) - 1)
WSACleanup
End Function

Public Function Hostname_von_IP(ByVal IP_String As String) _
As String
Dim lngNetwByteOrder As Long
Dim lp_to_Hostent As Long
Dim udtHost As HOSTENT
Dim buffer(1 To 4) As Byte
If Not Initialisierung() Then Exit Function
lngNetwByteOrder = inet_addr(IP_String)
CopyMemory buffer(1), VarPtr(lngNetwByteOrder), 4
lp_to_Hostent = gethostbyaddr(buffer(1), 4, AF_INET)
If lp_to_Hostent = 0 Then WSACleanup: Exit Function
CopyMemory udtHost, lp_to_Hostent, Len(udtHost)
Hostname_von_IP = String(256, 0)
CopyMemory ByVal Hostname_von_IP, udtHost.hName, 255
Hostname_von_IP = Left$(Hostname_von_IP, _
InStr(1, Hostname_von_IP, vbNullChar) - 1)
WSACleanup
End Function

Public Function Initialisierung() As Boolean
Dim udtWSAData As WSAData
If WSAStartup(MIN_SOCKETS_REQD, udtWSAData) = SOCKET_ERROR Then
Initialisierung = False
Exit Function
End If
Initialisierung = True
End Function

Daniel

<s...@yahoo.fr> a écrit dans le message de news:
1183640735.469190.200...@o61g2000hsh.googlegroups.com...
Bonjour,

une petite question je voudrais savoir s'il est possible de faire un
ping ou autre commande pour recuperer par rapport au nom de serveur
l'adresse ip de ce dernier dans excel.

dans mon classeur dans une page serveur j'ai 1800 nom en colonne B et
j'aimerais afficher l'ip en colonne N.

Merci de votre aide.

bionne journée.

Bonjour Daniel,

et merci pour cette fonction et la macro vais essayé d'adapter cela
mais c pas gagné.

Bye.- Masquer le texte des messages précédents -

- Afficher le texte des messages précédents -

re moi encore le grand débutant..

je vois pas bien comment interroger sur la colonne A et afficher le
resultat en B.

Pourrais tu mettre sur la voie ?

Merci d'avance.


Sub test()
Dim Testhost As String
Dim IP As String
Testhost = InputBox("Entre le nom du serveur", _
"IP abfragen", "t-online.de")
If Testhost = "" Then Exit Sub
IP = IP_von_Hostname(Testhost)
If IP = "" Then
MsgBox "Serveur introuvable"
Exit Sub
End If
MsgBox IP, , "IP de " & Testhost
Testhost = Hostname_von_IP(IP)
If Testhost = "" Then
MsgBox "Serveur introuvable"
Exit Sub
End If
MsgBox Testhost, , "Hostname von " & IP
End Sub

Bonjour sbtlg;
Moi, je tenterais bien simplement ceci:

Sub GetIP()
Dim y&
With ThisWorkbook.Sheets("Serveur")
For y = 1 To .Cells(.Rows.Count, 2).End(xlUp).Row
On Error Resume Next
.Cells(y, 14) = IPA(.Cells(y, 2))
Next y
End With
End Sub

' Computer = "." => PC local
Private Function IPA(Computer$) As String
Dim IP As Object, i As Byte
For Each IP In GetObject("winmgmts:\" & Computer & "rootcimv2") _
.ExecQuery("Select IPAddress from Win32_NetworkAdapterConfiguration")
If Not IsNull(IP.IPAddress) Then
For i = LBound(IP.IPAddress) To UBound(IP.IPAddress)
If Len(IPA) Then IPA = IPA & vbLf
IPA = IPA & IP.IPAddress(i)
Next i
End If
Next IP
End Function

MP

<sbtlg@yahoo.fr> a écrit dans le message de news:
1183640735.469190.200460@o61g2000hsh.googlegroups.com...
Bonjour,

une petite question je voudrais savoir s'il est possible de faire un
ping ou autre commande pour recuperer par rapport au nom de serveur
l'adresse ip de ce dernier dans excel.

dans mon classeur dans une page serveur j'ai 1800 nom en colonne B et
j'aimerais afficher l'ip en colonne N.

Merci de votre aide.

bionne journée.

Deux cas. Si tes serveurs sont sur le réseau local, utilise la macro de
Michel Pierron. S'ils sont sur internet, au lieu de la macro test, utilise :

Sub RecIP()
Dim c As Range, ip As String
For Each c In Range("B1", Range("B65536").End(xlUp))
c.Offset(, 12) = IP_von_Hostname(c.Value)
Next c
End Sub

En gardant tout le reste.
Daniel
<sbtlg@yahoo.fr> a écrit dans le message de news:
1183650237.826530.264450@k79g2000hse.googlegroups.com...
On 5 juil, 17:02, s...@yahoo.fr wrote:

On 5 juil, 15:29, "Daniel" <dZZZcolarde...@free.fr> wrote:

Bonjour.
Adapté d'un forum germanique. Les résultats apparaissent dans un msgbox.
A
adapter à tes besoins. La macro à exécuter est "test". Mets dans un
module :

Public Const MIN_SOCKETS_REQD As Long = 1
Public Const SOCKET_ERROR As Long = -1
Public Const WSADESCRIPTION_LEN = 257
Public Const WSASYS_STATUS_LEN = 129
Public Const MAX_WSADescription = 256
Public Const MAX_WSASYSStatus = 128
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 Type HOSTENT
hName As Long
hAliases As Long
hAddrType As Integer
hLen As Integer
hAddrList As Long
End Type
Private Const AF_INET As Long = 2
Private Declare Function WSAStartupInfo Lib "WSOCK32" _
Alias "WSAStartup" (ByVal wVersionRequested As Integer, _
lpWSADATA As WSAData) As Long
Private Declare Function WSACleanup Lib "WSOCK32" _
() As Long
Private Declare Function WSAStartup Lib "WSOCK32" _
(ByVal wVersionRequired As Long, _
lpWSADATA As WSAData) As Long
Private Declare Function gethostbyaddr Lib "WSOCK32" _
(szHost As Any, ByVal dwHostLen As Integer, _
dwSocketType As Integer) As Long
Private Declare Function inet_addr Lib "WSOCK32" _
(ByVal cp As String) As Long
Private Declare Function gethostbyname Lib "WSOCK32" _
(ByVal szHost As String) As Long
Private Declare Sub CopyMemory Lib "kernel32" _
Alias "RtlMoveMemory" (hpvDest As Any, _
ByVal hpvSource As Long, ByVal cbCopy As Long)

Sub test()
Dim Testhost As String
Dim IP As String
Testhost = InputBox("Entre le nom du serveur", _
"IP abfragen", "t-online.de")
If Testhost = "" Then Exit Sub
IP = IP_von_Hostname(Testhost)
If IP = "" Then
MsgBox "Serveur introuvable"
Exit Sub
End If
MsgBox IP, , "IP de " & Testhost
Testhost = Hostname_von_IP(IP)
If Testhost = "" Then
MsgBox "Serveur introuvable"
Exit Sub
End If
MsgBox Testhost, , "Hostname von " & IP
End Sub

Public Function IP_von_Hostname(ByVal Hoststring As String) _
As String
Dim strHostname As String * 256
Dim lp_to_Hostent As Long
Dim udtHost As HOSTENT
Dim lngIP As Long
Dim buffer(1 To 4) As Byte
Dim a As Long
If Not Initialisierung() Then Exit Function
strHostname = Hoststring & vbNullChar
lp_to_Hostent = gethostbyname(strHostname)
If lp_to_Hostent = 0 Then
WSACleanup
Exit Function
End If
With udtHost
CopyMemory udtHost, lp_to_Hostent, Len(udtHost)
CopyMemory lngIP, .hAddrList, 4
CopyMemory buffer(1), lngIP, 4
For a = 1 To 4
IP_von_Hostname = IP_von_Hostname _
& buffer(a) & "."
Next
End With
IP_von_Hostname = Left$(IP_von_Hostname, _
Len(IP_von_Hostname) - 1)
WSACleanup
End Function

Public Function Hostname_von_IP(ByVal IP_String As String) _
As String
Dim lngNetwByteOrder As Long
Dim lp_to_Hostent As Long
Dim udtHost As HOSTENT
Dim buffer(1 To 4) As Byte
If Not Initialisierung() Then Exit Function
lngNetwByteOrder = inet_addr(IP_String)
CopyMemory buffer(1), VarPtr(lngNetwByteOrder), 4
lp_to_Hostent = gethostbyaddr(buffer(1), 4, AF_INET)
If lp_to_Hostent = 0 Then WSACleanup: Exit Function
CopyMemory udtHost, lp_to_Hostent, Len(udtHost)
Hostname_von_IP = String(256, 0)
CopyMemory ByVal Hostname_von_IP, udtHost.hName, 255
Hostname_von_IP = Left$(Hostname_von_IP, _
InStr(1, Hostname_von_IP, vbNullChar) - 1)
WSACleanup
End Function

Public Function Initialisierung() As Boolean
Dim udtWSAData As WSAData
If WSAStartup(MIN_SOCKETS_REQD, udtWSAData) = SOCKET_ERROR Then
Initialisierung = False
Exit Function
End If
Initialisierung = True
End Function

Daniel

<s...@yahoo.fr> a écrit dans le message de news:
1183640735.469190.200...@o61g2000hsh.googlegroups.com...
Bonjour,

une petite question je voudrais savoir s'il est possible de faire un
ping ou autre commande pour recuperer par rapport au nom de serveur
l'adresse ip de ce dernier dans excel.

dans mon classeur dans une page serveur j'ai 1800 nom en colonne B et
j'aimerais afficher l'ip en colonne N.

Merci de votre aide.

bionne journée.

Bonjour Daniel,

et merci pour cette fonction et la macro vais essayé d'adapter cela
mais c pas gagné.

Bye.- Masquer le texte des messages précédents -

- Afficher le texte des messages précédents -

re moi encore le grand débutant..

je vois pas bien comment interroger sur la colonne A et afficher le
resultat en B.

Pourrais tu mettre sur la voie ?

Merci d'avance.


Sub test()
Dim Testhost As String
Dim IP As String
Testhost = InputBox("Entre le nom du serveur", _
"IP abfragen", "t-online.de")
If Testhost = "" Then Exit Sub
IP = IP_von_Hostname(Testhost)
If IP = "" Then
MsgBox "Serveur introuvable"
Exit Sub
End If
MsgBox IP, , "IP de " & Testhost
Testhost = Hostname_von_IP(IP)
If Testhost = "" Then
MsgBox "Serveur introuvable"
Exit Sub
End If
MsgBox Testhost, , "Hostname von " & IP
End Sub

On 5 juil, 19:27, "Daniel" <dZZZcolarde...@free.fr> wrote:

Deux cas. Si tes serveurs sont sur le réseau local, utilise la macro de
Michel Pierron. S'ils sont sur internet, au lieu de la macro test, utilis e :

Sub RecIP()
Dim c As Range, ip As String
For Each c In Range("B1", Range("B65536").End(xlUp))
c.Offset(, 12) = IP_von_Hostname(c.Value)
Next c
End Sub

En gardant tout le reste.
Daniel
<s...@yahoo.fr> a écrit dans le message de news:
1183650237.826530.264...@k79g2000hse.googlegroups.com...
On 5 juil, 17:02, s...@yahoo.fr wrote:

On 5 juil, 15:29, "Daniel" <dZZZcolarde...@free.fr> wrote:

Bonjour.
Adapté d'un forum germanique. Les résultats apparaissent dans un msgbox.
A
adapter à tes besoins. La macro à exécuter est "test". Mets dan s un
module :

Public Const MIN_SOCKETS_REQD As Long = 1
Public Const SOCKET_ERROR As Long = -1
Public Const WSADESCRIPTION_LEN = 257
Public Const WSASYS_STATUS_LEN = 129
Public Const MAX_WSADescription = 256
Public Const MAX_WSASYSStatus = 128
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 Type HOSTENT
hName As Long
hAliases As Long
hAddrType As Integer
hLen As Integer
hAddrList As Long
End Type
Private Const AF_INET As Long = 2
Private Declare Function WSAStartupInfo Lib "WSOCK32" _
Alias "WSAStartup" (ByVal wVersionRequested As Integer, _
lpWSADATA As WSAData) As Long
Private Declare Function WSACleanup Lib "WSOCK32" _
() As Long
Private Declare Function WSAStartup Lib "WSOCK32" _
(ByVal wVersionRequired As Long, _
lpWSADATA As WSAData) As Long
Private Declare Function gethostbyaddr Lib "WSOCK32" _
(szHost As Any, ByVal dwHostLen As Integer, _
dwSocketType As Integer) As Long
Private Declare Function inet_addr Lib "WSOCK32" _
(ByVal cp As String) As Long
Private Declare Function gethostbyname Lib "WSOCK32" _
(ByVal szHost As String) As Long
Private Declare Sub CopyMemory Lib "kernel32" _
Alias "RtlMoveMemory" (hpvDest As Any, _
ByVal hpvSource As Long, ByVal cbCopy As Long)

Sub test()
Dim Testhost As String
Dim IP As String
Testhost = InputBox("Entre le nom du serveur", _
"IP abfragen", "t-online.de")
If Testhost = "" Then Exit Sub
IP = IP_von_Hostname(Testhost)
If IP = "" Then
MsgBox "Serveur introuvable"
Exit Sub
End If
MsgBox IP, , "IP de " & Testhost
Testhost = Hostname_von_IP(IP)
If Testhost = "" Then
MsgBox "Serveur introuvable"
Exit Sub
End If
MsgBox Testhost, , "Hostname von " & IP
End Sub

Public Function IP_von_Hostname(ByVal Hoststring As String) _
As String
Dim strHostname As String * 256
Dim lp_to_Hostent As Long
Dim udtHost As HOSTENT
Dim lngIP As Long
Dim buffer(1 To 4) As Byte
Dim a As Long
If Not Initialisierung() Then Exit Function
strHostname = Hoststring & vbNullChar
lp_to_Hostent = gethostbyname(strHostname)
If lp_to_Hostent = 0 Then
WSACleanup
Exit Function
End If
With udtHost
CopyMemory udtHost, lp_to_Hostent, Len(udtHost)
CopyMemory lngIP, .hAddrList, 4
CopyMemory buffer(1), lngIP, 4
For a = 1 To 4
IP_von_Hostname = IP_von_Hostname _
& buffer(a) & "."
Next
End With
IP_von_Hostname = Left$(IP_von_Hostname, _
Len(IP_von_Hostname) - 1)
WSACleanup
End Function

Public Function Hostname_von_IP(ByVal IP_String As String) _
As String
Dim lngNetwByteOrder As Long
Dim lp_to_Hostent As Long
Dim udtHost As HOSTENT
Dim buffer(1 To 4) As Byte
If Not Initialisierung() Then Exit Function
lngNetwByteOrder = inet_addr(IP_String)
CopyMemory buffer(1), VarPtr(lngNetwByteOrder), 4
lp_to_Hostent = gethostbyaddr(buffer(1), 4, AF_INET)
If lp_to_Hostent = 0 Then WSACleanup: Exit Function
CopyMemory udtHost, lp_to_Hostent, Len(udtHost)
Hostname_von_IP = String(256, 0)
CopyMemory ByVal Hostname_von_IP, udtHost.hName, 255
Hostname_von_IP = Left$(Hostname_von_IP, _
InStr(1, Hostname_von_IP, vbNullChar) - 1)
WSACleanup
End Function

Public Function Initialisierung() As Boolean
Dim udtWSAData As WSAData
If WSAStartup(MIN_SOCKETS_REQD, udtWSAData) = SOCKET_ERROR Then
Initialisierung = False
Exit Function
End If
Initialisierung = True
End Function

Daniel

<s...@yahoo.fr> a écrit dans le message de news:
1183640735.469190.200...@o61g2000hsh.googlegroups.com...
Bonjour,

une petite question je voudrais savoir s'il est possible de faire un
ping ou autre commande pour recuperer par rapport au nom de serveur
l'adresse ip de ce dernier dans excel.

dans mon classeur dans une page serveur j'ai 1800 nom en colonne B et
j'aimerais afficher l'ip en colonne N.

Merci de votre aide.

bionne journée.

Bonjour Daniel,

et merci pour cette fonction et la macro vais essayé d'adapter cela
mais c pas gagné.

Bye.- Masquer le texte des messages précédents -

- Afficher le texte des messages précédents -

re moi encore le grand débutant..

je vois pas bien comment interroger sur la colonne A et afficher le
resultat en B.

Pourrais tu mettre sur la voie ?

Merci d'avance.

Sub test()
Dim Testhost As String
Dim IP As String
Testhost = InputBox("Entre le nom du serveur", _
"IP abfragen", "t-online.de")
If Testhost = "" Then Exit Sub
IP = IP_von_Hostname(Testhost)
If IP = "" Then
MsgBox "Serveur introuvable"
Exit Sub
End If
MsgBox IP, , "IP de " & Testhost
Testhost = Hostname_von_IP(IP)
If Testhost = "" Then
MsgBox "Serveur introuvable"
Exit Sub
End If
MsgBox Testhost, , "Hostname von " & IP
End Sub- Masquer le texte des messages précédents -

- Afficher le texte des messages précédents -

*
Merci je testerais demain pour les 2 macros car je dois verifier ,
J'ai des serveurs sur un reseau local et d'autre nom et certain ne
reponde pas au donc je verais demain.


bonne soiree et encore merci a tous