Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1141
  • Last Modified:

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
0
SoSmoothe
Asked:
SoSmoothe
  • 4
1 Solution
 
SoSmootheAuthor Commented:
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
 
BeldoranCommented:
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
 
BeldoranCommented:
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
Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 
aaarrrgggghhhCommented:
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
 
BeldoranCommented:
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
 
BeldoranCommented:
I believe I have answered the question as asked and also provided an alternative.
Bel
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Making Bulk Changes to Active Directory

Watch this video to see how easy it is to make mass changes to Active Directory from an external text file without using complicated scripts.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now