Code:
Const ADS_SCOPE_SUBTREE = 2
Const xlAscending = 1
Const xlDescending = 2
Const xlYes = 1
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
Set objWorkbook = objExcel.Workbooks.Add
Set objWorksheet = objWorkbook.Worksheets(1)
' Make bold
objExcel.Range("A1:N1").Select
objExcel.Selection.Font.Bold = True
' Name Columns
objExcel.Cells(1, 1).Value = "Employee ID"
objExcel.Cells(1, 2).Value = "First Name"
objExcel.Cells(1, 3).Value = "Middle Initial"
objExcel.Cells(1, 4).Value = "Last Name"
objExcel.Cells(1, 5).Value = "Full Name"
objExcel.Cells(1, 6).Value = "Description"
objExcel.Cells(1, 7).Value = "Job Title"
objExcel.Cells(1, 8).Value = "NT Login ID"
objExcel.Cells(1, 9).Value = "Email"
objExcel.Cells(1, 10).Value = "Office Phone"
objExcel.Cells(1, 11).Value = "Cell Phone"
objExcel.Cells(1, 12).Value = "Department Name"
objExcel.Cells(1, 13).Value = "Company name"
objExcel.Cells(1, 14).Value = "Manager"
' Use ADO to search Active Directory
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") = 100
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE
objCommand.CommandText = _
"SELECT displayName, samAccountName, EmployeeID, givenName, initials, sn, Description, title, mail, department, physicalDeliveryOfficeName, Manager, telephoneNumber, msExchHomeServerName, homeMDB, MOBILE, distinguishedName, Title, Company FROM " _
& "'LDAP://dc=na,dc=calpine,dc=com' WHERE " _
& "objectCategory='user'"
Set objRecordSet = objCommand.Execute
objRecordSet.MoveFirst
x = 2
' Pull Data
Do Until objRecordSet.EOF
objExcel.Cells(x, 1).Value = _
objRecordSet.Fields("EmployeeID").Value
objExcel.Cells(x, 2).Value = _
objRecordSet.Fields("givenName").Value
objExcel.Cells(x, 3).Value = _
objRecordSet.Fields("initials").Value
objExcel.Cells(x, 4).Value = _
objRecordSet.Fields("sn").Value
objExcel.Cells(x, 5).Value = _
objRecordSet.Fields("displayName").Value
objExcel.Cells(x, 6).Value = _
objRecordSet.Fields("Description").Value
objExcel.Cells(x, 7).Value = _
objRecordSet.Fields("title").Value
objExcel.Cells(x, 8).Value = _
objRecordSet.Fields("samAccountName").Value
objExcel.Cells(x, 9).Value = _
objRecordSet.Fields("mail").Value
objExcel.Cells(x, 10).Value = _
objRecordSet.Fields("telephoneNumber").Value
objExcel.Cells(x, 11).Value = _
objRecordSet.Fields("mobile").Value
objExcel.Cells(x, 12).Value = _
objRecordSet.Fields("department").Value
objExcel.Cells(x, 13).Value = _
objRecordSet.Fields("Company").Value
objExcel.Cells(x, 14).Value = _
objRecordset.Fields("Manager").Value
x = x + 1
objRecordSet.MoveNext
Loop
objExcel.Visible = True
Set objRange = objExcel.Range("A1:N1")
objRange.Activate
Set objRange = objExcel.Selection.EntireColumn
objRange.Autofit()
' Auto Sort
Set objRange = objWorksheet.UsedRange
Set objRange2 = objExcel.Range("A1")
objRange.Sort objRange2, xlDescending, , , , , , xlYes
' Clean up.
Set objRootDSE = Nothing
Set objCommand = Nothing
Set objConnection = Nothing
Set objRecordSet = Nothing
Bookmarks