Tim Mintner

Greetings everyone!  In my last article I showed you how to give your wizard-clicking hand some rest by using the magical 4 lines of code to create user accounts.  While it may be awful neat to be able to create a user account named myerken in the management OU of the fictitious Fabrikam.com domain, it's not terribly practical and truly not the Lazy Administrator way of creating accounts.

In this article, I am going to expand on those magical 4 lines and give you 10 additional lines of code that will allow you to create 1000's of accounts in just a few seconds.  In this script we are going to learn how to read a list of users  from an Excel file and create user accounts from that list. 

Like in my previous articles, I am going be a true Lazy Administrator and copy a script that again came straight from Microsoft.

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\Scripts\New_users.xls")
intRow = 2
Do Until objExcel.Cells(intRow,1).Value = ""
    Set objOU = GetObject("ou=Finance, dc=fabrikam, dc=com")
    Set objUser = objOU.Create("User", "cn=" & objExcel.Cells(intRow, 1).Value)
    objUser.sAMAccountName = objExcel.Cells(intRow, 2).Value
    objUser.GivenName = objExcel.Cells(intRow, 3).Value
    objUser.SN = objExcel.Cells(intRow, 4).Value
    objUser.AccountDisabled = FALSE
    objUser.SetInfo
    intRow = intRow + 1
Loop
objExcel.Quit

WOW!  Isn't that just a thing of beauty.  My right-click finger is already thanking me. 

If you take away the first four lines and the last three lines, this script looks awfully familiar.  In fact it is practically the same script as my previous article!  I know there are some slight differences, we have all of these references to Excel instead of all of myerken's precious information in the script.  So let's see how these 10 additional lines of code will change our user account creation process.

Let's take a look at the first three lines of this script:

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\Scripts\New_users.xls")
intRow = 2

There's that magical keyword Set again.  From our last article we know that Set means we are going to create an object reference.  In this case we are creating an object reference to an object called “Excel.Application“ and calling that reference objExcel.  Well that's simple enough, now that we have an Excel object it would be nice to be able to open an existing Excel workbook that contains my user account information.  And whammo, the next line of code does just that, here we create an object reference called objWorkbook that opens the file c:\scripts\new_users.xls!  That's it!  That's all it takes to open up an Excel file using VBscript.

Now the next line looks a little goofy:

intRow = 2

Why in the world do we need this line?  Well ideally you would want your Excel file to look a little something like this:

User                   SamAccountName       FirstName       LastName

Ken Myer           myerKen                      Ken                  Myer

Tim Mintner        mintnerTim                     Tim                 Mintner

I know.  It's a lame example of an Excel spreadsheet.  I'm still trying to figure out how to do tables in this editor. (I'm lazy what do you expect?)

Anyway, if your Excel file looks something close to the example above,  you will notice that the first row contains nothing but header information.  While it is neat to look at, you really don't want to create a user account called User with a SamAccountName of SamAccountName, etc.  The data you really want is on the second row.  Therefore this line intRow = 2, is setting our Row to the second row in our spreadsheet.

The next line of our script is the beginning of a Do Until loop.  Ideally we would like to loop through this spreadsheet row by row until all of the user accounts in the spreadsheet have been created, magically stopping when there is no more user information.  That's where the Do Until loop comes into play:

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

  intRow = intRow + 1

Loop

This code tells our script to start at row 2 (because of the earlier line of code) cell 1.  The script then checks the value of that field.  If it is blank, then the script exits, otherwise it continues to run our code gladly creating account after account.  Each pass through, the script increments the row by 1 and then checks to see if that row is blank.  Therefore all you have to do to stop this script is to not have anymore user accounts to create.

The rest of the code looks just like the script in my last article with one exception.  Instead of hard coding the values for those properties, we are pulling the values from the Excel fields.

    Set objOU = GetObject("ou=Finance, dc=fabrikam, dc=com")
    Set objUser = objOU.Create("User", "cn=" & objExcel.Cells(intRow, 1).Value)
    objUser.sAMAccountName = objExcel.Cells(intRow, 2).Value
    objUser.GivenName = objExcel.Cells(intRow, 3).Value
    objUser.SN = objExcel.Cells(intRow, 4).Value
    objUser.AccountDisabled = FALSE
    objUser.SetInfo

Here we are in the Finance OU of the Fabrikam.com OU.  Again we are creating an object reference called objUser that references the Create method of objOU.  Notice this bit of code:

"cn=" & objExcel.Cells(intRow, 1).Value

Here the cn is being set to the Value of the current Row and the first cell.  Now that the user account is created, we can set the SAMAccountName to the value in the second cell of the Row, GivenName(First Name) to the 3rd cell, and SN (last name) to the 4th cell.  We then set the Account to be enabled and use SetInfo to commit the user account to the database.

Well hopefully you have seen how easy it is to use Excel to create a mass list of email accounts.  Again while this is very useful, it is still not a complete solution.  Now we have a solution that creates a lot of user accounts in the Finance OU.  My next article will show you how to include additional information such as OU, Phone Number, Profile Path, Home Drive, plus many other properties all inside of that single spreadsheet.

We are getting closer to being Lazy Administrators!

As always feel free to post any questions or comments.