> Here is a ready-made script that I started from Mr Mueller's example. I
> added a "bit" to it.
> The line at 31 controls where it saves the file. At 41, set your OU
> structure. You MUST have
> Excell on the workstation you run this from. It creates a spreadsheet
> with multiple tabs - each
> tab is a sub-OU that contains users.
>
> ------------------------Start Script--------------------------------
> ' CreateUserList3.vbs
> ' VBScript program to create a Microsoft Excel spreadsheet documenting
> ' all users in the domain.
> '
> ' ----------------------------------------------------------------------
> ' Copyright (c) 2002 Richard L. Mueller
> ' Version 1.0 - November 12, 2002
> ' Version 1.1 - February 19, 2003 - Standardize Hungarian notation.
> ' This program enumerates all users in the domain and writes each user's
> ' LDAP DistinguishedName to a Microsoft Excel spreadsheet.
> '
> ' You have a royalty-free right to use, modify, reproduce, and
> ' distribute this script file in any way you find useful, provided that
> ' you agree that the copyright owner above has no warranty, obligations,
> ' or liability for such use.
> ' Rewritten by Randy Reimers to add fields and save to Excel
>
> 'Option Explicit
>
> Dim strExcelPath, oDomain, oContainer, usr, usrDN
> Dim objRecordSet, strDN, objExcel, objSheet, oObj, sClass, subPath,
> NTDomain
> Dim intIndex, k, sheet, strSheet, objWorkBook, i, objConnection,
> objCommand
> Dim strFilter, strValue, strQuery, strRemote
>
> k = 2
> sheet = 1
> strFilter = "(&(objectCategory=person)(objectClass=user))"
> strValue = ";DistinguishedName,Name"
>
> ' Spreadsheet file to be created.
> strExcelPath = "C:\UserListCC.xls"
>
> ' Bind to Excel object.
> Set objExcel = CreateObject("Excel.Application")
> Set objWorkBook = objExcel.Workbooks.Add 'new
> If objWorkBook.Sheets.Count < 10 then
> For i = objWorkBook.Sheets.Count to 12
> objWorkBook.Sheets.Add()
> Next
> End if
> Set oDomain = GetObject("LDAP://OU=MyCo,OU=BigCo,dc=corporate,dc=inet")
>
> ' Use ADO to search the domain for all users.
> Set objConnection = CreateObject("ADODB.Connection")
> Set objCommand = CreateObject("ADODB.Command")
> objConnection.Provider = "ADsDSOOBject"
> objConnection.Open "Active Directory Provider"
> Set objCommand.ActiveConnection = objConnection
>
> EnumOU(oDomain)
>
> ' Save the spreadsheet.
> objExcel.ActiveWorkbook.SaveAs strExcelPath
> objExcel.ActiveWorkbook.Close
>
> ' Quit Excel.
> objExcel.Application.Quit
>
> ' Clean up.
> Set objRecordSet = Nothing
> Set objSheet = Nothing
> Set objExcel = Nothing
>
> MsgBox "Done"
>
> Wscript.Quit
>
> Function EnumOU(oContainer)
> For Each oObj In oContainer
> For Each sClass in oObj.ObjectClass
> If LCase(sClass) = "organizationalunit" Then
> subPath=oObj.AdsPath
> ' wscript.echo subPath
> Call newSheet(subPath, sheet)
> EnumOUT(subPath)
> sheet = sheet + 1
> If k = 2 Then
> sheet = sheet - 1
> End If
> k = 2
> End If
> Next
> Next
> End Function
>
> Function EnumOUT(oContainer)
>
> strQuery = "<" & oContainer & ">;" & strFilter & strValue & ";subtree"
>
> objCommand.CommandText = strQuery
> objCommand.Properties("Page Size") = 500
> objCommand.Properties("Timeout") = 30
> objCommand.Properties("Cache Results") = False
> objCommand.Properties("Sort on") = "Name"
>
> Set objRecordSet = objCommand.Execute
> Do Until objRecordSet.EOF
>
> usrDN = ""
>
> On Error Resume Next
>
> usrDN = objRecordSet.Fields("distinguishedName")
> Set objItem = GetObject("LDAP://" & usrDN)
> intIndex = InStr(usrDN, "OU=")
> usrDN = Mid(usrDN, intIndex)
>
> objSheet.Cells(k, 1).Value = objItem.cn
> objSheet.Cells(k, 2).Value = objItem.sAMAccountName
> objSheet.Cells(k, 3).Value = objItem.ScriptPath
> objSheet.Cells(k, 4).Value = usrDN
> objSheet.Cells(k, 5).Value = objItem.Description
> objSheet.Cells(k, 6).Value = objItem.WhenChanged
> objSheet.Cells(k, 7).Value = objItem.WhenCreated
> If objItem.TerminalServicesProfilePath <> "" Then
> objSheet.Cells(k, 8).Value = objItem.TerminalServicesProfilePath
> objSheet.Cells(k, 9).Value = objItem.TerminalServicesHomeDrive & " = " &
> objItem.TerminalServicesHomeDirectory
> End If
>
> objSheet.Cells(k, 10).Value = objItem.PhysicalDeliveryOfficeName
> objSheet.Cells(k, 11).Value = objItem.StreetAddress
> objSheet.Cells(k, 12).Value = objItem.L
> objSheet.Cells(k, 13).Value = objItem.St
> objSheet.Cells(k, 14).Value = objItem.PostalCode
> objSheet.Cells(k, 15).Value = objItem.TelephoneNumber
> objSheet.Cells(k, 16).Value = objItem.Title
> objSheet.Cells(k, 17).Value = objItem.Department
> objSheet.Cells(k, 18).Value = objItem.Company
>
> 'Put Remote Control code into readable form
> Select Case objItem.EnableRemoteControl
> Case 0
> strRemote = "DISABLED"
> Case 1
> strRemote = "Ask, Control"
> Case 2
> strRemote = "No Ask, Control"
> Case 3
> strRemote = "Ask, ViewOnly"
> Case 4
> strRemote = "No Ask, ViewOnly"
> Case Else
> strRemote = "Error"
> End Select
>
> objSheet.Cells(k, 19).Value = strRemote
> objSheet.Cells(k, 20).Value = objItem.MaxDisconnectionTime
> If objItem.MaxDisconnectionTime = 0 then
> objSheet.Cells(k, 20).Value = "No Limit"
> End If
> objSheet.Cells(k, 21).Value = objItem.MaxIdleTime
> If objItem.MaxIdleTime = 0 then
> objSheet.Cells(k, 21).Value = "No Limit"
> End If
> objSheet.Cells(k, 22).Value = objItem.MaxConnectionTime
> If objItem.MaxConnectionTime = 0 then
> objSheet.Cells(k, 22).Value = "No Limit"
> End If
>
> k = k + 1
>
> objRecordSet.MoveNext
> Loop
>
> End Function
>
> ' Enumerate all users. Write each user's information to the
> ' spreadsheet.
>
> Function newSheet(subPath, sheet)
> strSheet = Mid(subPath,11)
> intIndex = InStr(strSheet, "OU=")
> strSheet = Left(strSheet, intIndex - 2)
> ' Bind to worksheet.
> 'wscript.Echo sheet & " " & strSheet
> Set objSheet = objExcel.ActiveWorkbook.Worksheets(sheet)
> objSheet.Name = strSheet
> objSheet.Cells(1, 1).Value = "User Distinguished Name"
> objSheet.Cells(1, 2).Value = "Logon ID"
> objSheet.Cells(1, 3).Value = "Logon Script"
> objSheet.Cells(1, 4).Value = "User's OU"
> objSheet.Cells(1, 5).Value = "Description"
> objSheet.Cells(1, 6).Value = "Changed"
> objSheet.Cells(1, 7).Value = "Created"
> objSheet.Cells(1, 8).Value = "TS Profile Path"
> objSheet.Cells(1, 9).Value = "TS Home Path"
> objSheet.Cells(1, 10).Value = "Office"
> objSheet.Cells(1, 11).Value = "Street"
> objSheet.Cells(1, 12).Value = "City"
> objSheet.Cells(1, 13).Value = "State"
> objSheet.Cells(1, 14).Value = "ZIP"
> objSheet.Cells(1, 15).Value = "Phone"
> objSheet.Cells(1, 16).Value = "Title"
> objSheet.Cells(1, 17).Value = "Department"
> objSheet.Cells(1, 18).Value = "Company"
> objSheet.Cells(1, 19).Value = "Remote Control"
> objSheet.Cells(1, 20).Value = "Max Disc"
> objSheet.Cells(1, 21).Value = "Max Idle"
> objSheet.Cells(1, 22).Value = "Max Live"
>
>
> ' Format the spreadsheet.
> objSheet.Range("A1:D1").Font.Bold = True
> objSheet.Select
> objSheet.Range("A2").Select
> objExcel.ActiveWindow.FreezePanes = True
> objExcel.Columns(1).ColumnWidth = 45
> objExcel.Columns(2).ColumnWidth = 26
> objExcel.Columns(3).ColumnWidth = 15
> objExcel.Columns(4).ColumnWidth = 65
> objExcel.Columns(5).ColumnWidth = 20
> objExcel.Columns(6).ColumnWidth = 15
> objExcel.Columns(7).ColumnWidth = 15
> objExcel.Columns(8).ColumnWidth = 35
> objExcel.Columns(9).ColumnWidth = 25
> objExcel.Columns(10).ColumnWidth = 20
> objExcel.Columns(11).ColumnWidth = 25
> objExcel.Columns(12).ColumnWidth = 15
> objExcel.Columns(15).ColumnWidth = 15
> objExcel.Columns(16).ColumnWidth = 25
> objExcel.Columns(17).ColumnWidth = 20
> objExcel.Columns(18).ColumnWidth = 20
> objExcel.Columns(19).ColumnWidth = 15
>
> End Function
>
> Function MakeTime(timeval)
> MakeTime = Left(TimeVal,2) & ":" & Right(TimeVal,2)
> End Function
> ------------------------End Script-----------------------------------
>
>
>
> "Richard Mueller [MVP]" <rlmueller-nospam[ at ]ameritech.nospam.net> wrote in
> message news:%23Hs2Zy%23wHHA.4640[ at ]TK2MSFTNGP03.phx.gbl...
>> Sylvia wrote:
>>
>>>I am trying to write a script that will give me an Excel Spreadsheet of
>>>all of the users in a particular OU and all of it's sub OU's. I'd like
>>>for the Output to include the Distinguishedname.
>>>
>>> Here's what I have so far. I can't find directions on how to get it to
>>> write to Excel instead of a text box on the screen. (Also, when I run
>>> this I get the person's name to show up, if I change 'name' to
>>> distinguishedName, nothing happens when I run the script. Any
>>> suggestions?
>>>
>>> On Error Resume Next
>>>
>>>
>>> Const ADS_SCOPE_SUBTREE = 2
>>>
>>> Set objConnection = CreateObject("ADODB.Connection")
>>> Set objCommand = CreateObject("ADODB.Command")
>>> objConnection.Provider = "ADsDSOObject"
>>> objConnection.Open "Active Directory Provider"
>>> Set objCommand.ActiveConnection = objConnection
>>>
>>> objCommand.Properties("Page Size") = 99999
>>> objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE
>>>
>>> objCommand.CommandText = _
>>> "SELECT Name FROM 'LDAP://ou=My Ou,dc=MYDOMAIN,dc=COM' WHERE
>>> objectCategory='user'"
>>> Set objRecordSet = objCommand.Execute
>>>
>>> objRecordSet.MoveFirst
>>> Do Until objRecordSet.EOF
>>> Wscript.Echo objRecordSet.Fields("name").Value
>>> objRecordSet.MoveNext
>>> Loop
>>>
>>>
>>
>> First, the maximum page size is 1000. I have an example VBScript program
>> that writes the Distinguised Names of all users to an Excel spreadsheet
>> linked here:
>>
>>
http://www.rlmueller.net/Create%20User%20List%203.htm>>
>> To restrict this to one OU (and any child OU's), change the base of the
>> LDAP syntax query. Instead of:
>>
>> strQuery = "<LDAP://" & strDNSDomain & ">;" & strFilter _
>> & ";distinguishedName;subtree"
>>
>> use something similar to:
>>
>> strQuery = "<LDAP://ou=My OU," & strDNSDomain & ">;" & strFilter _
>> & ";distinguishedName;subtree"
>>
>> or if you prefer:
>>
>> strQuery = "<LDAP://ou=My OU,dc=MyDomain,dc=com>;" & strFilter _
>> & ";distinguishedName;subtree"
>>
>> In your code, the SQL syntax query retrieves the Name attribute. To
>> retrieve Name and distinguishedName, the query would be:
>>
>> objCommand.CommandText = "SELECT Name, distinguishedName " _
>> & "FROM 'LDAP://ou=My Ou,dc=MYDOMAIN,dc=COM' " _
>> & "WHERE objectCategory='person' AND objectClass='user'"
>>
>> Then in the loop where the recordset is enumerated, you could retrieve
>> both objRecordSet.Fields("name").Value and
>> objRecordset.Fields("distinguishedName").Value
>>
>> For more on using ADO to retrieve values for AD, see this link:
>>
>>
http://www.rlmueller.net/ADOSearchTips.htm>>
>> --
>> Richard Mueller
>> Microsoft MVP Scripting and ADSI
>> Hilltop Lab -
http://www.rlmueller.net>> --
>>
>>
>
>