asked on

Creating Bulk Active directory User's from excel AsAP

Hello
I have an excel spreadsheet containing a large number of usernames, password, and various other attributes from which I would like to create users in an Active Directory Domain.
I need a script (preferably vbs) which will reference the spreadsheet, looping through the rows and create the users for automatically me.

Thank you
So Smoothe

SoSmoothe

ASKER

curently i have this vbs scrpit that you can only do one user at a time
Option Explicit

Dim oRoot, oOU, strContainer, oUser, strUser

strContainer = "LDAP://ou=finance,dc=acme,dc=com"

Const ADS_PROPERTY_CLEAR = 1
Const ADS_PROPERTY_UPDATE = 2
Const ADS_PROPERTY_APPEND = 3
Const ADS_PROPERTY_DELETE = 4

Set oOU = GetObject(strContainer)

Set oUser = oOU.Create("User", "cn=Judy Schneider") ' IADsContainer
oUser.Put "sAMAccountName", "JudyS" ' IADs
oUser.SetInfo ' IADs

oUser.SetPassword "qwerty" ' IADsUser

oUser.AccountDisabled = FALSE ' IADs
oUser.SetInfo ' IADs
'**********************************************************************

'**********************************************************************
'* General property page
'**********************************************************************
oUser.givenName = "Judy" ' First Name
oUser.initials = "JAS" ' Initials
oUser.sn = "Schneider" ' Last Name
oUser.displayName = "Judy Schneider" ' Display Name
oUser.description = "Comptroller" ' Description
oUser.physicalDeliveryOfficeName = "12/3456" ' Office Location
oUser.telephoneNumber = "(111) 111-1111" ' Telephone Number
oUser.otherTelephone = Array("(222) 222-2222", "(333) 333-3333") ' Phone Number (Others)
oUser.mail = "judys@acme.com" ' E-Mail Address
oUser.wWWHomePage = "http://acme.com/judys" ' Web Page Address
oUser.url = Array("http://anotherUrl", "http://yetanotherUrl") ' Web Page Address (Others)

'**********************************************************************
'* Address property page
'**********************************************************************
oUser.streetAddress = "1 Wall Street" ' Street Address
oUser.postOfficeBox = "N/A" ' Post Office Box
oUser.l = "New York" ' City
oUser.st = "NY" ' State/Province
oUser.postalCode = "10005" ' ZIP/Postal Code
oUser.co = "United States" ' Country

'**********************************************************************
'* Account property page
'**********************************************************************
oUser.userPrincipalName = "judys" ' Logon Name
oUser.userWorkstations = "jasdesktop" & "," & "jaslaptop" ' Log On To... Logon Workstations
oUser.pwdLastSet = CLng(-1) ' Disable "User must change password at next logon"
oUser.AccountExpirationDate = Date + 1 ' Account expires - End of:

'**********************************************************************
'* Profile property page
'**********************************************************************
oUser.profilePath = "%LOGONSERVER%\Documents and Settings\judys" ' User profile - Profile path
oUser.scriptPath = "logon.wsf //job:finance" ' User profile - Logon script
oUser.homeDrive = "H:" ' Home Drive
oUser.homeDirectory = "%LOGONSERVER%\Documents and Settings\judys" ' Home Folder

'**********************************************************************
'* Telephones property page
'**********************************************************************
oUser.homePhone = "(444) 444-4444" ' Home Phone
oUser.otherHomePhone = Array("(555) 555-5555", "(666) 666-6666") ' Home Phone (Others)
oUser.pager = "(777) 777-7777" ' Pager Number
oUser.otherPager = Array("(888) 888-8888", "(999) 999-9999") ' Pager Number (Others)
oUser.mobile = "(000) 000-0000" ' Mobile Number
oUser.otherMobile = Array("(aaa) aaa-aaaa", "(bbb) bbb-bbbb") ' Mobile Number (Others)
oUser.facsimileTelephoneNumber = "(ccc) ccc-cccc" ' Fax Number
oUser.otherFacsimileTelephoneNumber = Array("(ddd) ddd-dddd", _
"(eee) eee-eeee") ' Fax Number (Others)
oUser.ipPhone = "1.1.1.1" ' IP Phone Number
oUser.otherIpPhone = Array("2.2.2.2", "3.3.3.3") ' IP Phone Number (Others)
oUser.info = "Primary contact number: Mobile" ' Notes

