Export des utilisateurs Active Directory dans Excel

Un petit script qui vous permettra facilement d’Exporter la liste des utilisateurs et leur propriétés dans Excel,

ce script peut très simplement être modifié suivant les informations que vous désirez remonter

‘—————————————————————-
‘  Name: Export_Users_to_Excel.vbs   
‘ Exporte les Utilisateurs AD vers Excel
‘ Pekmez David 21 Sept 2005.
‘  V2:
‘ - Optimisation de l’Autofit des cellules Excel
‘ - Enregistrement du fichier dans le répertoire courant
‘ v3:
‘ - Ajout du UserAccountControl: Enabled / Disabled
"—————————————————————–

DIM accountcontrol
Const ADS_SCOPE_SUBTREE = 2
Set objExcel = CreateObject("Excel.Application")
Set fso   = WScript.CreateObject("Scripting.FileSystemObject")
objExcel.Visible = True
objExcel.Workbooks.Add

objExcel.Cells(1, 1).Value = "Liste des Comptes " & " le " & FormatDateTime(now, vbLongDate)
objExcel.Cells(1, 1).Font.Bold = TRUE
objExcel.Cells(1, 1).Font.Size = 10
objExcel.Cells(1, 1).Font.ColorIndex = 3

objExcel.Cells(2, 2).Value = "Last name"
objExcel.Cells(2, 2).Font.ColorIndex = 5
objExcel.Cells(2, 3).Value = "First name"
objExcel.Cells(2, 3).Font.ColorIndex = 5
objExcel.Cells(2, 4).Value = "samAccountName"
objExcel.Cells(2, 4).Font.ColorIndex = 5
objExcel.Cells(2, 5).Value = "Department"
objExcel.Cells(2, 5).Font.ColorIndex = 5
objExcel.Cells(2, 6).Value = "Phone number"
objExcel.Cells(2, 6).Font.ColorIndex = 5
objExcel.Cells(2, 7).Value = "Mail"
objExcel.Cells(2, 7).Font.ColorIndex = 5
objExcel.Cells(2, 8).Value = "userPrincipalName"
objExcel.Cells(2, 8).Font.ColorIndex = 5
objExcel.Cells(2, 9).Value = "distinguishedName"
objExcel.Cells(2, 9).Font.ColorIndex = 5
objExcel.Cells(2, 10).Value = "homeDirectory"
objExcel.Cells(2, 10).Font.ColorIndex = 5
objExcel.Cells(2, 11).Value = "homeDrive"
objExcel.Cells(2, 11).Font.ColorIndex = 5
objExcel.Cells(2, 12).Value = "canonicalName"
objExcel.Cells(2, 12).Font.ColorIndex = 5
objExcel.Cells(2, 13).Value = "scriptPath"
objExcel.Cells(2, 13).Font.ColorIndex = 5
objExcel.Cells(2, 14).Value = "userAccountControl"
objExcel.Cells(2, 14).Font.ColorIndex = 5
‘objExcel.Cells(2, 15).Value = "userAccountControl"
‘objExcel.Cells(2, 15).Font.ColorIndex = 5

‘ Connexion Active directory et selection des données

Set objConnection = CreateObject("ADODB.Connection")
Set objCommand =   CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"

‘ Remplacer & "‘LDAP://dc=INTRA,dc=NET’ WHERE " le nom de domaine

Set objCommand.ActiveConnection = objConnection
objCommand.Properties("Page Size") = 100
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE
objCommand.CommandText = _
    "SELECT givenName, SN, samAccountName, department, telephoneNumber, mail, userPrincipalName, distinguishedName, homeDirectory, homeDrive, canonicalName, scriptPath, userAccountControl FROM " _
        & "‘LDAP://dc=INTRA,dc=NET‘ WHERE " _
            & "objectCategory=’person’ AND objectClass=’user’ ORDER BY samAccountName"
Set objRecordSet = objCommand.Execute
objRecordSet.MoveFirst
x = 3

‘ Export des données vers Excel

Do Until objRecordSet.EOF
    objExcel.Cells(x, 2).Value = _
        objRecordSet.Fields("SN").Value
    objExcel.Cells(x, 3).Value = _
        objRecordSet.Fields("givenName").Value
    objExcel.Cells(x, 4).Value = _
        objRecordSet.Fields("samAccountName").Value
    objExcel.Cells(x, 5).Value = _
        objRecordSet.Fields("department").Value
    objExcel.Cells(x, 6).Value = _
        objRecordSet.Fields("telephoneNumber").Value
    objExcel.Cells(x, 7).Value = _
        objRecordSet.Fields("mail").Value
    objExcel.Cells(x, 8).Value = _
        objRecordSet.Fields("userPrincipalName").Value
    objExcel.Cells(x, 9).Value = _
        objRecordSet.Fields("distinguishedName").Value
    objExcel.Cells(x, 10).Value = _
        objRecordSet.Fields("homeDirectory").Value
    objExcel.Cells(x, 11).Value = _
        objRecordSet.Fields("homeDrive").Value
    objExcel.Cells(x, 12).Value = _
        objRecordSet.Fields("canonicalName").Value
    objExcel.Cells(x, 13).Value = _
        objRecordSet.Fields("scriptPath").Value
accountcontrol = objRecordSet.Fields("userAccountControl").Value

‘ Status du compte (Activé ou désactivé)

   If accountcontrol AND 2 Then
objExcel.Cells(x, 14).Value = "Disabled"
    else  objExcel.Cells(x, 14).Value = "enabled"
    end if

    x = x + 1
    objRecordSet.MoveNext
Loop

objExcel.Columns("B:N").Select
objExcel.Selection.Columns.AutoFit
objExcel.Range("A1").Select

‘ Enregistrement du fichier en "Liste des comptes du Domaine.xls"
‘ Dans le répertorie courant

ExcelFile=getpath() & "Liste des comptes du domaine" & ".xls"
If fso.FileExists(ExcelFile) Then fso.DeleteFile ExcelFile, true
objExcel.ActiveWorkbook.SaveAs ExcelFile
objExcel.ACtiveWorkbook.Saved = True
Wscript.quit

‘——————————————————————–
‘ Fonction de récupération du répertoire courant
Function GetPath()
Dim path
path = WScript.ScriptFullName
GetPath = Left(path, InStrRev(path, "\"))
End Function
‘——————————————————————–

About these ads