vbs script which can bulk upload AD accounts & mailboxes

patelbg2001
patelbg2001 used Ask the Experts™
on
HI I'm trying to create a script which can (bulk) upload users & mailbox details using an excel spreadsheet. I'm have difficulty binding to the Active Directory with my script and I need some help completing the script.  

It seems to error here with the following error

Line: 12
Char:12
Error: Expected end of statement
code: 800A0401

adoRS.Open "SELECT * FROM ["LDAP://DC=mx,DC=local"]", adoConn, 3, 3

It doesn't seem to bind with the domain at all which I cannot work out why.  This is the error from running it through MS Scriptomatic...

C:\scp\temp_script.sm(12, 1) Microsoft JET Database Engine: The Microsoft Jet database engine could not find the object 'LDAP://DC=mx,DC=local'.  Make sure the object exists and that you spell its name and the path name correctly.

Any assistance would be gratefully received

Thanks,
P

Option Explicit
 
' Get a connection to AD
Dim objRootDSE: Set objRootDSE = GetObject("LDAP://RootDSE")
Dim objDomain: Set objDomain = GetObject("LDAP://" & objRootDSE.Get("defaultNamingContext"))
 
' Get data from Excel s/s
Dim adoConn: Set adoConn = CreateObject("ADODB.Connection")
Dim adoRS: Set adoRS = CreateObject("ADODB.RecordSet")
adoConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\test.xls;Extended Properties=Excel 8.0"
' open a connection to the Domain
adoRS.Open "SELECT * FROM ["LDAP://DC=mx,DC=local"]", adoConn, 3, 3
 
' Create user accounts & mailboxes
Dim objUser
Dim objMailbox
Do Until adoRS.EOF
	Set objUser = objDomain.Create("user", "cn=" & adoRS("givenName") & " " & adoRS("sn"))
	objUser.Put "sAMAccountName", CStr(adoRS("sAMAccountName"))
	objUser.SetInfo
	objUser.SetPassword "wh()sy0urmama"
	objUser.AccountDisabled = False
	'objUser.Put "displayName", adoRS("givenName") & " " & adoRS("sn")
	'objUser.Put "sn", CStr(adoRS("sn"))
	'objUser.Put "givenName", CStr(adoRS("givenName"))
	objUser.Put "description", "Dummy user account"
	objUser.SetInfo
	objUser.Put "DN", CStr(adoRS("DN"))
	objUser.Put "objectClass", CStr(adoRS("objectClass"))
	objUser.Put "distinguishedName", CStr(adoRS("distinguishedName"))
	objUser.Put "name", CStr(adoRS("name"))
	objUser.Put "cn", CStr(adoRS("cn"))
	objUser.Put "sn", CStr(adoRS("sn"))
	objUser.Put "givenName", CStr(adoRS("givenName"))
	objUser.Put "displayName", CStr(adoRS("displayName"))
	objUser.Put "proxyAddresses", CStr(adoRS("proxyAddresses"))
	objUser.Put "homeMDB", CStr(adoRS("homeMDB"))
	objUser.Put "mDBUseDefaults", CStr(adoRS("mDBUseDefaults"))
	objUser.Put "mailNickname", CStr(adoRS("mailNickname"))
	objUser.Put "userAccountControl", CStr(adoRS("userAccountControl"))
	objUser.Put "sAMAccountName", CStr(adoRS("sAMAccountName"))
	objUser.Put "showInAddressBook", CStr(adoRS("showInAddressBook"))
	objUser.Put "legacyExchangeDN", CStr(adoRS("legacyExchangeDN"))
	objUser.Put "mail", CStr(adoRS("mail"))
	objUser.Put "msExchHomeServerName", CStr(adoRS("msExchHomeServerName"))
	objUser.SetInfo
 
	Set objMailbox = objUser
	MDBName = "Mailbox Store (EX01)"  'Exchange Store Name: distingiushedName
	StorageGroup = "First Storage Group"
	Server = "EX01" 'Exchange Server Name
	AdminGroup = "First Administrative Group"
	Organization = "MX (Exchange)" ' Org Name
	DomainDN = "DC=MX,DC=local"
 
				objMailbox.CreateMailbox "LDAP://CN=" & MDBName & _
				   ",CN=" & StorageGroup & _
				   ",CN=InformationStore" & _
				   ",CN=" & Server & _
				   ",CN=Servers" & _
				   ",CN=" & AdminGroup & _
				   ",CN=Administrative Groups" & _
				   ",CN=" & Organization & _
				   ",CN=Microsoft Exchange,CN=Services" & _
				   ",CN=Configuration," & DomainDN
 
objUser.SetInfo
 
