How to Create User Accounts from a Spreadsheet with VBScript
Posted by thangletoan
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.
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.
Guy Recommends: SolarWinds’ Free Bulk Mailbox Import Tool
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.
- Bulk-import new users and mailboxes into Active Directory.
- Seek and zap unwanted user accounts.
- Find inactive computers.
Instructions for Creating User Accounts from a Spreadsheet
Phase 1 Build the Spreadsheet
- 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.
- Mandatory LDAP attributes: sAMAccountName and CN (ObjectClass is taken care of by VBScript).
- Important LDAP attributes: givenName, sn
- Optional LDAP attributes: physicalDeliveryOfficeName, email, phone, description, displayName.
- 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
- You need access to a Windows Active Directory domain.
- Check the prerequisite to create an Excel spreadsheet.
- Copy and paste the example script below into notepad or a VBScript editor.
- 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.
- Save the file with a .vbs extension, for example: ComputerSpreadsheet .vbs.
- Double click ComputerSpreadsheet .vbs and check the Computers container for strComputer.
Example Script to create User Accounts from a spreadsheet
‘ UserSpreadsheet .vbs
‘ Sample VBScript to create User accounts from a spreadsheet
‘ Author Guy Thomas http://computerperformance.co.uk/
‘ Version 4.6 – June 2010
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 & _
‘ 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
‘ Separate section to enable account with its password
objUser.userAccountControl = 512
objUser.pwdLastSet = 0
intRow = intRow + 1
‘ End of free example UserSpreadsheet VBScript.
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.
Recommended: Solarwinds’ Permissions Analyzer – Free Active Directory Tool
I like thePermissions Monitor 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.
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’
About thangletoanHallo Aloha
Posted on 27/06/2012, in Active Directory AD, AD developer, Chính sách CNTT, Live@edu, PowerShell, PowerShell PS, Private cloud and tagged Live@edu, PowerShell. Bookmark the permalink. Bạn nghĩ gì về bài viết này?.