vbs Script to create users from excel

Posted on 2009-04-22
Last Modified: 2013-12-27
I have a script that I've used before to add users from an excel spreadsheet. I'm trying to add more LDAP fields to the script but it's failing. I think I may be using the wrong syntax. The code errors on Line 36 Char 4 "Variable is undefined: "strdisplay" is not defined.
' UserSpreadsheet .vbs

' VBScript to create User accounts from a spreadsheet

' RE:

' Version 1.0 - April 2009

' ------------------------------------------------------' 

Option Explicit

Dim objRootLDAP, objContainer, objUser, objShell

Dim objExcel, objSpread, intRow

Dim strUser, strOU, strSheet

Dim strCN, strSam, strFirst, strLast, strPWD

' -------------------------------------------------------------'

' Important change OU= and strSheet to reflect your domain

' -------------------------------------------------------------'

strOU = "OU Path ," ' Note the comma

strSheet = "Excel Path"

' Bind to Active Directory, Users container.

Set objRootLDAP = GetObject("LDAP://rootDSE")

Set objContainer = GetObject("LDAP://" & strOU & _


' Open the Excel spreadsheet

Set objExcel = CreateObject("Excel.Application")

Set objSpread = objExcel.Workbooks.Open(strSheet)

intRow = 3 'Row 1 often contains headings

' Here is the 'DO...Loop' that cycles through the cells

' Note intRow, x must correspond to the column in strSheet

Do Until objExcel.Cells(intRow,1).Value = ""

   strSam = Trim(objExcel.Cells(intRow, 1).Value)

   strCN = Trim(objExcel.Cells(intRow, 2).Value) 

   strFirst = Trim(objExcel.Cells(intRow, 3).Value)

   strLast = Trim(objExcel.Cells(intRow, 4).Value)

   strdisplay = Trim(objExcel.Cells(intRow, 6).Value)

   strUPN = Trim(objExcel.Cells(intRow, 7).Value)    

   strCtry = Trim(objExcel.Cells(intRow, 8).Value)

   strcom = Trim(objExcel.Cells(intRow, 9).Value)

   strdep = Trim(objExcel.Cells(intRow, 10).Value)

   strtel = Trim(objExcel.Cells(intRow, 11).Value) 

   strtil = Trim(objExcel.Cells(intRow, 12).Value)

   strPWD = Trim(objExcel.Cells(intRow, 5).Value)

   ' Build the actual User from data in strSheet.

   Set objUser = objContainer.Create("User", "cn=" & strCN)

   objUser.sAMAccountName = strSam

   objUser.givenName = strFirst = strLast

   objuser.displayName = strdisplay

   objUser.userPrincipleName = strUPN

   objUser.c = strCtry = strcom

   objUser.department = strdep

   objUser.telephoneNumber = strtel

   obluser.title = strtil


   ' Separate section to enable account with its password

   objUser.userAccountControl = 512

   objUser.pwdLastSet = 0

   objUser.SetPassword strPWD


intRow = intRow + 1




' End of free example UserSpreadsheet VBScript.

Open in new window

Question by:leche671
    LVL 9

    Expert Comment

    by:Ken Fayal
    You don't have the variable dim'd out in lines 7-11.  You would need to add strdisplay to the dims as I've shown below.
    Dim strCN, strSam, strFirst, strLast, strPWD, strdisplay

    Open in new window

    LVL 14

    Expert Comment

    try to remove line 6
    LVL 9

    Expert Comment

    by:Ken Fayal
    You will also have the same problems with lines 39 - 42.

    Author Comment

    I added the DIM fields but now I get an error saying that the Object doesn't support this property or method: "userPrincipleName" Can this just not be scripted?
    LVL 9

    Expert Comment

    by:Ken Fayal
    No, I do it all the time.  You just have it spelled wrong.  It should be userPrincipalName, not userPrincipleName

    Author Comment

    Ok I think we're real close. I know get an error on line 57 char 4 "a constraint violation occured" I don't know what this command does. It was just in the original script so I didn't take it out.

    Author Comment

    excuse me not line 54. it errors on line 57 "objUser.SetInfo"
    LVL 14

    Accepted Solution

    change line 56

    obluser.title = strtil

    it's need to be

    objuser.title = strtil

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    this article is a guided solution for most of the common server issues in server hardware tasks we are facing in our routine job works. the topics in the following article covered are, 1) dell hardware raidlevel (Perc) 2) adding HDD 3) how t…
    In this article you will get to know about pros and cons of storage drives HDD, SSD and SSHD.
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    734 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

    19 Experts available now in Live!

    Get 1:1 Help Now