Set objUser = Nothing
Loop

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
The script your designing here is way beyond my skill level, however i will provide what input i feel i have.

The error return that the script is giving you obviously doesnt like this statement:

'LDAP://DC=mx,DC=local'

Im Assuming that your domain is mx.local from the above, are you sure you are specifying the entire domain name in the LDAP statement above. Something is telling me you are missing a CN=:

'LDAP://CN=*,DC=mx,DC=local'

or is the rest of the script substituting this value in from variable input?

Author

Commented:
I've ammended my script to reflect some other posts found on this site.

I'm currently experiencing the following error, and need some help!!

Line: 112
Char: 43
Error: Expected end of statement
code: 800A0401
Source: Microsoft VBScript compliation error

Thanks
P
Option Explicit
'
Dim adoConn
Dim adoRS
Dim strPathtoTextFile
' Dim sCSVFile
Dim objNewUser
Dim objMailbox
'
' Variables needed for LDAP connection
Dim oRootLDAP
Dim oContainer
'
' Variables needed for Exchange connection
Dim oMDBName
Dim oStorageGroup
Dim oServer
Dim oAdminGroup
Dim oOrganization
Dim oDomainDN
'
' Holding variables for information import from CSV file
' Manditory & Optional objects information - http://support.microsoft.com/kb/555638
'
Dim DN
Dim objectClass
Dim givenName
Dim sAMAccountName
Dim distinguishedName
Dim name
Dim cn
Dim displayName
Dim proxyAddresses
Dim homeMDB
Dim mDBUseDefaults
Dim mailNickname
Dim userAccountControl
Dim showInAddressBook
Dim legacyExchangeDN
Dim mail
Dim msExchHomeServerName
'
' Create a connection to the Active Directory Users container.
Set oRootLDAP = GetObject("LDAP://rootDSE")
Set oContainer = GetObject("LDAP://OU=_Test OU,OU=LV-Users," & oRootLDAP.Get("defaultNamingContext"))
'
' Commands used to open the CSV file and select all of the records
Set adoConn = CreateObject("ADODB.Connection")
Set adoRS = CreateObject("ADODB.RecordSet")
' Input file location
strPathtoTextFile = "C:\Scripts\"
adoConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & strPathtoTextFile & ";" & "Extended Properties=Excel 8.0"
' 
adoRS.Open "SELECT * FROM C:\Scripts\user.xls" , adoConn
do until adoRS.EOF
' Allows processing to continue even if an error occurs (i.e. dup user)
' We put this below the CSV  and AD information since processing can
' continue with a single bad record, but not if there is a problem with
' the CSV file or AD connection
'on error resume next                                                      
'do until adoRS.EOF ' Reads the values (cells) in the CSVInputFile file.
'
' --------- Start creating user account
' Read variable information from the CSV file
' and build everything needed to create the account
'
sAMAccountName = adoRS.Fields.Item(0).value
givenName = adoRS.Fields.Item(1).value
DN = adoRS.Fields.Item(2).value
objectClass = adoRS.Fields.Item(3).value
distinguishedName = adoRS.Fields.Item(4).value
name = adoRS.Fields.Item(5).value
cn = adoRS.Fields.Item(6).value
displayName = adoRS.Fields.Item(7).value
proxyAddresses = adoRS.Fields.Item(8).value
homeMDB = adoRS.Fields.Item(9).value
mDBUseDefaults = adoRS.Fields.Item(10).value
mailNickname = adoRS.Fields.Item(11).value
userAccountControl = adoRS.Fields.Item(12).value
showInAddressBook = adoRS.Fields.Item(13).value
legacyExchangeDN = adoRS.Fields.Item(14).value
mail = adoRS.Fields.Item(15).value
msExchHomeServerName = adoRS.Fields.Item(16).value
'
'
'Build the User account
'
'objNewUser.SetInfo
Set objNewUser = oContainer.Create("user", "cn=" & adoRS("givenName"))
            objNewUser.SetPassword = "wearem1ckeymouse"
            objNewUser.AccountDisabled = False
            objNewUser.Put "sAMAccountName", CStr(adoRS("sAMAccountName"))
            ' objNewUser.Put "givenName", adoRS("givenName")
            objNewUser.Put "DN", CStr(adoRS("DN"))
            objNewUser.Put "objectClass", CStr(adoRS("objectClass"))
            objNewUser.Put "distinguishedName", CStr(adoRS("distinguishedName"))
            objNewUser.Put "name", CStr(adoRS("name"))
            objNewUser.Put "cn", CStr(adoRS("cn"))
            objNewUser.Put "displayName", CStr(adoRS("displayName"))
            objNewUser.Put "proxyAddresses", CStr(adoRS("proxyAddresses"))
            objNewUser.Put "homeMDB", CStr(adoRS("homeMDB"))
            objNewUser.Put "mDBUseDefaults", CStr(adoRS("mDBUseDefaults"))
            objNewUser.Put "mailNickname", CStr(adoRS("mailNickname"))
            objNewUser.Put "userAccountControl", CStr(adoRS("userAccountControl"))
            objNewUser.Put "showInAddressBook", CStr(adoRS("showInAddressBook"))
            objNewUser.Put "legacyExchangeDN", CStr(adoRS("legacyExchangeDN"))
            objNewUser.Put "mail", CStr(adoRS("mail"))
            objNewUser.Put "msExchHomeServerName", CStr(adoRS("msExchHomeServerName"))
