How to Create User Accounts from a Spreadsheet with VBScript tip

0 Reviews Post a Review Posted by: sabayshared24 Published On: 3:05 AM Avaliability Product Avaliable For Sale

adnroid APPLE Featured PROGRAMME trick vb6 vba VBA/Programme vbs VBS/Programme wifi.router

Add to Cart

View Cart Checkout

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: scriptsUserSpread1.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

' UserSpreadsheet .vbs
' Sample VBScript to create User accounts from a spreadsheet
' Author Guy Thomas http://computerperformance.co.uk/
' Version 4.6 - June 2010
' ------------------------------------------------------'
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:scriptsUserSpread1.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
objUser.userAccountControl = 512
objUser.pwdLastSet = 0
objUser.SetPassword strPWD
objUser.SetInfo

intRow = intRow + 1
Loop
objExcel.Quit

WScript.Quit

' End of free example UserSpreadsheet VBScript.

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.

Source    http://www.computerperformance.co.uk/vbscript