'**********************************************************************
'* Organization property page
'**********************************************************************
oUser.title = "Senior Comptroller" ' Job Title
oUser.department = "Accounting & Finance" ' Department
oUser.company = "Acme Widgets" ' Company

'**********************************************************************
'* Miscellaneous:
'**********************************************************************
oUser.middleName = "Anne" ' Middle Name
oUser.employeeID = "23" ' Employee ID
oUser.comment = "Vegatarian" ' Comment
oUser.personalTitle = "Accountant" ' Title
oUser.division = "Corporate" ' Division
oUser.otherMailbox = Array("judys@hotmail.com", _
"judys@zdnetonebox.com") ' E-Mail Address (Others)
oUser.homePostalAddress = "10 Carriage Circle" ' Home Address
oUser.c = "US" ' Country Abbreviation
oUser.primaryTelexNumber = "(fff) fff-ffff" ' Telex Number
oUser.telexNumber = Array("(ggg) ggg-gggg", "(hhh) hhh-hhhh") ' Telex Number (Others)
oUser.primaryInternationalISDNNumber = "InternationalISDNNumber1" ' International ISDN Number

oUser.SetInfo
Set oOU = Nothing
Set oUser = Nothing

'**********************************************************************

'**********************************************************************
strUser = "LDAP://cn=Judy Schneider,ou=finance,dc=Conskill1,dc=com"

Set oUser = GetObject(strUser)
oUser.Put "homePostalAddress", "39 Echo Mill Drive" ' IADs
oUser.Put "homePhone", "(123) 456-7890" ' IADs
oUser.PutEx ADS_PROPERTY_UPDATE, _
"otherHomePhone", _
Array("(098) 765-4321") ' IADs
oUser.SetInfo ' IADs
Set oUser = Nothing

'**********************************************************************

'**********************************************************************
strContainer = "LDAP://ou=Finance,dc=Conskill1,dc=com"
strUser = "LDAP://cn=Judy Schneider,ou=finance,dc=ConSkill1,dc=com"

Set oOU = GetObject(strContainer)
oOU.MoveHere strUser, "cn=Judy Schneider" ' IADsContainer
Set oOU = Nothing

'**********************************************************************

ASKER CERTIFIED SOLUTION

Beldoran

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Beldoran

Just found some sample code which opens and loops through an excel spreadsheet try,

http://www.labyrinth.net.au/~dbareis/ppwizard/importexcel.htm

Good luck

aaarrrgggghhh

from exel press Alt+F11 to get to VBA. Create yourself a new sub routine and copy your existing code into there replacing the hardcoded values of attributes with variables. These variables can then be populated from the excel sheet.

Place a for loop round you code e.g:

for i = 1 to 1000

strGivenName = cells(i, 1)
strInitials = cells(i, 2)
strSn = cells(i, 3)
...etc...

...your modified code...

next

Not a detailed answer, but should be enough to give you the jist. The point is that the values from excel can easily be accessed using "cells(i,j)". Note for the LDAP stuff to work, you'll need to include some references for the LDAP DLLs from the Tools menu.

Beldoran

the VBS wrapper would go something like this, (bits cut directly from http://www.labyrinth.net.au/~dbareis/ppwizard/importexcel.htm )

'--- Create Excel object -------------------------------------------------
set XlObj = Wscript.CreateObject("Excel.Application")

'--- Open the EXCEL Spread sheet -----------------------------------------
call XlObj.Workbooks.Open("C:\TMP\EXCEL\SIMPLE.XLS",0)
XlObj.Sheets(XlSheet).Activate

'--- Loop through rows until we find a cell in "A" that is empty ---------
Row = 1
do until XlObj.Range("A:A").Cells(Row).Text = ""

**********************************************************************
'* General property page
'*
'* Assumes givenname is in col A, initials in Col B, etc...
'* Loops until there is no givenname in the first column
'**********************************************************************
oUser.givenName = XlObj.Range("A:A").Cells(Row).Text ' First Name
oUser.initials = XlObj.Range("B:B").Cells(Row).Text ' Initials
oUser.sn = XlObj.Range("C:C").Cells(Row).Text ' Last Name
oUser.displayName = XlObj.Range("D:D").Cells(Row).Text ' Display Name
' etc...

'--- Look at next row ------------------------------------------------
Row = Row + 1
loop

'--- Close Excel object --------------------------------------------------
XlObj.Quit

'--- That's All Folks ----------------------------------------------------
Wscript.Quit 0

Beldoran

I believe I have answered the question as asked and also provided an alternative.
Bel