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
Const ADS_SCOPE_SUBTREE = 2
‘ 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”
‘ Increasing the index to move to the next line in the excel
i = i + 1