Help in debugging a script that adds uses to AD 1

[bnymk]

Hello All:

Our client just sent us a huge list of users in an Excel spreadsheet that they want us to add to an Active Directory so they can view a website that we have created for them using SharePoint portal. I was looking for a script and thanks to some of you guys here on this forum, I found one which I modified a little bit to work. I tested this script with a small numbers of users (around 50) in a spreadsheet and it adds all of them to the AD. But when I tried to use the original spreadsheet that has thousands of records, it fails and gives me an error after only inserting 100 records into the AD. And the erorr that I get is all the time different but mostly the content of the error is as follows with the line number different.
"A device attached to the system is not function. Code: 8007001f Source: (null)"

The line number where the error allegedly occurs is always different.

Can someone help.

thanks.

code below.


'Parent container of new user
Dim oContainer

'created user
Dim oUser

'Set the file type and location where we are going to get the data from
set x = getobject(,"excel.application")
Set objWorkbook = x.Workbooks.Open("D:\Users\new_active_dir.xls")

'Start from the second row of the spreadsheet
r = 2

Const ou_name = "Allusers"

do until len(x.cells(r, 1).value) = 0

first_name = x.cells(r, 1).value
last_name = x.cells(r, 2).value
title = x.cells(r, 3).value
phone = x.cells(r, 6).value
login = x.cells(r, 7).value
email = x.cells(r, 7).value
dmisId = x.cells(r, 10).value
dmisName = x.cells(r, 12).value
firstName = replace(first_name, " ","")
pswString = LCase(firstName)
pswdtxt = "!BLA" & pswString & "4me"


'Set the path to the appropriate network.
set oContainer = GetObject("LDAP://OU=NEW_Users,OU=Allusers,DC=company,DC=com")

'Add users data

fullName = first_name & " " & last_name

Set oUser = oContainer.Create("User","CN=" & fullName)
oUser.Put "samAccountName", LCase(firstName) & "." & LCase(last_name)
oUser.SetInfo
oUser.Put "userPrincipalName", email
oUser.SetInfo
oUser.Put "displayName", fullName
oUser.SetInfo
oUser.Put "givenName", first_name
oUser.SetInfo
oUser.Put "mail", email
oUser.SetInfo
oUser.Put "sn", last_name
oUser.SetInfo
oUser.Put "telephoneNumber", phone
oUser.SetInfo
oUser.Put "physicalDeliveryOfficeName", dmisId & " - " & dmisName
oUser.SetInfo
oUser.PUt "description", "New User"
oUser.SetInfo
oUser.SetPassword pswdtxt
oUser.SetInfo
oUser.AccountDisabled = False
oUser.SetInfo

'If there is an error then show error otherwise display confirmation texts in the assigned cells
If Err.Number <> 0 And Err.Number <> -2147019886 Then
x.cells(r, 17).value = err.number & ": " & "ID creation error"
Else

x.cells(r, 14).value = "created"
x.cells(r, 15).value = pswdtxt
x.cells(r, 16).value = LCase(firstName) & "." & LCase(last_name)
end if


r = r + 1


set objOU = Nothing
set oUser = Nothing
set o = nothing
Err.Clear

'Add the users to a particular group
set grp = GetObject("LDAP://CN=NewUsersGroup,OU=UserGroups,OU=AllUsers,DC=company,DC=com")
grp.Add("LDAP://CN="&fullName&",OU=NEW_Users,OU=AllUsers,DC=company,DC=com")
grp.SetInfo

Set grp=Nothing
Loop


set x = nothing

msgbox "Successfully added to Active Directory"


"Behind every great fortune there lies a great crime", Honore De Balzac

 

[tsuji]

Hello bnymk,

There are many problems with the script as presented.
[1] I do not see "on error resume next" strategically placed, so your if err.number<>o etc is never effective.
[2] You use the existing instance of excel.application. In this case, it is necessary to provision alternative in case there isn't one.
[3] You use object x to read the cell value. It can work. But, it is better to use specific worksheet object to do it.
[4] You have hard coded ou_name, and other group name and new_ users ou. That is fine. Only that the const is under-utilized and served no purpose.
[5] Your if err.number<>0 include a specific error number as another criterion. It should not. Should just write the err.number into the sheet and you can identify what it is from there. Else other type of error will not be recorded.
[6] Those objOU and o are not defined in the maybe excerpt.

There are subtle points as to where to place on error resume next and where to not. It is too boring to describe in word. Here is my revision as I go along cut and paste. Watch typos etc. There are places where I prefer not to let error handled gracefully because it relates to system not well prepared. In a word, my use of On Error Resume Next is never a blank cheque. I use as much as I could your lines. You might prefer to keep some of the logic you're more comfortable with, the revision then illustrates the idea you have to take care of.

