Trouver l'adresse IP d'un pc avec VBA pour excel

Bonjour aussi
Oui c'est possible, voici le code de Randy Birch (attention il y a du
copyright)
Exécuter la macro TestIP pour afficher l'adresse IP.
Cordialement
Pascal

Option Explicit
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Copyright ©1996-2000 VBnet, Randy Birch, All Rights Reserved.
' Some pages may also contain other copyrights by the author.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' You are free to use this code within your own applications,
' but you are expressly forbidden from selling or otherwise
' distributing this source code without prior written consent.
' This includes both posting free demo projects made from this
' code as well as reproducing the code in text or html format.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

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

'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
Sub TestIp()
Dim LadresseIP As String
LadresseIP = GetIPAddress()
MsgBox LadresseIP
End Sub



"FB" <anonymous@discussions.microsoft.com> a écrit dans le message de
news:336a01c4aab6$2d3da140$a301280a@phx.gbl...
Est ce que quelqu'un peut me dire comment faire en VBA
pour aller rechercher une adresse IP
ou le n°ID du PC ou le nom du PC????

(Ce serais pour faire une selection de programme en
fonction que l'on ouvre sur un pc ou sur un autre)

Bonjour,
Grâce à des ressources puisées sur internet
j'ai adapté ceci qui lit le num"ro de série
le nom d'utilisateur et crée un état des Addins
installés, mettre dans module standard et
lancer veriAdd:
'********
Declare Function GetUserName Lib "advapi32.dll"
Alias "GetUserNameA" ( _
ByVal lpBuffer As String, _
nSize As Long) As Long
Declare Function GetComputerName Lib "kernel32"
Alias "GetComputerNameA" ( _
ByVal lpBuffer As String, _
nSize As Long) As Long
'******

Public Function ComputerName() As String
' Retourne le nom de l'ordinateur
Dim stTmp1 As String, lgTmp1 As Long
stTmp1 = Space$(250)
lgTmp1 = 251
Call GetComputerName(stTmp1, lgTmp1)
ComputerName = stTmp1 'Split(stTmp, Chr$(0))(0)
End Function




Public Function UserName() As String
' Retourne le nom de l'utilisateur courant de l'ordinateur
Dim stTmp2 As String, lgTmp2 As Long
stTmp2 = Space$(250)
lgTmp2 = 251
Call GetUserName(stTmp2, lgTmp2)
UserName = Mid$(stTmp2, 1, InStr(1, stTmp2, Chr$(0)) - 1)
End Function



'********
Sub veriAdd()
'ajoute une feuille
'etat des addins
Dim madd As AddIn
Dim i As Integer
i = 1



Sheets.Add
ActiveSheet.Name = "F" & Format(Now, "ddmmyyhhnnss")

[A1] = ComputerName
[A1] = "Poste: " & [A1] & " Util: " & UserName
[b1] = "fichier"
[c1] = "Etat"
For Each madd In AddIns
i = i + 1
ActiveSheet.Cells(i, 1) = madd.Title
ActiveSheet.Cells(i, 2) = madd.FullName
'on peut aussi utiliser path et name
ActiveSheet.Cells(i, 3) = madd.Installed
Next
Columns("A:C").EntireColumn.AutoFit
ActiveWindow.Zoom = 81

Range("A1:C1").Select
With Selection.Interior
.ColorIndex = 43
.Pattern = xlSolid
End With
Selection.Font.Bold = True
[A1].Select
End Sub
'******
'lsteph

-----Message d'origine-----
Est ce que quelqu'un peut me dire comment faire en VBA
pour aller rechercher une adresse IP
ou le n°ID du PC ou le nom du PC????

(Ce serais pour faire une selection de programme en
fonction que l'on ouvre sur un pc ou sur un autre)
.

Bonsoir FB;
Pas testé, mais ça devrait rouler:

Sub ScruteReseau()
Application.ScreenUpdating = False
Dim oWin As Object, Item As Object
Dim Domain$(), y%, i%, j%
Set oWin = GetObject("WinNT:")
For Each Item In oWin
i = i + 1
ReDim Preserve Domain(1 To i)
Domain(i) = Item.Name
If i = 1 Then Workbooks.Add Else Sheets.Add
ActiveSheet.Name = Domain(i)
For y = 1 To 4
Cells(1, y) = Choose(y, "PC NAME", "USER LOG", "USER NAME", "DESCRIPTION")
Cells(1, y).Font.Bold = True
Next y
Range("A2").Select
ActiveWindow.FreezePanes = True
Next Item
If i = 0 Then Exit Sub
For j = 1 To i
Sheets(Domain(j)).Activate
Set oWin = GetObject("WinNT://" & Domain(j))
oWin.Filter = Array("computer"): y = 1
For Each Item In oWin
y = y + 1: Cells(y, 1) = Item.Name
Next Item
oWin.Filter = Array("user"): y = 1
For Each Item In oWin
y = y + 1: Cells(y, 2) = Item.Name
Cells(y, 3) = Item.FullName
Cells(y, 4) = Item.Description
Next Item
Columns("A:D").Columns.AutoFit
Next j
Set oWin = Nothing
End Sub

MP

"FB" <anonymous@discussions.microsoft.com> a écrit dans le message de
news:336a01c4aab6$2d3da140$a301280a@phx.gbl...
Est ce que quelqu'un peut me dire comment faire en VBA
pour aller rechercher une adresse IP
ou le n°ID du PC ou le nom du PC????

(Ce serais pour faire une selection de programme en
fonction que l'on ouvre sur un pc ou sur un autre)