• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 441
  • Last Modified:

vbs Script to create users from excel

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: http://computerperformance.co.uk/
' 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
   objUser.sn = strLast
   objuser.displayName = strdisplay
   objUser.userPrincipleName = strUPN
   objUser.c = strCtry
   objUser.company = 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

  • 3
  • 3
  • 2
1 Solution
Ken FayalCommented:
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

try to remove line 6
Ken FayalCommented:
You will also have the same problems with lines 39 - 42.
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

leche671Author Commented:
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?
Ken FayalCommented:
No, I do it all the time.  You just have it spelled wrong.  It should be userPrincipalName, not userPrincipleName
leche671Author Commented:
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.
leche671Author Commented:
excuse me not line 54. it errors on line 57 "objUser.SetInfo"
change line 56

obluser.title = strtil

it's need to be

objuser.title = strtil

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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