Photo Gallery Member List Search Calendars FAQ Ticket List Log Out


Add users to Active Directory from Excel

  		Logged in as: Guest
arrSession:exec spGetSession 2,2,32343
Active Users: There are 0 members and 0 guests.
Users viewing this topic: none
 

 

  
 
  Printable Version
All Forums >> [Scripting] >> WSH & Client Side VBScript >> Add users to Active Directory from Excel
  Do you like VisualBasicScript.com? Link to us and help spread the word about our forum. Thanks! 		Page: [1]
Login
Message << Older Topic   Newer Topic >>
Add users to Active Directory from Excel - 3/16/2006 11:28:33 PM   
  chucky1323

 

Posts: 7
Score: 0
Joined: 3/16/2006
Status: offline 		Hi i found the script on the web and modified it but when i run the script i get an error code of 8007200B and only the very first user account is created

the error reads

Line 51
Char 4

Error The attribute systax specified to the directory service is invalid

code 8007200B
source (null)

here is the code

' ------------------------------------------------------'
Option Explicit
Dim objRootLDAP, objContainer, objUser, objShell
Dim objExcel, objSpread, intRow
Dim strUser, strOU, strSheet
Dim strCN, strSam, strFirst, strLast, strPWD, strdisplay, strdesc, strprinc, strinit
' -------------------------------------------------------------'
' Important change OU= and strSheet to reflect your domain
' -------------------------------------------------------------'
strOU = "OU=Accounts ," ' Note the comma
strSheet = "d:\scripts\UserSpread1.xls"
' Bind to Active Directory, Users container.
Set objRootLDAP = GetObject("LDAP://rootDSE")
Set objContainer = GetObject("LDAP://" & strOU & _
objRootLDAP.Get("defaultNamingContext"))
' 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, 4).Value)
  strLast = Trim(objExcel.Cells(intRow, 3).Value)
  strPWD = Trim(objExcel.Cells(intRow, 5).Value)
  strdisplay = Trim(objExcel.Cells(intRow, 6).Value)
  strdesc = Trim(objExcel.Cells(intRow, 7).Value)
  strprinc = Trim(objExcel.Cells(intRow, 8).Value)
  strinit = Trim(objExcel.Cells(intRow, 9).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.description = strdesc
  objUser.userPrincipalName = strprinc
  objUser.initials = strinit
  objUser.SetInfo
  ' Separate section to enable account with its password
   objUser.userAccountControl = 512
   objUser.pwdLastSet = 0
   objUser.SetPassword strPWD
   objUser.SetInfo
intRow = intRow + 1
Loop
objExcel.Quit
WScript.Quit
' End of Sample UserSpreadsheet VBScript
 
 
Post #: 1
 
RE: Add users to Active Directory from Excel - 3/17/2006 12:29:28 AM   
  Country73


Posts: 733
Score: 10
Joined: 8/25/2004
From: USA
Status: offline 		Are you sure this is all of your code?
I don't see line 51; I only see 49 lines in your script.

Please supply the whole script, or show us the exact line you are getting the error on.

(in reply to chucky1323)
 
 
Post #: 2
 
RE: Add users to Active Directory from Excel - 3/17/2006 12:44:38 AM   
  chucky1323

 

Posts: 7
Score: 0
Joined: 3/16/2006
Status: offline 		sorry about that did not copy all

here u go

' UserSpreadsheet .vbs
' Sample VBScript to create User accounts from a spreadsheet
' Author Guy Thomas http://computerperformance.co.uk/
' Version 4.6 - June 2005
' ------------------------------------------------------'
Option Explicit
Dim objRootLDAP, objContainer, objUser, objShell
Dim objExcel, objSpread, intRow
Dim strUser, strOU, strSheet
Dim strCN, strSam, strFirst, strLast, strPWD, strdisplay, strdesc, strprinc, strinit
' -------------------------------------------------------------'
' Important change OU= and strSheet to reflect your domain
' -------------------------------------------------------------'
strOU = "OU=Accounts ," ' Note the comma
strSheet = "d:\scripts\UserSpread1.xls"
' Bind to Active Directory, Users container.
Set objRootLDAP = GetObject("LDAP://rootDSE")
Set objContainer = GetObject("LDAP://" & strOU & _
objRootLDAP.Get("defaultNamingContext"))
' 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, 4).Value)
  strLast = Trim(objExcel.Cells(intRow, 3).Value)
  strPWD = Trim(objExcel.Cells(intRow, 5).Value)
  strdisplay = Trim(objExcel.Cells(intRow, 6).Value)
  strdesc = Trim(objExcel.Cells(intRow, 7).Value)
  strprinc = Trim(objExcel.Cells(intRow, 8).Value)
  strinit = Trim(objExcel.Cells(intRow, 9).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.description = strdesc
  objUser.userPrincipalName = strprinc
  objUser.initials = strinit
  objUser.SetInfo
  ' Separate section to enable account with its password
   objUser.userAccountControl = 512
   objUser.pwdLastSet = 0
   objUser.SetPassword strPWD
   objUser.SetInfo
intRow = intRow + 1
Loop
objExcel.Quit
WScript.Quit
' End of Sample UserSpreadsheet VBScript.

(in reply to Country73)
 
 
Post #: 3
 
RE: Add users to Active Directory from Excel - 3/17/2006 1:02:36 AM   
  Country73


Posts: 733
Score: 10
Joined: 8/25/2004
From: USA
Status: offline 		Just to make sure I'm viewing this the same way, are you recieving the error on:

intRow = intRow + 1

That's what I see at line 51

(in reply to chucky1323)
 
 
Post #: 4
 
RE: Add users to Active Directory from Excel - 3/17/2006 1:31:36 AM   
  chucky1323

 

Posts: 7
Score: 0
Joined: 3/16/2006
Status: offline 		Hi

when i copy and paste the code it removes the spaces but line 51

objUser.SetInfo


hi i put number in front of the code

1' UserSpreadsheet .vbs
2' Sample VBScript to create User accounts from a spreadsheet
3' Author Guy Thomas http://computerperformance.co.uk/
4' Version 4.6 - June 2005
5' ------------------------------------------------------'
6Option Explicit
7Dim objRootLDAP, objContainer, objUser, objShell
8Dim objExcel, objSpread, intRow
9Dim strUser, strOU, strSheet
10Dim strCN, strSam, strFirst, strLast, strPWD, strdisplay, strdesc, strprinc, strinit
11
12' -------------------------------------------------------------'
13' Important change OU= and strSheet to reflect your domain
14' -------------------------------------------------------------'
15
16strOU = "OU=Accounts ," ' Note the comma
17strSheet = "d:\scripts\UserSpread1.xls"
18
19' Bind to Active Directory, Users container.
20Set objRootLDAP = GetObject("LDAP://rootDSE")
21Set objContainer = GetObject("LDAP://" & strOU & _
22objRootLDAP.Get("defaultNamingContext"))
23
24' Open the Excel spreadsheet
25Set objExcel = CreateObject("Excel.Application")
26Set objSpread = objExcel.Workbooks.Open(strSheet)
27intRow = 3 'Row 1 often contains headings
28
29' Here is the 'DO...Loop' that cycles through the cells
30' Note intRow, x must correspond to the column in strSheet
31Do Until objExcel.Cells(intRow,1).Value = ""
32   strSam = Trim(objExcel.Cells(intRow, 1).Value)
33   strCN = Trim(objExcel.Cells(intRow, 2).Value)
34   strFirst = Trim(objExcel.Cells(intRow, 4).Value)
35   strLast = Trim(objExcel.Cells(intRow, 3).Value)
36   strPWD = Trim(objExcel.Cells(intRow, 5).Value)
37   strdisplay = Trim(objExcel.Cells(intRow, 6).Value)
38   strdesc = Trim(objExcel.Cells(intRow, 7).Value)
39   strprinc = Trim(objExcel.Cells(intRow, 8).Value)
40   strinit = Trim(objExcel.Cells(intRow, 9).Value)
41
42   ' Build the actual User from data in strSheet.
43   Set objUser = objContainer.Create("User", "cn=" & strCN)
44   objUser.sAMAccountName = strSam
45   objUser.givenName = strFirst
46   objUser.sn = strLast
47   objUser.displayName = strdisplay
48   objUser.description = strdesc
49   objUser.userPrincipalName = strprinc
50   objUser.initials = strinit
51   objUser.SetInfo
52
53   ' Separate section to enable account with its password
54    objUser.userAccountControl = 512
55    objUser.pwdLastSet = 0
56    objUser.SetPassword strPWD
57    objUser.SetInfo
58
59intRow = intRow + 1
60Loop
61objExcel.Quit
62
63WScript.Quit
64
65' End of Sample UserSpreadsheet VBScript.

(in reply to Country73)
 
 
Post #: 5
 
RE: Add users to Active Directory from Excel - 3/17/2006 1:54:38 AM   
  Country73


Posts: 733
Score: 10
Joined: 8/25/2004
From: USA
Status: offline 		When you run this script does it add the first user correctly and then it errors out, or is it erroring out on the first user?

(I don't have a test environment to test this out, and our network side wouldn't be too happy if I tried it out, so I'll do what I can for you.)

If you try running this script to add just one individual, does the script run correctly?

(in reply to chucky1323)
 
 
Post #: 6
 
RE: Add users to Active Directory from Excel - 3/17/2006 2:04:32 AM   
  chucky1323

 

Posts: 7
Score: 0
Joined: 3/16/2006
Status: offline 		when i run the scriot it runs for a few seconds and then i get the error message
then if i go into the OU which is Accounts it only has the first user from the excel file with all the correct information entered for that user
if i delete that user and run the script again it does the same thing and the same use is created with the correct info
i even changes the info for the first use and the same thing happens again but the new user is created




(in reply to Country73)
 
 
Post #: 7
 
RE: Add users to Active Directory from Excel - 3/17/2006 3:07:33 AM   
  ebgreen


Posts: 5069
Score: 31
Joined: 7/12/2005
Status: offline 		What if you add this line right after the SetInfo line:
Set objUser = Nothing

_____________________________

"... when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick
Goog places to start:http://www.visualbasicscript.com/m_24727/tm.htm
http://www.visualbasicscript.com/m_47117/tm.htm

(in reply to chucky1323)
 
 
Post #: 8
 
RE: Add users to Active Directory from Excel - 3/17/2006 3:29:44 AM   
  chucky1323

 

Posts: 7
Score: 0
Joined: 3/16/2006
Status: offline 		Thanks for the reply

But that did not work got the same error and olny the first user was created

(in reply to ebgreen)
 
 
Post #: 9
 
RE: Add users to Active Directory from Excel - 3/17/2006 3:40:21 AM   
  ebgreen


Posts: 5069
Score: 31
Joined: 7/12/2005
Status: offline 		What OS?

_____________________________

"... when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick
Goog places to start:http://www.visualbasicscript.com/m_24727/tm.htm
http://www.visualbasicscript.com/m_47117/tm.htm

(in reply to chucky1323)
 
 
Post #: 10
 
RE: Add users to Active Directory from Excel - 3/17/2006 4:17:53 AM   
  chucky1323

 

Posts: 7
Score: 0
Joined: 3/16/2006
Status: offline 		I am running it on a Windows 2000 ADC

(in reply to ebgreen)
 
 
Post #: 11
 
RE: Add users to Active Directory from Excel - 3/17/2006 4:28:27 AM   
  ebgreen


Posts: 5069
Score: 31
Joined: 7/12/2005
Status: offline 		Is there any difference in the type of data in the excel spreadsheet for the first user vs. the second?

_____________________________

"... when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick
Goog places to start:http://www.visualbasicscript.com/m_24727/tm.htm
http://www.visualbasicscript.com/m_47117/tm.htm

(in reply to chucky1323)
 
 
Post #: 12
 
RE: Add users to Active Directory from Excel - 3/17/2006 5:02:30 AM   
  chucky1323

 

Posts: 7
Score: 0
Joined: 3/16/2006
Status: offline 		ok cool

i compaired the first row with the second and the problem was the fact that the sn colum was blank
i  filled it in and ran the script again it created user account up to a user who did not have the sn filled

thanks for the suggestion i will correct the excel document


(in reply to ebgreen)
 
 
Post #: 13
 
 
 
 

If you found our site useful please link to us <a href="http://www.visualbasicscript.com">VisualBasicScript.com</a>.
All Forums >> [Scripting] >> WSH & Client Side VBScript >> Add users to Active Directory from Excel Page: [1]
Jump to:





New Messages No New Messages
Hot Topic w/ New Messages Hot Topic w/o New Messages
Locked w/ New Messages Locked w/o New Messages
 Post New Thread
 Reply to Message
 Post New Poll
 Submit Vote
 Delete My Own Post
 Delete My Own Thread
 Rate Posts




Forum Software © ASPPlayground.NET Advanced Edition 2.5.5 ANSI