'
' Create a connection to the Exchange 2003 Organisation.
'
Set objNewUser = objMailbox.CreateMailbox LDAP://CN=Mailbox Store (EX01)," & _
		"CN=First Storage Group," & _
		"CN=InformationStore," & _
		"CN=EX01," & _
		"CN=Servers," & _
		"CN=First Administrative Group," & _
		"CN=Administrative Groups," & _
		"CN=MX," & _
		"CN=Microsoft Exchange," & _
		"CN=Services," & _
		"CN=Configuration," & _
		"DC=MX," & _
		"DC=local"
'
objNewUser.SetInfo
Set objNewUser = Nothing
Loop
adoRS.Close
adoConn.Close
Wscript.Quit

Open in new window

Sorted it my self
Option Explicit
'
Dim adoConn
Dim adoRS
Dim objUser
Dim objMailbox
'
' Variables needed for LDAP connection
Dim oRootLDAP
Dim oContainer
'
' Variables needed for Exchange connection '
Dim MDBName 
Dim StorageGroup 
Dim Server 
Dim AdminGroup 
Dim Organization 
Dim DomainDN 
'
' Holding variables for information import from Excel file 
' Manditory & Optional objects information - http://support.microsoft.com/kb/555638
'
'
Dim givenName
Dim CN
Dim sn
Dim displayName
Dim sAMAccountName
Dim userPrincipalName
Dim email
Dim mDBUseDefaults
Dim mailNickname
'
' Create a connection to the Active Directory Users container.
'
Set oRootLDAP = GetObject("LDAP://rootDSE")
Set oContainer = GetObject("LDAP://OU=Accounts,OU=Test," & oRootLDAP.Get("defaultNamingContext"))
'
' Commands used to open the CSV file and select all of the records
' 
Set adoConn = CreateObject("ADODB.Connection")
Set adoRS = CreateObject("ADODB.RecordSet")
'
'
adoConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Scripts\dupe.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
adoRS.Open "SELECT * FROM [FAX$]", adoConn, 3, 3
'
'Build the User account
on error resume next
do until adoRS.EOF
Set objUser = oContainer.Create("user", "cn=" & adoRS("givenName") & " " & adoRS("sn"))
'
	objUser.Put "sAMAccountName", CStr(adoRS("sAMAccountName"))
	objUser.Put "CN", CStr(adoRS("CN"))
	objUser.Put "sn", CStr(adoRS("sn"))
	objUser.Put "givenName", CStr(adoRS("givenName"))
	objUser.Put "displayName", CStr(adoRS("displayName"))
	objUser.Put "userPrincipalName", CStr(adoRS("userPrincipalName"))
	objUser.Put "mail", CStr(adoRS("email"))
	objUser.Put "description", CStr(adoRS("description"))
	objUser.SetInfo
	objUser.userAccountControl = 512
	objUser.pwdLastSet = 0
	objUser.SetPassword "P@ssword01"
	objUser.AccountDisabled = False
	objUser.SetInfo
	WScript.Echo "New User " & displayName & " created successfully"
'
'Build the User MBX
	objuser.CreateMailbox "LDAP://CN=Mailbox Store (EX01),CN=First Storage Group,CN=InformationStore,CN=EX01,CN=Servers,CN=First Administrative Group,CN=Administrative Groups,CN=MX,CN=Microsoft Exchange,CN=Services,CN=Configuration,DC=MX,DC=local"
	objuser.AutoUpdateOnRecipientPolicy = True
	objuser.SetInfo
	objUser.Put "email", CStr(adoRS("email"))
	objuser.Put "mDBUseDefaults", CStr(adoRS("mDBUseDefaults"))
	objuser.Put "mailNickname", CStr(adoRS("mailNickname"))
	objUser.SetInfo
	WScript.Echo "New User " & mailNickname & " created successfully"
	adoRS.MoveNext
Loop
objUser.SetInfo
Set objUser = Nothing
adoRS.Close
adoConn.Close
WScript.Echo "Done."

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial