How to Create User Accounts from a Spreadsheet with VBScript

Guy recommends this:
Free - WMI Monitor

---

Guy's Top 10 Free Tools

1) FreePing
2) Network Monitor
3) Xobni
4) PuTTY (UNIX)
5) IP SLA Monitor
6) BgInfo
7) Net-SNMP
8) Engineer's Toolset
9) Exchange Monitor
10) WinDiff

Tutorial for Creating User Accounts from an Excel Spreadsheet

This is this THE key VBScript page for creating Active Directory accounts.  The User object has many attributes, consequently the VBScript will be complex.  My tutorial will provide step-by-step instructions to import Users into your domain from values held in an Excel spreadsheet.  The benefit of mastering how to build a user object is that creating the other objects such as computer, group or OU will be so much easier.

Topics for Creating User Accounts from a Spreadsheet

• Our Mission and Goals
• Example - Create User Accounts from a Spreadsheet
• VBScript Tutorial - Learning Points - Spreadsheet
• VBScript Tutorial - Learning Points - VBScript
• Summary Creating User Accounts from a Spreadsheet

Our Mission and Goals

Our goal is to read user properties from a spreadsheet, and to use that data as a source of names for new User Accounts.  Whilst my previous user script only created one user, with this script could create an account for every user in your organization.

In order to access the user's properties in the spreadsheet we need a new scripting object.  Fortunately, VBScript has a method called CreateObject("Excel.Application"), which handles data transfer between the spreadsheet and Active Directory.  The other scripting technique is the classic loop.  In this instance I chose: Do... Until empty, this cycles through the cells reading the users' properties.  (Other scripts employ For... Then.... Next constructions.)

Example Script to Create User Accounts from a Spreadsheet

It is best to divide our mission into two phases, firstly build the spreadsheet, secondly master the VBScript techniques to open that spreadsheet and create a new user based on the data in each row.

Before you script any object, have a walk though with Active Directory Users and Computers.  Creating the object manually will remind you of the properties an object possesses, properties that you will need in your script.

Prerequisites

Recommended: that you complete my basic script as a refresher on how VBScript binds to Active directory.  If possible, logon as administrator, preferably at a domain controller.

Create a spreadsheet with your prospective users' properties.  My advice is to spend time researching the LDAP attributes, which correspond to the property sheets in Active Directory Users and Computers.  See more on LDAP properties here.

Be aware that where you save this .xls file should correspond to the strSheet variable in the script below.  For example: E: \scripts\Computers.xls.

Instructions for Creating User Accounts from a Spreadsheet

Phase 1 Build the 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.  (Reference Now corrected thanks to Brian C)

Instructions for Phase 2 - Copy and amend my VBScript

1. You need access to a Windows Active Directory domain.
2. Check the prerequisite to create an Excel spreadsheet.
3. Copy and paste the example script below into notepad or a VBScript editor.
4. Amend the path for strSheet.  I will be surprised if strSheet = "E:\ scripts\UserSpread1.xls" works without modification to reflect the location of Your spreadsheet.
5. Save the file with a .vbs extension, for example: ComputerSpreadsheet .vbs.
6. Double click ComputerSpreadsheet .vbs and check the Computers container for strComputer.

Example Script to create User Accounts from a spreadsheet

VBScript Tutorial - Learning Points - 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.

VBScript Tutorial - Learning Points - VBScript

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.  If I try to join the second and third sections without that intermediate .SetInfo, the script fails.

Note 4: When I first ran this script I noticed zillions of instance of Excel in the Task Manger, this is how I cured that problem objExcel.Quit.  However, without error-correcting code, watch out for numerous instances of Excel in your Task manager, some of these may prevent you editing your spreadsheet.  I also confess that if the script fails, then you get an orphaned Excel which you need to zap with Task Manager.

Summary of Creating User Accounts from an Excel Spreadsheet

Creating users from a spreadsheet is one of the high points of VBScript.  Pay equal attention to the Excel spreadsheet, your VBScript code, and Active Directory Users and Computers.  To climb this scripting peak obey the old saying, 'Yard by yard and it's hard, but inch by inch and it's a cinch'.

Guy Thomas recommends Computer Training Software. 

Their topics and material are ideal for getting you started with VBScript.  The videos are easy to follow and you can control the pace.  Try their free demo material and then see if you want to buy the full package. See more about VB Script Training CD.

See Also

● Create User     ● Create a Computer from a Spreadsheet    ● LDAP Attributes in Active Directory