Re: Creating AD users from Excel help

With this script, how will the script know which Cell has what information?

"Richard Mueller" wrote:

A Slater wrote:

I found this script on the internet to import users from an excel
spread***, and using LDAP, create accounts in AD. The script works fine
if
all the cells in the spread*** contain some type of data. However, in
the
spead*** that I need to use, there are a lot of blank fields. I realize
that the problem lies with the line;

Do Until objExcel.Cells(intRow,1).Value = ""

but I don't know how to modify it enough to allow the script to pass over
blank cells and not stop. I've tried adding a ~ as the value for the above
line and made that the last entry in the excel spread***, but to no
avail.
Any help would be greatly appriciated.

Andy


Option Explicit
Dim objRootLDAP, objContainer, objUser, objShell
Dim objExcel, objSpread, intRow
Dim strUser, strOU, str***
Dim strCN, strSam, strFirst, strInit, strLast, strDisp, strPWD, strUPN,
strMail, strDesc


strOU = "OU=ANDYTEST ,"
str*** = "C:\ADImport.xls"

Set objRootLDAP = GetObject("LDAP://rootDSE";)
Set objContainer = GetObject("LDAP://"; & strOU & _
objRootLDAP.Get("defaultNamingContext"))

Set objExcel = CreateObject("Excel.Application")
Set objSpread = objExcel.Workbooks.Open(str***)
intRow = 3

Do Until objExcel.Cells(intRow,1).Value = ""
strSam = Trim(objExcel.Cells(intRow, 1).Value)
strCN = Trim(objExcel.Cells(intRow, 2).Value)
strFirst = Trim(objExcel.Cells(intRow, 3).Value)
strInit = Trim(objExcel.Cells(intRow, 4).Value)
strLast = Trim(objExcel.Cells(intRow, 5).Value)
strDisp = Trim(objExcel.Cells(intRow, 6).Value)
strUPN = Trim(objExcel.Cells(intRow, 7).Value)
strPWD = Trim(objExcel.Cells(intRow, 8).Value)
strDesc = Trim(objExcel.Cells(intRow, 9).Value)
strMail = Trim(objExcel.Cells(intRow, 10).Value)

Set objUser = objContainer.Create("User", "cn=" & strCN)
objUser.sAMAccountName = strSam
objUser.givenName = strFirst
objUser.initials = strInit
objUser.sn = strLast
objUser.displayName = strDisp
objUser.userPrincipalName = strUPN
objUser.description = strDesc
objUser.mail = strMail
objUser.SetInfo

objUser.userAccountControl = 512
objUser.pwdLastSet = 0
objUser.SetPassword strPWD
objUser.SetInfo

intRow = intRow + 1
Loop
objExcel.Quit


WScript.Quit

Hi,

The program needs to know when it has read the last row of the spread***,
or it will never stop. Generally, at least one column is required (never
blank). This doesn't have to be the first column. In the above, where you
are creating users, the value for sAMAccountName in the first column is a
mandatory attribute, so it cannot be blank. The first row encountered with a
blank value for sAMAccountName can be assumed to be at the end. The only
other required column is the second, for cn. The statement:

Do Until objExcel.Cells(intRow,1).Value = ""

is fine in this case. If a column can be blank, you should test for this.
You cannot assing a blank string to the attribute. If the column is blank,
you should not assign any value. You could use code similar to this:

If (strFirst <> "") Then
objUser.givenName = strFirst
End If

Do this for every column that can be missing. For example, if the password
can be missing, use:

If (strPwd <> "") Then
objUser.SetPassword strPwd
End If

Finally, it is recommended that you not assign values directly to
userAccountControl. It is recommended that you turn individual bits of the
attribute on or off using bit masks and the Or or Xor operators. In this
case, 512 is the value assigned by the system to normal user accounts when
they are created. The following statement can be omitted:

objUser.userAccountControl = 512

--
Richard
Microsoft MVP Scripting and ADSI
Hilltop Lab - http://www.rlmueller.net



.