Search user in Active Directory with First and Last Name and export user information in to excel file using VBScript.

Search user in Active Directory with First and Last Name and export user information in to excel File using VBScript.

Now we will continue our script but instead of display results we export that in to the well formatted Excel File.We use same script and make some changes to allow it to write the excel file.

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"