[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Creating Bulk Active directory User's from excel AsAP

Posted on 2004-10-25
9
Medium Priority
?
1,134 Views
Last Modified: 2012-06-27
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
0
Comment
Question by:SoSmoothe
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
9 Comments
 

Author Comment

by:SoSmoothe
ID: 12406426
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

'**********************************************************************
0
 
LVL 4

Accepted Solution

by:
Beldoran earned 500 total points
ID: 12407501
I know this is not exactly what you have asked for but when I had to do this I used some Microsoft tools which worked very nicely. CSVDE and/or LDIFDE

Extract from web page http://support.microsoft.com/default.aspx?scid=kb;en-us;327620

"Csvde.exe is a Microsoft Windows 2000 command-line utility that is located in the SystemRoot\System32 folder after you install Windows 2000. Csvde.exe is similar to Ldifde.exe, but it extracts information in a comma-separated value (CSV) format. You can use Csvde to import and export Active Directory data that uses the comma-separated value format. Use a spreadsheet program such as Microsoft Excel to open this .csv file and view the header and value information. See Microsoft Excel Help for information about functions such as Concatenate that can simplify the process of building a .csv file."

I would recommend doing an export (using csvde) to get the format and modify your spreadsheet to match, save as .csv and import.

There are many other pages of examples in Microsoft and will work with XP as well.



0
 
LVL 4

Expert Comment

by:Beldoran
ID: 12407534
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
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 2

Expert Comment

by:aaarrrgggghhh
ID: 12412285
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.
0
 
LVL 4

Expert Comment

by:Beldoran
ID: 12417780
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
0
 
LVL 4

Expert Comment

by:Beldoran
ID: 12640167
I believe I have answered the question as asked and also provided an alternative.
Bel
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What do responsible coders do? They don't take detrimental shortcuts. They do take reasonable security precautions, create important automation, implement sufficient logging, fix things they break, and care about users.
The SignAloud Glove is capable of translating American Sign Language signs into text and audio.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Six Sigma Control Plans
Suggested Courses

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question