'Parent container of new user
Dim oContainer 

'created user
Dim oUser 

'Set the file type and location where we are going to get the data from
on error resume next
set x = getobject(,"excel.application")
if err.number<>0 then
	set x = createobject("excel.application")
	err.clear
end if

set objWorkbook = x.Workbooks.Open("D:\Users\new_active_dir.xls")

if err.number<>0
	wscript.echo "Vital file not found. Operation aborted."
	x.quit : set x=nothing : wscript.quit
end if

set y = objWorkbook.activesheet
'Start from the second row of the spreadsheet
r = 2

Const ou_name = "Allusers"	'you are not using it though.

'Set the path to the appropriate network.
set oContainer = GetObject("LDAP://OU=NEW_Users,OU=Allusers,DC=company,DC=com")
set grp = GetObject("LDAP://CN=NewUsersGroup,OU=UserGroups,OU=AllUsers,DC=company,DC=com")

do until len(y.cells(r, 1).value) = 0

	with y
		first_name = .cells(r, 1).value    
		last_name  = .cells(r, 2).value
		title      = .cells(r, 3).value
		phone      = .cells(r, 6).value
		login      = .cells(r, 7).value    
		email      = .cells(r, 7).value
		dmisId     = .cells(r, 10).value
		dmisName   = .cells(r, 12).value
	end with
    firstName  = replace(first_name, " ","")
    pswString  = LCase(firstName)
    pswdtxt    = "!BLA" & pswString & "4me"
   
    'Add users data
	fullName = first_name & " " & last_name
    
	on error resume next
	Set oUser = oContainer.Create("User","CN=" & fullName)
    oUser.Put "samAccountName", LCase(firstName) & "." & LCase(last_name)
    oUser.SetInfo
	If Err.Number <> 0 And Err.Number <> -2147019886 Then
		'If there is an error then show error otherwise display confirmation texts in the assigned cells
        x.cells(r, 17).value = err.number & ":  " & "ID creation error"
		err.clear
	Else
		with oUser
			.Put "userPrincipalName", email
			.Put "displayName", fullName
			.Put "givenName", first_name
			.Put "mail", email
			.Put "sn", last_name
			.Put "telephoneNumber", phone
			.Put "physicalDeliveryOfficeName", dmisId & " - " & dmisName
			.Put "description", "New User"
			.SetInfo
			.SetPassword pswdtxt
			.AccountDisabled = False
			.SetInfo
		end with
		with y
			.cells(r, 14).value = "created"
			.cells(r, 15).value = pswdtxt
			.cells(r, 16).value = LCase(firstName) & "." & LCase(last_name)
		end with
		'Add the users to a particular group, here control can be tightened in the future
		grp.Add oUser.adspath
		grp.SetInfo
	End if
	set oUser=nothing
	on error goto 0
	r = r + 1
Loop
Set grp = Nothing
Set oContainer = nothing

'tidying up xls
set y=nothing
objWorkbook.save
objWorkbook.close
set objWorkbook = nothing

'x.quit	'you might not want this
set x = nothing

msgbox "Successfully added to Active Directory"

regards - tsuji

 

[tsuji]

Further notes:

I forgot to place an intended on error goto 0 just above the set y=...
[tt]
'...etc
[green]on error goto 0[/green]
set y = objWorkbook.activesheet
'...etc
[/tt]
I intended to let script error out in case your group and/or ou have not been set up correctly.

- tsuji

 

[tsuji]

Further Notes (II):

With my temptated revised script as it stands now, I can see two issues, inherited from the original, you have to make good.

[A] If the user (samaccountname) already exists in the ou, or that the same script is repeatedly executed for what ever reason, you have to delete the existing copy before creating a clean child object.

Similarly, the group membership of ouser.

- tsuji

 

[bnymk]

Thank you very much tsuji. All the points that you mentioned makes perfect sense. I was in rush to get something working and the fact that I didn't have any experience in writing a script like this before didn't help my cause either. Anyway, once again, thank you and I appreciate your help and ideas.

"Behind every great fortune there lies a great crime", Honore De Balzac

 

[martyh]

Just out of interest, since you had the users in a spreadsheet anyway, why not use dsadd from the w2k resource kit? You can set up all the info in the spreadsheet to create the command lines and then paste them into a batchfile.

Works for me.

Marty
Network Admin
Hilliard Schools

 

[mrmovie]

i would say learning how to manipulate AD from a scripting language leads to an infinite number of possible solutions/utils. if you stick with what the resource kit gives you are restricting your knowledge and your ability to solve a problem