Instructions for Creating Active Directory-Aware User Accounts from an Excel Spreadsheet

There may be an occasion where you need to bulk-create new user accounts from a list given to you by HR. Using the below Excel spreadsheet method combined with an AD-aware VB script can solve your headache in short order. 

Step 1 - Build the Excel Spreadsheet

1. Each user will occupy one row, for example John Evans, Row 3.  Each attribute will always be in the same column, for example givenName in Column C.
2. Mandatory LDAP attributes: sAMAccountName and CN (ObjectClass is taken care of by VBScript).
3. Important LDAP attributes: givenName, sn
4. Optional LDAP attributes: physicalDeliveryOfficeName, email, phone, description, displayName.
5. Note how you can use the power of Excel's functions to derive one column from another, for example, sAMAccountName could be build up from the first three letters of the givenName added to the 4 left most characters of the sn. See =LEFT(C3,3)&LEFT(D3,4) in the above diagram. 

Step 2 - Copy and Edit the VBScript

1. You need access to a Windows Active Directory domain.
2. Copy and paste the example script below into notepad or a VBScript editor.
3. Amend the file path for strSheet.  Example: strSheet = "E:\scripts\CreateUserSpreadsheet.xls"
4. Save the file with a .vbs extension, for example: CreateUserSpreadsheet.vbs.
5. Double click CreateUserSpreadsheet.vbs and check the Computers container for strComputer.

Example Script to create User Accounts from a spreadsheet

' CreateUserSpreadsheet .vbs
' Sample VBScript to create User accounts from a spreadsheet

' ------------------------------------------------------'
Option Explicit
Dim objRootLDAP, objContainer, objUser, objShell
Dim objExcel, objSpread, intRow
Dim strUser, strOU, strSheet
Dim strCN, strSam, strFirst, strLast, strPWD

' -------------------------------------------------------------'
' Important change OU= and strSheet to reflect your domain
' -------------------------------------------------------------'

strOU = "OU=Accounts7 ," ' Note the comma
strSheet = "E:\scripts\UserSpread1.xls"

' 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 = 3 'Row 1 often contains headings

' Here is the 'DO...Loop' that cycles through the cells
' Note intRow, x must correspond to the column in strSheet
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)
   strLast = Trim(objExcel.Cells(intRow, 4).Value)
   strPWD = Trim(objExcel.Cells(intRow, 5).Value)

   ' Build the actual User from data in strSheet.
   Set objUser = objContainer.Create("User", "cn=" & strCN)
   objUser.sAMAccountName = strSam
   objUser.givenName = strFirst
   objUser.sn = strLast
   objUser.SetInfo

   ' Separate section to enable account with its password set to expire via policy, you can set the value to 514 to have the accounts set to disabled
   objUser.userAccountControl = 512
   objUser.pwdLastSet = 0
   objUser.SetPassword strPWD
   objUser.SetInfo

intRow = intRow + 1
Loop
objExcel.Quit

WScript.Quit

' End of example CreateUserSpreadsheet VBScript.

VBScript Tutorial – Notes on Using Excel Spreadsheet

 Note 1: In this example, the basic Excel spreadsheet has just 5 columns of properties / LDAP attributes.  Trace how each of the 5 columns is used in the VBScript, see line 33 onwards.  Once you master the concept, then you can add many more columns of LDAP properties.
Note 2: As I mentioned earlier, I love the power of Excel to calculate one column from another.  Column A, sAMAccountName (logon name) is derived from the first three letters of the givenName, joined with an & to the first 4 letters of the sn column. =Left(C3,3)&LEFT(D3,4).  The beauty of this technique is that you can then use Excel's fill down to calculate the rest of the users.
Note 3: I always reserve Row 2 for indexing the Column letters, e.g. A = 1, B=2 etc.  This makes it easier to reference .cell properties, for example,  intRow, 4).Value) corresponds to Column D.
Note 4: It is worth commenting on what is not explicitly required in the spreadsheet.  VBScript takes care of the objectClass ("User").  It also calculates the DN (Distinguished Name) from the name of the OU and the DNS domain as specified by objContainer.

Note 1:  In this example see how CreateObject("Excel.Application") creates an instance of Excel.  Equally see how objExcel.Quit closes Excel at the end of the script.
Note 2:  Here we employ the Open method, just as if we clicked on the File menu: objExcel.Workbooks.Open(strSheet)
Note 3: It is worth studying the Do.. Loop from lines 33-54.  If you break the loop into 3 sections, you can see at the first section where it interacts with the spreadsheet, extracting the values with the aid of the trim function to get rid of any spaces.
The second section builds most of the user, while the third section deals with setting the password and enabling the account with userAccountControl = 512
Note 4: objExcel.Quit prevents zillions of instance of Excel in the Task Manger when running the script, do not remove it.  However, do watch out for numerous instances of Excel in your Task manager as a precaution, some of these may prevent you editing your spreadsheet.  If the script fails for any reason, then you will get an orphaned Excel instance running which you'll need to zap with Task Manager.

END OF GUIDE