Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Creating Bulk Active directory User's from excel AsAP

Posted on 2004-10-25
9
Medium Priority
?
1,137 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
  • 4
6 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
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.

 
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

Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

Question has a verified solution.

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

It’s time for spooky stories and consuming way too much sugar, including the many treats we’ve whipped for you in the world of tech. Check it out!
The article explains the process to deploy a Self-Service password reset portal I developed a few years ago. Hopefully, it will prove useful to someone.  Any comments, bug reports etc. are welcome...
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Progress

577 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