I need a script wich is going in a loop and reads in data from an exce

[sx3]

I am trying to modify active Directory user’s expiry date using the following script

strExpireDate = "23/10/2007"
strUserDN = "cn=testkm,ou=test km laptops,DC=corp,DC=int,DC=mydomain,DC=gov,DC=uk"

set objUser = GetObject("LDAP://" & strUserDN)
objUser.AccountExpirationDate = strExpireDate
objUser.SetInfo
WScript.Echo "Set user " & strUserDN & " to expire on " & strExpireDate

The above works but it's only for one user. The problem is that I have a bunch of users in different ou's that need changing.

Is there a way to use an excel document which has the users names/usernames and use that as a source?

Any help will be appreciated before I throw my pc out of the window.

 

[Sheco]

A text file would be better than an excel file but perhaps the best way would be to make a group in your AD and put the users you want to set in that group... that way you could alter your LDAP query to get the group members.

 

[EBGreen]

searching this forum will return several examples of using excel as a data source.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[mrmovie]

i agree with Sheco, excel is cool if you have some fit ladies in the accounts department you want to impress but i would say delimited plain text file,,,,or better still go for an xml file

 

[markdmac]

One reqason to use Excel would be to be able to provide status reporting in an organized method, so I would stay with Excel. You will find info on how to do this in my FAQ faq329-4871.

I hope you find this post helpful.

Regards,

Mark

 

[sx3]

Thank you everey one for your input. I have explored markdmac FAQ but may be too complex to understand in a day. If the script works using a txt file then I am open to any suggestions.

 

[mrmovie]

do you want to change all users in AD properties???

if you want to read a file then...

Set objTs = FSO.OpenTextFile(...

Do While Not objTS.AtEndOfStream
strTemp = ""
strTemp = objTS.ReadLine
....

Loop

 

[sx3]

I have managed to get a script that will do the changes that I require using an Excel sreadsheet. Here it is for what it's worth and thanks all for your input.

'This script wiil use kalabmiis.xls as a source file
'Please note this has only been tested on lbbvsmiis02 server which has a test exchange server fully pupulated.

Option Explicit

Dim objExcel, strExcelPath, objSheet, intRow, strUserDN, strFirstName
Dim strexpire, strLastName, strdepartment, strextensionAttribute3, strextensionAttribute4, objUser

' Bind to Excel object.
On Error Resume Next
Set objExcel = CreateObject("Excel.Application")
If Err.Number <> 0 Then
On Error GoTo 0
Wscript.Echo "Excel application not found."
Wscript.Quit
End If
On Error GoTo 0

strExcelPath = "c:\scripts\kalabmiis.xls"

' Open specified spreadsheet and select the first worksheet.
objExcel.WorkBooks.Open strExcelPath
Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)

' Iterate through the rows of the spreadsheet after the first, until the
' first blank entry in the first column. For each row, bind to the user
' specified in the first column and set attributes.
intRow = 2
Do While objSheet.Cells(intRow, 1).Value <> ""
strUserDN = objSheet.Cells(intRow, 1).Value
strFirstName = objSheet.Cells(intRow, 2).Value
strexpire = objSheet.Cells(intRow, 3).Value
strLastName = objSheet.Cells(intRow, 4).Value
strdepartment = objSheet.Cells(intRow, 5).Value
strextensionAttribute3 = objSheet.Cells(intRow, 6).Value
strextensionAttribute4 = objSheet.Cells(intRow, 7).Value
On Error Resume Next
Set objUser = GetObject("LDAP://" & strUserDN)
If Err.Number <> 0 Then
On Error GoTo 0
Wscript.Echo "User NOT found" & vbCrLf & strUserDN
Else
On Error GoTo 0
objUser.givenName = strFirstName
objUser.AccountExpirationDate = strexpire
objUser.sn = strLastName
objuser.department = strdepartment
objuser.extensionAttribute3 = strextensionAttribute3
objuser.extensionAttribute4 = strextensionAttribute4
On Error Resume Next
objUser.SetInfo
If Err.Number <> 0 Then
On Error GoTo 0
Wscript.Echo "Unable to update user" & vbCrLf & strUserDN
End If
On Error GoTo 0
End If
intRow = intRow + 1
Loop

' Close workbook and quit Excel.
objExcel.ActiveWorkbook.Close
objExcel.Application.Quit

' Clean up.
Set objExcel = Nothing
Set objSheet = Nothing
Set objUser = Nothing

Wscript.Echo "Done at last"