Creating User accounts from Excel 1

[cdtech9198]

Hello All,

I have successfully written a Script that reads columns from an excel sheet and creates user account.

One problem I am running into is when a particular cell is blank.

For example, if I am missing the GivenName of a particular account, the script craps out. When I add "On Error Resume Next" the script continues but that account is never created.

Is there a way around this?

Column 1 = DisplayName
Column 2 = GivenName
Column 3 = userId
Column 4 = Description
Column 5 = SurName

My script

On Error Resume Next

--------------------------------------------------------'

strOU = "OU=TESTOU ," 
strSheet = "C:\test.xls"
strPWD = "P@ssword!"
' Bind to Active Directory, Users container.
Set objRootLDAP = GetObject("LDAP://rootDSE")
Set objContainer = GetObject("LDAP://" & strOU & _
objRootLDAP.Get("defaultNamingContext"))

' Open the Excel spreadsheet
Set objExcel = CreateObject("Excel.Application")
Set objSpread = objExcel.Workbooks.Open(strSheet)
intRow = 2 'Skip row for headings


Do Until objExcel.Cells(intRow,1).Value = ""
   strCN = Trim(objExcel.Cells(intRow, 1).Value)
   strFirst = Trim(objExcel.Cells(intRow, 2).Value)
   strSam = Trim(objExcel.Cells(intRow, 3).Value)
   strDscpt = Trim(objExcel.Cells(intRow, 4).Value)
   strLast = Trim(objExcel.Cells(intRow, 5).Value)
   


'Principal Name
strPrin =  strSam & "@" & "foo.net"
'New Container Name and display
strCNnew = "SMB_" & strCN


 'Created container name as SMB_
  Set objUser = objContainer.Create("User", "cn=" & strCNnew)

  
   objUser.userPrincipalName = strPrin
   objUser.sAMAccountName = strSam
   objUser.givenName = strFirst
   objUser.sn = strLast
   objUser.displayName = strCNnew
   objUser.Description = strDscpt
   objUser.SetInfo

   ' enable account with password
   objUser.userAccountControl = 512
   objUser.pwdLastSet = 0
   objUser.SetPassword strPWD
   objUser.SetInfo

    'Pw set to not expire
	Const ADS_UF_DONT_EXPIRE_PASSWD = &H10000
	lngFlag = objUser.userAccountControl
	lngFlag = lngFlag Or ADS_UF_DONT_EXPIRE_PASSWD
	objUser.userAccountControl = lngFlag
	objUser.SetInfo

intRow = intRow + 1
Loop

objExcel.Quit
WScript.Quit

 

[PHV]

Replace this:
objUser.givenName = strFirst
with something like this:
If Trim(strFirst & "") = "" Then
objUser.givenName = "whatever default value you want here"
Else
objUser.givenName = strFirst
End If

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cdtech9198]

Thank you PHV

Would I be able to leave the value blank.

For example:
If Trim(strFirst & "") = "" Then
objUser.givenName = " "
Else
objUser.givenName = strFirst
End If

 

[markdmac]

Yes you can do that.

I hope you find this post helpful.

Regards,

Mark

Check out my scripting solutions at

http://www.thespidersparlor.com/vbscript

Work SMARTER not HARDER. The Spider's Parlor's Admin Script Pack is a collection of Administrative scripts designed to make IT Administration easier! Save time, get more work done, get the Admin Script Pack.

 

[cdtech9198]

Thanks Mark. i should have responded with the answer.

I attemped to use this

If Trim(strFirst & "") = "" Then
  objUser.givenName = " "
Else
  objUser.givenName = strFirst
End If

However the script cut out everytime there was a blank cell. I ended up using this:

   sFirstName = ""
   sFirstName = Replace(strFirst, Chr(34), "")

                
  If sFirstName <> "" Then
     objUser.Put "givenName", sFirstName
     objUser.SetInfo
  End If

I have no clue why the first example did not work.

 

[markdmac]

If Len(strFirst) = 0 Then
  'Don't bother to set anything
Else
  objUser.givenName = strFirst
End If

I hope you find this post helpful.

Regards,

Mark

Check out my scripting solutions at

http://www.thespidersparlor.com/vbscript

Work SMARTER not HARDER. The Spider's Parlor's Admin Script Pack is a collection of Administrative scripts designed to make IT Administration easier! Save time, get more work done, get the Admin Script Pack.