a Script for Changing user properties from Excel

Hi Guys,

One of my clients asked me to write him a script that updates user properties in Active Directory, while taking the data out of an Excel file.
Basicly, the script opens an Excel file, and read’s the cell located at index (2,1).
On the Excel it self, I wrote the users sAMAccountName on the first Cullomn.
The script read’s the sAMAccountName, and get’s that user from the Directory.
The rest is pretty simple, so here you go:
On Error Resume Next
‘ Creating object to connect to AD
Set objConnection = CreateObject(“ADODB.Connection”)
Set objCommand = CreateObject(“ADODB.Command”)
objConnection.Provider = “ADsDSOObject”
objConnection.Open “Active Directory Provider”
Set objCommand.ActiveConnection = objConnection
‘ Setting the scope size
objCommand.Properties(“Page Size”) = 1000
objCommand.Properties(“Searchscope”) = ADS_SCOPE_SUBTREE 
‘ Creating object to work with Excel Data Sheet
Set objExcel = CreateObject(“Excel.Application”)
Set objWorkbook = objExcel.Workbooks.Open(“FILENAME”)
objExcel.Visible = True
‘ Creating an index Variable.
‘ This index variable will be used to read lines from the Excel Data Sheet
i = 2
‘ Running on all the lines in the Excell
Do Until objExcel.Cells(i, 1).Value = “”
 ‘ Getting the first user name from the Excel
 strName = objExcel.Cells(i,1)
 ‘ Getting the user object from the directory
 objCommand.CommandText = _
        “SELECT * FROM ‘LDAP://dc=DOMAIN’ WHERE objectCategory=’user’ ” & _
            “AND samAccountName='” & strName & “‘”
 Set objRecordSet = objCommand.Execute
 ‘ Creating a user object
 strUser = objRecordSet.Fields(“ADsPath”).Value
 Set objUser =  GetObject(strUser)
‘ Getting the values from the Excel in putting them to Variables
‘ I wrote here some most common users attributes, but you can add/remove them as you like.
 strGivenName = objExcel.Cells(i,2)
 strSn = objExcel.Cells(i,3)
 strStreetAddress = objExcel.Cells(i,4)
 strL = objExcel.Cells(i,5)
 strCo = objExcel.Cells(i,6)
 strPostalCode = objExcel.Cells(i,7)
 strMobile = objExcel.Cells(i,8)
 strTitle = objExcel.Cells(i,9)
‘ Setting the user object with the values from the variables set earlier
‘ Notice that im changing the value of PostalCode and Mobile to number, as the attribute
‘ in Active Directory has to be a number and not a string.
‘ You can find out more about the ‘FormatNumber’ function at this link.
 objUser.givenname = strGivenName
 objUser.sn = strSn
 objUser.streetAddress = strStreetAddress
 objUser.l = strL
 objUser.co = strCo
 objUser.postalCode = FormatNumber(strPostalCode,0,0,0,0)
 objUser.mobile = “0” & FormatNumber(strMobile,0,0,0,0)
 objUser.title = strTitle
 ‘ Executing the command – putting the values to the Directory
‘ Changing the Excel to show what user were changed
‘ I have added a collumn in the Excel to show what users were changed.
‘ if for instance a user is not found, then the script will right “Not Found” in that collumn
 If objRecordset.RecordCount = 1 Then
  objExcel.Cells(i,11) = “Found”
  objExcel.Cells(i,11) = “Not found”
 End If
 ‘ Increasing the index to move to the next line in the excel
    i = i + 1

What do you think about this post?