How to Create User Accounts from a Spreadsheet with VBScript
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 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.)
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.
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.
Import users from a spreadsheet, complete with their mailbox. Just provide a list of the users with the fields in the top row, and save as .csv file. Then launch this FREE utility, match your Exchange fields with AD's attributes, click and import the users. Optionally, you can provide the name of the OU where the new mailboxes will be born.
Instructions for Creating User Accounts from a Spreadsheet
Phase 1 Build the Spreadsheet
Instructions for Phase 2 - Copy and amend my VBScript
Example Script to create User Accounts from a spreadsheet
' UserSpreadsheet .vbs
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.
I like the Permissions Analyzer because it enables me to see WHO has permissions to do WHAT at a glance. When you launch this tool it analyzes a users effective NTFS permissions for a specific file or folder, and takes into account network share access, then displays the results in a nifty desktop dashboard!
Think of all the frustration that this free SolarWinds utility saves when you are troubleshooting authorization problems for user's access to a resource. Give this permissions monitor a try - it's free!
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.
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'.