VBScript for Search for all users in Windows Active Directory and export it in to well formatted Excel file

Search for all users in Windows Active Directory and export it in to well formatted  Excel file using VBScript.

If Your boss want all users Email address in excel file from you and your Active Directory having more than 50 to 100 user then it is boring job to type or copy and pest all users details in excel file to do job easy write VBScript file that do what you want within 3 to 5 min. and your boss also happy on your work for quick response. so if  you want t  export active directory user full name and email address in to MS-Excel file copy it into text file and save it as ADUserInExcel.vbs.

Option Explicit
Dim adocommand, adoconnection, strBase, strFilter, strAttributes
Dim objRootDSE, strDNSDomain, strQuery, adoRecordset, strName, strCN , strGivenNAme ,strSN, strmail
Dim objExcel, objwb, objrange, x

Set objExcel = createObject("Excel.Application")
set objwb = objExcel.Workbooks.add
set objwb = objExcel.activeWorkBook.Worksheets(1)
objwb.Name = "User Information "

ObjExcel.Visible = True
objwb.Activate
objwb.Cells(1,1).Value = "First Name"
objwb.Cells(1,2).value = "Last Name"
objwb.Cells(1,3).value = "Dispaly Name"
objwb.Cells(1,4).value = "Login Name"
objwb.Cells(1,5).value = "Email Address"
set objrange = objExcel.Range("A1","E1")
objRange.Interior.ColorIndex = 15
objRange.font.Bold = True

x= 2


'Setup ADO Objects.
Set adocommand = CreateObject("ADODB.Command")
Set adoConnection = CreateObject("ADODB.Connection")
adoConnection.Provider = "ADsDSOobject"
adoConnection.Open "Active Directory Provider"
Set adoCommand.ActiveConnection = adoConnection

' Search entire Active Directory Domain
Set objRootDSE = GetObject("LDAP://RootDSE")

strDNSDomain = objRootDSE.Get("defaultNamingContext")
strBase = "<LDAP://" & strDNSDomain & ">"

' Filter on user objects.
strFilter = "(&(objectCategory=person)(objectClass=user))"

'Comma delimited list of attribute values to retrieve.
strAttributes = "sAMAccountName,cn,givenName,sn,mail"

'Constuct the LDAP syntax query
strQuery = strBase & ";" & strFilter & ";" & strAttributes & ";subtree"
adocommand.commandText = strQuery
adoCommand.Properties("Page Size") = 100
adoCommand.Properties("TimeOut") = 30
adoCommand.properties("Cache Results") = False

' Run the query

Set adoRecordset = adoCommand.Execute

' Enumerate the resulting recordset.
Do Until adoRecordset.EOF
  ' Retrieve values and display.
   strName = adoRecordset.Fields("sAMAccountName").Value
   strCN = adoRecordset.Fields("cn").Value
   strGivenName = adoRecordset.Fields("givenName")
   strSN = adoRecordset.Fields("sn").Value
   strmail = adoRecordset.Fields("mail").Value
  
   ' Write data in Excel file

   objwb.Cells(x,1).Value = strGivenName
   objwb.Cells(x,2).value = strSN
   objwb.Cells(x,3).value = strCN
   objwb.Cells(x,4).value = strName
   objwb.Cells(x,5).value = strmail

   'Move to the Next Record in the recordset.
   adoRecordSet.MoveNext
   x = x + 1
Loop

'autofit the output
 Set objRange = objwb.UsedRange
 objRange.EntireColumn.Autofit()


'Clean up.
adoRecordSet.Close
adoConnection.Close

' Display Massage All Done

MsgBox "Done"

It will open excel file and write all user information in to excel file the format it. Time Saving Job.:-)