afficher l'adresse IP dans une cellule

Nikom

XLDnaute Nouveau
Bonjour le forum

j'aimerais afficher l'adresse IP de ma machine dans une cellule excel via une macro.
j'ai essayé ceci

'IP de l'ordinateur
Private Declare Function apiGetComputername Lib "kernel32" Alias _
"GetComputernameA" (ByVal lpBuffer As String, nSize As Long) As Long

Sub Display_UserIP()
MsgBox fOSMachinename
End Sub

Function fOSMachinename() As String
Dim lngLen As Long
Dim lngX As Long
Dim strCompname As String
lngLen = 16
strCompname = String$(lngLen, 0)
lngX = apiGetComputername(strCompname, lngLen)
If lngX <> 0 Then
fOSMachinename = Left$(strCompname, lngLen)
Else
fOSMachinename = ""
End If
End Function



mais j'ai le message d'erreur suivant:
Erreur d'execution '453':

Point d'entrée GetComputernameA d'une DLL introuvable dans kernel32

Merci de votre réponse

Nikom
 

RENAUDER

Nous a quitté
Repose en paix
Re : afficher l'adresse IP dans une cellule

Bonjour,
Voici du code qui fonctionne.
Tu lances la procédure RecupereIP et tu as l'adresse IP du poste.

Code:
Public Const MIN_SOCKETS_REQD As Long = 1
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 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
Type WSADataInfo
    wVersion As Integer
    wHighVersion As Integer
    szDescription As String * WSADESCRIPTION_LEN
    szSystemStatus As String * WSASYS_STATUS_LEN
    iMaxSockets As Integer
    iMaxUdpDg As Integer
    lpVendorInfo As String
End Type
Public Type HOSTENT
    hName As Long
    hAliases As Long
    hAddrType As Integer
    hLen As Integer
    hAddrList As Long
End Type
Declare Function WSAStartupInfo Lib "WSOCK32" Alias "WSAStartup" (ByVal wVersionRequested As Integer, lpWSADATA As _
                                                                                                      WSADataInfo) As Long
Declare Function WSACleanup Lib "WSOCK32" () As Long
Declare Function WSAGetLastError Lib "WSOCK32" () As Long
Declare Function WSAStartup Lib "WSOCK32" (ByVal wVersionRequired As Long, lpWSADATA As WSAData) As Long
Declare Function gethostname Lib "WSOCK32" (ByVal szHost As String, ByVal dwHostLen As Long) As Long
Declare Function gethostbyname Lib "WSOCK32" (ByVal szHost As String) As Long
Declare Sub CopyMemoryIP Lib "kernel32" Alias "RtlMoveMemory" (hpvDest As Any, ByVal hpvSource As Long, ByVal cbCopy As Long)
 
Sub RecupereIP()
    MsgBox GetIPAddress
End Sub
 
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
    CopyMemoryIP HOST, lpHost, Len(HOST)
    CopyMemoryIP dwIPAddr, HOST.hAddrList, 4
    ReDim tmpIPAddr(1 To HOST.hLen)
    CopyMemoryIP 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 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
 

MJ13

XLDnaute Barbatruc
Re : afficher l'adresse IP dans une cellule

Bonjour à tous

Voici une méthode basé sur le shell et ipconfig. On récupère un fichier txt.
C'est facilement adaptable.
 

Pièces jointes

  • ipconfig.xls
    26.5 KB · Affichages: 403
  • ipconfig.xls
    26.5 KB · Affichages: 422
  • ipconfig.xls
    26.5 KB · Affichages: 410

Nikom

XLDnaute Nouveau
Re : afficher l'adresse IP dans une cellule

Bonjour RENAUDER, MJ13

merci de vos réponses, elles marchent toutes les deux.
du code comme ça, ça ne s'invente pas avec l'expérience que j'ai.
la premiere solution est plus adaptée à mon besoin.
la seconde peut être utile lorsque l'on a besoin de plus d'infos que l'adresse ip.

merci à tous les deux et bon WE

Nikom
 

Discussions similaires

Statistiques des forums

Discussions
312 316
Messages
2 087 172
Membres
103 490
dernier inscrit
zatougraf