Export Active Directory Users to Excel Worksheet

The snippet below is taken from http://www.rlmueller.net/ADOSearchTips.htm:
 
You will see that memberof is a multivalued attribute and will have to be scanned with another inner For Next while the Do Until is moving through the ADO recordset -- great stuff!
 
 
*******  R. Mueller stuff starts here  ****************
Most Active Directory attributes have string values, so you can echo the values directly, or assign the values to variables. Some Active Directory attributes are not single-valued strings. Multi-valued attributes are returned by ADO as arrays. Examples include the attributes memberOf, tokenGroups, directReport, otherHomePhone, and objectClass. In these cases, the Value property of the Fields collection will be Null if there are no values in the multi-valued attribute, and will be an array if there is one or more values. For example, if the list of attributes includes the sAMAcountName and memberOf attributes, you could enumerate the Recordset object with a loop similar to:
 

 '  I did a slight adjustment in R. Muellers' code example in the For-Next loop, S. Wolfe 
 Do Until ADO.RecordSet.EOF
      strName = adoRecordSet.Fields("sAMAAccountName").Value
      Wscript.Echo "User: " & strName
      arrGroups = adoRecordSte.Fields("MemberOf").Value
      ' Note the above statement loads the array
      If IsNull(arrGroups) Then
          Wscript.Echo " -- No Group Memberships"
      Else
          For Each strGroup in arrGroups
                 Wscript.Echo "Member of Group: " & strGroup
          Next
      End If
      adorecordset.MoveNext
 Loop
 

 
hth

IN Line 126 [Set objwb = objExcel.ActiveWorkbook.Worksheets(shtName)] I have a "Subscript out of range" error. Any ideas?

Hi,
i have the same Problem. Any idea what the problem is? And yes, i can see the excel.exe is running in the Taskmanager!
(the Script in the first post)

live long and prosper

Super thanks, your script started me working on creating a phone list (several, internal and external) from my AD

Have started working with Office 2007 as well as 2003 so here is a excel "save by version" snippet of code.

Thanks Again.

   Const xlExcel8 = 56
      If objExcel.Version < 12 Then
         objwb.SaveAs "c:\temp\PhoneList_Office2003.xls"
      Else
         objwb.SaveAs "c:\temp\PhoneList_Office2007.xls", xlExcel8
      End If
 

Hi Im fairly new to all this
 
Ive used this script to query what I require but there are two things wrong
 
Here is the script after i amended it
 

  Dim ObjWb 
 Dim ObjExcel 
 Dim x, zz 
 Set objRoot = GetObject("[link=http://www.visualbasicscript.com/ldap://RootDSE]LDAP://RootDSE[/link]") 
 strDNC = objRoot.Get("DefaultNamingContext") 
 Set objDomain = GetObject("LDAP://" & strDNC) ' Bind to the top of the Domain using LDAP using ROotDSE  
 Call ExcelSetup("Sheet1") ' Sub to make Excel Document 
 x = 1 
 Call enummembers(objDomain) 
 Sub enumMembers(objDomain) 
    On Error Resume Next 
    Dim Secondary(20) ' Variable to store the Array of 2ndary email alias's 
    For Each objMember In objDomain ' go through the collection     
        If ObjMember.Class = "user" then 
            x = x +1 ' counter used to increment the cells in Excel 
            
              objwb.Cells(x, 1).Value = objMember.Class 
              ' I set AD properties to variables so if needed you could do Null checks or add if/then's to this code 
              ' this was done so the script could be modified easier. 
            SamAccountName = ObjMember.samAccountName 
            Cn = ObjMember.CN 
            Department = objMember.Department 
            LastLogin = objMember.LastLogin 
     PasswordLastChanged = objMember.PasswordLastChanged 
    
            ' Write the values to Excel, using the X counter to increment the rows. 
            
            objwb.Cells(x, 2).Value = SamAccountName 
            objwb.Cells(x, 3).Value = CN 
            objwb.Cells(x, 4).Value = Department  
            objwb.Cells(x, 5).Value = LastLogin 
     objwb.Cells(x, 6).Value = PasswordLastChanged
  
            ' Blank out Variables in case the next object doesn't have a value for the property 
            SamAccountName = "-" 
            Cn = "-" 
            Department = "-"  
            LastLogin = "-" 
     PasswordLastChanged = "_"
        End If 
              
              ' If the AD enumeration runs into an OU object, call the Sub again to itinerate 
              
        If objMember.Class = "organizationalUnit" or OBjMember.Class = "container" Then 
            enumMembers (objMember) 
        End If 
    Next 
 End Sub 
 Sub ExcelSetup(shtName) ' This sub creates an Excel worksheet and adds Column heads to the 1st row 
    Set ObjExcel = CreateObject("Excel.Application") 
    Set objwb = objExcel.Workbooks.Add 
    Set objwb = objExcel.ActiveWorkbook.Worksheets(shtName) 
    Objwb.Name = "Active Directory Users" ' name the sheet 
    objwb.Activate 
    ObjExcel.Visible = True 
    objwb.Cells(1, 2).Value = "SamAccountName" 
    objwb.Cells(1, 3).Value = "CN" 
    objwb.Cells(1, 4).Value = "Department" 
    objwb.Cells(1, 5).Value = "LastLogin"
    objwb.Cells(1, 6).Value = "PassWordLastChanged"   
    'formatting for header 
    Set objRange = objExcel.Range("A1","Z1")
    objRange.Interior.ColorIndex = 33
    objRange.Font.Bold = True
    objRange.Font.Underline = True
 End Sub 
 'autofit the output
 Set objRange = objwb.UsedRange
 objRange.EntireColumn.Autofit()
 ObjExcel.Save("ADoutput.xls")
 MsgBox "Done" ' show that script is complete 
 
 

 
Firstly the last login stamp isn't working as we have more than one dc, and it only gets the information from the dc the computer you are logged into. How can i get the information from all the dcs for the last login stamp?
 
Secondly I dont want admin accounts to be included. If i add them all to one group how can i specify the script to exclude all accounts added to that group?

Anyone willing to help me with a simple script?
I"m looking for something that outputs the OU and it's members, with their primary email.

Since this is a new script I'm opening a new thread here.

ORIGINAL: DataBase
we are making an upgrade for both Hardware and OS (from 2000 to 2003).
and I will use your tool to migrate the users from the old server on to another (I need a tool that will export to a file, then import from it).
Thank you so much for your work

Wow.  DCpromo the new server.  Verify replication.  I recomend you leave the old server up for a bit (a week or two).  Move all domain roles.  DCpromo the old server to demote it down to a member server.  Turn the old server off.  Upgrade domain working level from 2000 to 2003.... Profit?

This script should not be used for backup or migration.  I see it's use more as a good way to dump all AD values so that you can dome some Excel magic to update alot of fields and have them reimported.

Oh, and sorry for the thread necromancy.  Consider this a bump of a realy good resource.

edavis6678

This script when run will export every "user" object in Active Directory and put into an Excel Spreadsheet.

Just what I have been looking for.... BUT we have a very large domain with multiple OUs... Anyway I can limit it to a particular OU? Newbie in the arena of AD scripting so please be kind.... 8^)

d

Hi,
Thanks for Script,
I have a problem, the script works fine means no errors it shoes "Done" but users will not be created in AD.
Can you please help me....
 
Nilesh..