Solved

Creating Bulk Active directory User's from excel AsAP

Posted on 2004-10-25
1,120 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
Question by:SoSmoothe
    6 Comments
     

    Author Comment

    by:SoSmoothe
    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:
    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
    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
     
    LVL 2

    Expert Comment

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

    Expert Comment

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

    Expert Comment

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone. Privacy Policy Terms of Use

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Suggested Solutions

    Title # Comments Views Activity
    Email sent from Outlook, OWA or iPhone - Exchange 2010 7 27
    find a node in VST 2 23
    count7 challenge 12 24
    allStar challenge 1 10
    Introduction You may have a need to setup a group of users to allow local administrative access on workstations.  In a domain environment this can easily be achieved with Restricted Groups and Group Policies. This article will demonstrate how to…
    This is about my first experience with programming Arduino.
    This tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles to another domain controller. Log onto the new domain controller with a user account t…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    877 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

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now