jerphillips
MIS
Has anyone set up a simple web page to administrate users with in AD? I have the VBS scripts but I want to have my helpdesk people to be able to add the user information into them with out actually editing the VBS scripts. Thanks
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Web Page user creations with VBS
- Thread starter jerphillips
- Start date
- Status
- Thread starter
- #3
jerphillips
MIS
If you can that would be great. Also the main thing I was looking for is to have a webpage my guys can use to do simple admin type stuff.
You will be getting into some hairy and complicated stuff if you are looking to tie in a properly designed web solution for admin functions. My personal feeling on it is it is not worth all the effort when you can give your help desk a custom MMC snap in and let them take things from their. just make sure you install the support tools on thier workstations first.
Anyway, here is a script that will read from Excel and populate data into AD to create a user ID.
Make sure you check the whole script and edit for your companies LDAP paths. If you are unsure about the full LDAP path for your server, install ADSIEdit from the Win2K CD. It is part of the support tools in the support directory. ADSIEdit is an MMC Snap-In and it can show you your LDAP Path.
This script will set the initial password to be the same as the login.
'==========================================================================
'
' NAME: createNewUsers.vbs
'
' AUTHOR: Mark D. MacLachlan , The Spider's Parlor
' DATE : 04/21/2003
' COPYRIGHT 2003, All Rights Reserved
'
' COMMENT:
' Creates users based on Excel spreadsheet information.
' Excel spreadsheet formatting:
' Row 1 to have headings
' Column A (1) to have login
' Column B (2) to have first_name
' Column C (3) to have last_name
' Column D (4) to have phone
' Column E (5) to have extension
' Column F (6) to have fax
' Column G (7) to have email
' Column H (8) to have street
' Column I (9) to have suite
' Column J (10) to have city
' Column K (11) to have state
' Column L (12) to have zip
'=====================================
set x = getobject(,"excel.application"
r = 2
Const c = "US"
Const co = "UNITED STATES"
Const company = "Company Name"
Const ou_name = "Users"
do until len(x.cells(r, 1).value) = 0
login = x.cells(r, 1).value
first_name = x.cells(r, 2).value
last_name = x.cells(r, 3).value
phone = x.cells(r, 4).value
extension = x.cells(r, 5).value
fax = x.cells(r, 6).value
email = x.cells(r, 7).value
street = x.cells(r, 8).value
suite = x.cells(r, 9).value
city = x.cells(r, 10).value
state = x.cells(r, 11).value
zip =cstr(x.cells(r, 12).value)
if len(first_name) = 0 then
first_name = " "
fn=0
end if
if len(last_name) = 0 then
last_name = " "
ln=0
end if
if len(phone) = 0 then
phone = " "
end if
if len(extension) = 0 then
extension = " "
end if
if len(fax) = 0 then
extension = " "
end if
if len(email) = 0 then
email = " "
end if
if len(street) = 0 then
street = " "
end if
if len(suite) = 0 then
suite = " "
end if
if len(city) = 0 then
city = " "
end if
if len(state) = 0 then
state = " "
end if
if len(zip) = 0 then
zip = " "
end if
set o = getobject("LDAP://SERVER.company.com.local/CN=Users,DC=company,DC=com,DC=local"
if err then
x.cells(r, 15).value = err.number & ": " & err.description
err.Clear
end if
if fn + ln = 0 then
fullName = x.cells(r, 1).value
else
fullName = first_name & " " & last_name
end if
Set oUser = o.Create("user","CN=" & fullName)
oUser.Put "sAMAccountName", login
oUser.SetInfo
oUser.Put "userPrincipalName", email
oUser.SetInfo
oUser.Put "c", c
oUser.Put "co", co
oUser.Put "company", company
oUser.Put "displayName", first_name & " " & last_name
oUser.Put "facsimileTelephoneNumber", fax
oUser.Put "givenName", first_name
oUser.SetInfo
oUser.Put "l", "Phoenix"
oUser.Put "mail", email
oUser.Put "mailNickname", login
'oUser.Put "name", first_name & " " & last_name
oUser.Put "otherTelephone", "Ext. "& extension
oUser.Put "postalCode", zip
oUser.SetInfo
oUser.Put "sn", last_name
oUser.Put "st", state
oUser.Put "streetAddress", street & vbCrLf & Suite
oUser.Put "telephoneNumber", phone & "Ext. "& extension
oUser.SetPassword login
oUser.SetInfo
oUser.AccountDisabled = False
oUser.SetInfo
If Err.Number <> 0 And Err.Number <> -2147019886 Then
x.cells(r, 15).value = err.number & ": " & "ID creation error"
Else
x.cells(r, 15).value = "created"
end if
r = r + 1
set objOU = Nothing
set oUser = Nothing
set o = nothing
Err.Clear
Loop
set x = nothing
msgbox "done"
Anyway, here is a script that will read from Excel and populate data into AD to create a user ID.
Make sure you check the whole script and edit for your companies LDAP paths. If you are unsure about the full LDAP path for your server, install ADSIEdit from the Win2K CD. It is part of the support tools in the support directory. ADSIEdit is an MMC Snap-In and it can show you your LDAP Path.
This script will set the initial password to be the same as the login.
'==========================================================================
'
' NAME: createNewUsers.vbs
'
' AUTHOR: Mark D. MacLachlan , The Spider's Parlor
' DATE : 04/21/2003
' COPYRIGHT 2003, All Rights Reserved
'
' COMMENT:
' Creates users based on Excel spreadsheet information.
' Excel spreadsheet formatting:
' Row 1 to have headings
' Column A (1) to have login
' Column B (2) to have first_name
' Column C (3) to have last_name
' Column D (4) to have phone
' Column E (5) to have extension
' Column F (6) to have fax
' Column G (7) to have email
' Column H (8) to have street
' Column I (9) to have suite
' Column J (10) to have city
' Column K (11) to have state
' Column L (12) to have zip
'=====================================
set x = getobject(,"excel.application"
r = 2
Const c = "US"
Const co = "UNITED STATES"
Const company = "Company Name"
Const ou_name = "Users"
do until len(x.cells(r, 1).value) = 0
login = x.cells(r, 1).value
first_name = x.cells(r, 2).value
last_name = x.cells(r, 3).value
phone = x.cells(r, 4).value
extension = x.cells(r, 5).value
fax = x.cells(r, 6).value
email = x.cells(r, 7).value
street = x.cells(r, 8).value
suite = x.cells(r, 9).value
city = x.cells(r, 10).value
state = x.cells(r, 11).value
zip =cstr(x.cells(r, 12).value)
if len(first_name) = 0 then
first_name = " "
fn=0
end if
if len(last_name) = 0 then
last_name = " "
ln=0
end if
if len(phone) = 0 then
phone = " "
end if
if len(extension) = 0 then
extension = " "
end if
if len(fax) = 0 then
extension = " "
end if
if len(email) = 0 then
email = " "
end if
if len(street) = 0 then
street = " "
end if
if len(suite) = 0 then
suite = " "
end if
if len(city) = 0 then
city = " "
end if
if len(state) = 0 then
state = " "
end if
if len(zip) = 0 then
zip = " "
end if
set o = getobject("LDAP://SERVER.company.com.local/CN=Users,DC=company,DC=com,DC=local"
if err then
x.cells(r, 15).value = err.number & ": " & err.description
err.Clear
end if
if fn + ln = 0 then
fullName = x.cells(r, 1).value
else
fullName = first_name & " " & last_name
end if
Set oUser = o.Create("user","CN=" & fullName)
oUser.Put "sAMAccountName", login
oUser.SetInfo
oUser.Put "userPrincipalName", email
oUser.SetInfo
oUser.Put "c", c
oUser.Put "co", co
oUser.Put "company", company
oUser.Put "displayName", first_name & " " & last_name
oUser.Put "facsimileTelephoneNumber", fax
oUser.Put "givenName", first_name
oUser.SetInfo
oUser.Put "l", "Phoenix"
oUser.Put "mail", email
oUser.Put "mailNickname", login
'oUser.Put "name", first_name & " " & last_name
oUser.Put "otherTelephone", "Ext. "& extension
oUser.Put "postalCode", zip
oUser.SetInfo
oUser.Put "sn", last_name
oUser.Put "st", state
oUser.Put "streetAddress", street & vbCrLf & Suite
oUser.Put "telephoneNumber", phone & "Ext. "& extension
oUser.SetPassword login
oUser.SetInfo
oUser.AccountDisabled = False
oUser.SetInfo
If Err.Number <> 0 And Err.Number <> -2147019886 Then
x.cells(r, 15).value = err.number & ": " & "ID creation error"
Else
x.cells(r, 15).value = "created"
end if
r = r + 1
set objOU = Nothing
set oUser = Nothing
set o = nothing
Err.Clear
Loop
set x = nothing
msgbox "done"
- Thread starter
- #5
jerphillips
MIS
Ok coping and modifing your script I am now running into some gotcha's that are a little beyond me. (If you haven't notice I'm not a VBScriptor.
This is my script I'm trying to use.
' NAME: createNewUsers.vbs
' COMMENT:
' Creates users based on Excel spreadsheet information.
' Excel spreadsheet formatting:
' Row 1 to have headings
' Column A (1) to have login
' Column B (2) to have first_name
' Column C (3) to have last_name
' Column D (4) to have phone
' Column E (5) to have email
' Column F (6) to have street
' Column G (7) to have suite
' Column H (8) to have city
' Column I (9) to have state
' Column J (10) to have zip
' Column K (11) to have discription
'=====================================
set x = getobject(,"excel.application"
r = 2
Const c = "US"
Const co = "UNITED STATES"
Const company = "Company"
Const ou_name = "Users"
do until len(x.cells(r, 1).value) = 0
login = x.cells(r, 1).value
first_name = x.cells(r, 2).value
last_name = x.cells(r, 3).value
phone = x.cells(r, 4).value
email = x.cells(r, 5).value
street = x.cells(r, 6).value
suite = x.cells(r, 7).value
city = x.cells(r, 8).value
state = x.cells(r, 9).value
zip =cstr(x.cells(r, 10).value)
discription = x.cells(r, 11).value
if len(first_name) = 0 then
first_name = " "
fn=0
end if
if len(last_name) = 0 then
last_name = " "
ln=0
end if
if len(phone) = 0 then
phone = " "
end if
if len(email) = 0 then
email = " "
end if
if len(street) = 0 then
street = " "
end if
if len(suite) = 0 then
suite = " "
end if
if len(city) = 0 then
city = " "
end if
if len(state) = 0 then
state = " "
end if
if len(zip) = 0 then
zip = " "
end if
if len(discription) = 0 then
discription = " "
end if
set o = getobject("LDAP://domaincontroler.domain.com/CN=Users,DC=domain,DC=com"
if err then
x.cells(r, 15).value = err.number & ": " & err.description
err.Clear
end if
if fn + ln = 0 then
fullName = x.cells(r, 1).value
else
fullName = first_name & " " & last_name
end if
On Error Resume Next
Set oUser = o.Create("user","CN=" & fullName)
oUser.Put "sAMAccountName", login
oUser.SetInfo
oUser.Put "userPrincipalName", email
oUser.SetInfo
oUser.Put "c", c
oUser.Put "co", co
oUser.Put "company", company
oUser.Put "displayName", first_name & " " & last_name
oUser.Put "givenName", first_name
oUser.SetInfo
'oUser.Put "l", "City"
oUser.Put "mail", email
'oUser.Put "mailNickname", login
oUser.Put "name", first_name & " " & last_name
'oUser.Put "postalCode", zip
oUser.SetInfo
oUser.Put "sn", last_name
oUser.Put "st", state
oUser.Put "streetAddress", street & vbCrLf & Suite
oUser.Put "telephoneNumber", phone & "Ext. "& extension
oUser.SetPassword "Changeit1"
oUser.SetInfo
oUser.AccountDisabled = False
oUser.SetInfo
objUser.Put "scriptPath", "logon.bat"
objUser.Put "homeDirectory", "\\Fileserver\Home\%username%"
objUser.Put "homeDrive", "H:"
objUser.SetInfo
If Err.Number <> 0 And Err.Number <> -2147019886 Then
x.cells(r, 15).value = err.number & ": " & "ID creation error"
Else
x.cells(r, 15).value = "created"
end if
r = r + 1
set objOU = Nothing
set oUser = Nothing
set o = nothing
Err.Clear
Loop
set x = nothing
msgbox "done"
Also Thanks for all your help
This is my script I'm trying to use.
' NAME: createNewUsers.vbs
' COMMENT:
' Creates users based on Excel spreadsheet information.
' Excel spreadsheet formatting:
' Row 1 to have headings
' Column A (1) to have login
' Column B (2) to have first_name
' Column C (3) to have last_name
' Column D (4) to have phone
' Column E (5) to have email
' Column F (6) to have street
' Column G (7) to have suite
' Column H (8) to have city
' Column I (9) to have state
' Column J (10) to have zip
' Column K (11) to have discription
'=====================================
set x = getobject(,"excel.application"
r = 2
Const c = "US"
Const co = "UNITED STATES"
Const company = "Company"
Const ou_name = "Users"
do until len(x.cells(r, 1).value) = 0
login = x.cells(r, 1).value
first_name = x.cells(r, 2).value
last_name = x.cells(r, 3).value
phone = x.cells(r, 4).value
email = x.cells(r, 5).value
street = x.cells(r, 6).value
suite = x.cells(r, 7).value
city = x.cells(r, 8).value
state = x.cells(r, 9).value
zip =cstr(x.cells(r, 10).value)
discription = x.cells(r, 11).value
if len(first_name) = 0 then
first_name = " "
fn=0
end if
if len(last_name) = 0 then
last_name = " "
ln=0
end if
if len(phone) = 0 then
phone = " "
end if
if len(email) = 0 then
email = " "
end if
if len(street) = 0 then
street = " "
end if
if len(suite) = 0 then
suite = " "
end if
if len(city) = 0 then
city = " "
end if
if len(state) = 0 then
state = " "
end if
if len(zip) = 0 then
zip = " "
end if
if len(discription) = 0 then
discription = " "
end if
set o = getobject("LDAP://domaincontroler.domain.com/CN=Users,DC=domain,DC=com"
if err then
x.cells(r, 15).value = err.number & ": " & err.description
err.Clear
end if
if fn + ln = 0 then
fullName = x.cells(r, 1).value
else
fullName = first_name & " " & last_name
end if
On Error Resume Next
Set oUser = o.Create("user","CN=" & fullName)
oUser.Put "sAMAccountName", login
oUser.SetInfo
oUser.Put "userPrincipalName", email
oUser.SetInfo
oUser.Put "c", c
oUser.Put "co", co
oUser.Put "company", company
oUser.Put "displayName", first_name & " " & last_name
oUser.Put "givenName", first_name
oUser.SetInfo
'oUser.Put "l", "City"
oUser.Put "mail", email
'oUser.Put "mailNickname", login
oUser.Put "name", first_name & " " & last_name
'oUser.Put "postalCode", zip
oUser.SetInfo
oUser.Put "sn", last_name
oUser.Put "st", state
oUser.Put "streetAddress", street & vbCrLf & Suite
oUser.Put "telephoneNumber", phone & "Ext. "& extension
oUser.SetPassword "Changeit1"
oUser.SetInfo
oUser.AccountDisabled = False
oUser.SetInfo
objUser.Put "scriptPath", "logon.bat"
objUser.Put "homeDirectory", "\\Fileserver\Home\%username%"
objUser.Put "homeDrive", "H:"
objUser.SetInfo
If Err.Number <> 0 And Err.Number <> -2147019886 Then
x.cells(r, 15).value = err.number & ": " & "ID creation error"
Else
x.cells(r, 15).value = "created"
end if
r = r + 1
set objOU = Nothing
set oUser = Nothing
set o = nothing
Err.Clear
Loop
set x = nothing
msgbox "done"
Also Thanks for all your help
What errors are you getting? Have you modified each of the following lines with real data?
Const company = "Company"
Set o = getobject("LDAP://domaincontroler.domain.com/CN=Users,DC=domain,DC=com"
In the section you added:
objUser.Put "scriptPath", "logon.bat"
objUser.Put "homeDirectory", "\\Fileserver\Home\%username%"
objUser.Put "homeDrive", "H:"
objUser.SetInfo
You need to keep with the same naming convention as the rest of the script. Change your objUser to oUser.
Also, I would suggest you make the following change:
objUser.Put "homeDirectory", "\\Fileserver\Home\" & login
Const company = "Company"
Set o = getobject("LDAP://domaincontroler.domain.com/CN=Users,DC=domain,DC=com"
In the section you added:
objUser.Put "scriptPath", "logon.bat"
objUser.Put "homeDirectory", "\\Fileserver\Home\%username%"
objUser.Put "homeDrive", "H:"
objUser.SetInfo
You need to keep with the same naming convention as the rest of the script. Change your objUser to oUser.
Also, I would suggest you make the following change:
objUser.Put "homeDirectory", "\\Fileserver\Home\" & login
- Thread starter
- #7
jerphillips
MIS
I'm getting:
Script H:\scripts\CreateNewUsers.vbs
Line: 133
Char: 5
Error: The Directory services cannot perform the requested operation on the RDN attribute of an object
Code: 80072016
Source: (Null)
I removed the real data on teh script to protect some sensitive information.
Thanks again for the help.
Script H:\scripts\CreateNewUsers.vbs
Line: 133
Char: 5
Error: The Directory services cannot perform the requested operation on the RDN attribute of an object
Code: 80072016
Source: (Null)
I removed the real data on teh script to protect some sensitive information.
Thanks again for the help.
OK, sounds like you are trying to fill AD with a null value. On your script, which is line 133? (hard to tell because I don't know if you started on line one or had some blank returns, etc)
You will notice at the top of my script that I have set all values that are a length of zero to be equal to " " which gives them a value of a blank space which is different from a null value.
You will notice at the top of my script that I have set all values that are a length of zero to be equal to " " which gives them a value of a blank space which is different from a null value.
- Thread starter
- #9
jerphillips
MIS
The line in question is this:
oUser.SetInfo
The items above it Follows:
Set oUser = o.Create("user","CN=" & fullName)
oUser.Put "sAMAccountName", login
oUser.SetInfo
oUser.Put "userPrincipalName", email
oUser.SetInfo
oUser.Put "c", c
oUser.Put "co", co
oUser.Put "company", company
oUser.Put "displayName", first_name & " " & last_name
oUser.Put "givenName", first_name
oUser.SetInfo
oUser.Put "l", "Arlington"
oUser.Put "description", description
oUser.Put "mail", email
oUser.Put "name", first_name & " " & last_name
oUser.Put "postalCode", zip
oUser.SetInfo
The items above it Follows:
Set oUser = o.Create("user","CN=" & fullName)
oUser.Put "sAMAccountName", login
oUser.SetInfo
oUser.Put "userPrincipalName", email
oUser.SetInfo
oUser.Put "c", c
oUser.Put "co", co
oUser.Put "company", company
oUser.Put "displayName", first_name & " " & last_name
oUser.Put "givenName", first_name
oUser.SetInfo
oUser.Put "l", "Arlington"
oUser.Put "description", description
oUser.Put "mail", email
oUser.Put "name", first_name & " " & last_name
oUser.Put "postalCode", zip
Cool we are getting closer.
Your problem is somewhere in here
oUser.Put "l", "Arlington"
oUser.Put "description", description
oUser.Put "mail", email
oUser.Put "name", first_name & " " & last_name
oUser.Put "postalCode", zip
The SetInfo above this section did not give you an error, so you need to check the values for each of these. I'd suggest a quick test using an additional SetInfo like this:
oUser.Put "l", "Arlington"
oUser.Put "description", description
oUser.SetInfo
oUser.Put "mail", email
oUser.Put "name", first_name & " " & last_name
oUser.Put "postalCode", zip
Run the script again and if it fails at this new SetInfo you know the problem is with the 'Arlington' value (very doubtful since we can see the data value) or with the description. If the failure happens in the same place, move this SetInfo down more and re-run until you find the part that is causing the error.
If you want to go crazy with it, you can do a SetInfo after each line and just see where it dies.
Though frustrating, you are getting some good troubleshooting experience, so look at it from that point of view.
I hope you find this post helpful. Please let me know if it was.
Regards,
Mark
Your problem is somewhere in here
oUser.Put "l", "Arlington"
oUser.Put "description", description
oUser.Put "mail", email
oUser.Put "name", first_name & " " & last_name
oUser.Put "postalCode", zip
The SetInfo above this section did not give you an error, so you need to check the values for each of these. I'd suggest a quick test using an additional SetInfo like this:
oUser.Put "l", "Arlington"
oUser.Put "description", description
oUser.SetInfo
oUser.Put "mail", email
oUser.Put "name", first_name & " " & last_name
oUser.Put "postalCode", zip
Run the script again and if it fails at this new SetInfo you know the problem is with the 'Arlington' value (very doubtful since we can see the data value) or with the description. If the failure happens in the same place, move this SetInfo down more and re-run until you find the part that is causing the error.
If you want to go crazy with it, you can do a SetInfo after each line and just see where it dies.
Though frustrating, you are getting some good troubleshooting experience, so look at it from that point of view.
I hope you find this post helpful. Please let me know if it was.
Regards,
Mark
- Thread starter
- #11
jerphillips
MIS
I went crazy and added the oUser.Setinfo after each entry and it errored out on:
oUser.Put "name", first_name & " " & last_name
oUser.SetInfo
Looking at the script and the user account I don't think I really need to have it there. All the information was populated properly. Now all I have to do is add the terminal services profile to it and I'll be set. Thanks again for your help.
Jeremy
oUser.Put "name", first_name & " " & last_name
oUser.SetInfo
Looking at the script and the user account I don't think I really need to have it there. All the information was populated properly. Now all I have to do is add the terminal services profile to it and I'll be set. Thanks again for your help.
Jeremy
Sounds like you are satisfied which is great, but if you want to solve that issue take a look in your excel spreadsheet. Looks like you must not have some data in the name fields. Also check your script and make sure that it checks at the top if the length of either field is blank it should set the value to a space " ".
I hope you find this post helpful. Please let me know if it was.
Regards,
Mark
I hope you find this post helpful. Please let me know if it was.
Regards,
Mark
- Thread starter
- #13
jerphillips
MIS
Actually I think it's having an issue modifing the RDN properties of the AD user. Which is fine cause the field is filled in by AD. on a side note I found the VBScript for configuring a users terminal service profile. The only problem is that it needs to run off of a Windows 2003 box.
Const Enabled = 1
Const Disabled = 0
Set objUser = GetObject _
("LDAP://cn=MyerKen,ou=Management,dc=NA,dc=fabrikam,dc=com"
objUser.TerminalServicesProfilePath = "TerminalServicesProfilePath"
objUser.TerminalServicesHomeDirectory = "TerminalServicesHomeDirectory"
objUser.TerminalServicesHomeDrive = "W:"
objUser.AllowLogon = Enabled
objUser.SetInfo
Const Enabled = 1
Const Disabled = 0
Set objUser = GetObject _
("LDAP://cn=MyerKen,ou=Management,dc=NA,dc=fabrikam,dc=com"
objUser.TerminalServicesProfilePath = "TerminalServicesProfilePath"
objUser.TerminalServicesHomeDirectory = "TerminalServicesHomeDirectory"
objUser.TerminalServicesHomeDrive = "W:"
objUser.AllowLogon = Enabled
objUser.SetInfo
Hi,
I am new to Vbscript and I got some errors when I run this script.
First, I have to get around to get to the spread sheet
Second I get SetInfo error from the first line
Third the I get syntax error and invalid property errors with the oUser.SetPasswor login or oUser.AccountDisabled = False.
What is the objOU variable at the bottom?
I have used the ADSIEdit tool and everything is correct in the LAPD:// line
strExcelPath = "c:\newuser.xls"
set objExel = createObject("Excel.Application")
objExcel.workbook.open strExcelPath
set objSheet = objExcel.ActiveWorkbook.Worksheets(1)
'
' etc...
'
' when using the loop to read
do until len(objSheet.cells(r, 1).Value) = 0
login = objSheet.cells(r, 1).value
'
' etc..
I could not bind or write to the object any help. Do you have the full script?
Thanks
I am new to Vbscript and I got some errors when I run this script.
First, I have to get around to get to the spread sheet
Second I get SetInfo error from the first line
Third the I get syntax error and invalid property errors with the oUser.SetPasswor login or oUser.AccountDisabled = False.
What is the objOU variable at the bottom?
I have used the ADSIEdit tool and everything is correct in the LAPD:// line
strExcelPath = "c:\newuser.xls"
set objExel = createObject("Excel.Application")
objExcel.workbook.open strExcelPath
set objSheet = objExcel.ActiveWorkbook.Worksheets(1)
'
' etc...
'
' when using the loop to read
do until len(objSheet.cells(r, 1).Value) = 0
login = objSheet.cells(r, 1).value
'
' etc..
I could not bind or write to the object any help. Do you have the full script?
Thanks
The fourth post in this thread is the full working script. You will simply need to edit it for your environment.
The script will fail if you don't have the Excel spreadsheet. I am uncertain if you are saying you have not done that part yet.
Please ignore setting ObjOU to nothing. That was a remnant from another script that I based this off of. My bad. :-(
I hope you find this post helpful. Please let me know if it was.
Regards,
Mark
The script will fail if you don't have the Excel spreadsheet. I am uncertain if you are saying you have not done that part yet.
Please ignore setting ObjOU to nothing. That was a remnant from another script that I based this off of. My bad. :-(
I hope you find this post helpful. Please let me know if it was.
Regards,
Mark
Thanks for your prompt reply.
I have already created the spread sheet which suppose to start from row 2, althoug I have tried to trim all the user variables, but no luck e.g.
login = Trim(objSheet.cells(r, 1).value)
I am using values from excel spreadsheet based on the variables in your script.
I am using 2003 server AD.
Thanks I appriciate your help.
I have already created the spread sheet which suppose to start from row 2, althoug I have tried to trim all the user variables, but no luck e.g.
login = Trim(objSheet.cells(r, 1).value)
I am using values from excel spreadsheet based on the variables in your script.
I am using 2003 server AD.
Thanks I appriciate your help.
I believe you are having problems because you are trying to do the trim before it is actually a string in VBScript. Try this instead.
login = objSheet.cells(r, 1).value
login = Trim(login)
I hope you find this post helpful. Please let me know if it was.
Regards,
Mark
login = objSheet.cells(r, 1).value
login = Trim(login)
I hope you find this post helpful. Please let me know if it was.
Regards,
Mark
farkel, I don't know what you are looking for. As posted above the 4the post int his thread IS the full script.
Why don't you post what you are trying to use.
CONST Ou_Name = "Users" is specifying to creat a Constant Variable (one whose value can't change) andgive it the value of Users.
This is done because the default location to store users in AD is in the users container.
I hope you find this post helpful. Please let me know if it was.
Regards,
Mark
Why don't you post what you are trying to use.
CONST Ou_Name = "Users" is specifying to creat a Constant Variable (one whose value can't change) andgive it the value of Users.
This is done because the default location to store users in AD is in the users container.
I hope you find this post helpful. Please let me know if it was.
Regards,
Mark
The following is the full script. However for the CONST Ou_Name, I mean I didn't see how it has been used in the script except when it has been declared as a constant.
' Creates users based on Excel spreadsheet information.
' Excel spreadsheet formatting:
' Row 1 to have headings
' Column A (1) to have login
' Column B (2) to have first_name
' Column C (3) to have last_name
' Column D (4) to have phone
' Column E (5) to have extension
' Column F (6) to have fax
' Column G (7) to have email
' Column H (8) to have street
' Column I (9) to have suite
' Column J (10) to have city
' Column K (11) to have state
' Column L (12) to have zip
'=====================================
' Option Explicit
Dim login, first_name, last_name, phone, extension, fax, fullName
Dim email, street, suite, city, state, zip
Dim x,o, r
Dim strExcelPath, objExcel, objSheet, objOU
Dim fn, ln
Dim oUser
Dim oRoot, oDomain
strExcelPath = "C:\NewUsers2.xls"
set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.open strExcelPath
set objSheet = objExcel.ActiveWorkbook.Worksheets(1)
r = 2
Const c = "US"
Const co = "UNITED STATES"
Const company = "ECE"
Const ou_name = "Users"
do until len(ObjSheet.cells(r, 1).value) = 0
login = objSheet.cells(r, 1).value
login = Trim(login)
first_name = objSheet.cells(r, 2).value
first_name = Trim(first_name)
last_name = objSheet.cells(r, 3).value
last_name = Trim(last_name)
phone = objSheet.cells(r, 4).value
phone = Trim(phone)
extension = objSheet.cells(r, 5).value
extension = Trim(extension)
fax = objSheet.cells(r, 6).value
fax = Trim(fax)
email = objSheet.cells(r, 7).value
email = Trim(email)
street = objSheet.cells(r, 8).value
street = Trim(street)
suite = objSheet.cells(r, 9).value
suite = Trim(street)
city = objSheet.cells(r, 10).value
city = Trim(city)
state = objSheet.cells(r, 11).value
state = Trim(state)
zip = cstr(objSheet.cells(r, 12).value)
zip = Trim(zip)
if len(first_name) = 0 then
first_name = " "
fn=0
end if
if len(last_name) = 0 then
last_name = " "
ln=0
end if
if len(phone) = 0 then
phone = " "
end if
if len(extension) = 0 then
extension = " "
end if
if len(fax) = 0 then
extension = " "
end if
if len(email) = 0 then
email = " "
end if
if len(street) = 0 then
street = " "
end if
if len(suite) = 0 then
suite = " "
end if
if len(city) = 0 then
city = " "
end if
if len(state) = 0 then
state = " "
end if
if len(zip) = 0 then
zip = " "
end if
set o = getobject("LDAP://myserver.ece.tce.edu/CN=Users,DC=ece,DC=tce,DC=edu")
if err then
x.cells(r, 15).value = err.number & ": " & err.description
err.Clear
end if
if fn + ln = 0 then
fullName = objSheet.cells(r, 1).value
else
fullName = first_name & " " & last_name
end if
Set oUser = o.Create("user","CN=" & fullName)
oUser.Put "sAMAccountName", login
' oUser.SetInfo
oUser.Put "userPrincipalName", email
' oUser.SetInfo
oUser.Put "c", c
oUser.Put "co", co
oUser.Put "company", company
oUser.Put "displayName", first_name & " " & last_name
' oUser.Put "facsimileTelephoneNumber", fax
oUser.Put "givenName", first_name
' oUser.SetInfo
oUser.Put "l", "Phoenix"
oUser.Put "mail", email
oUser.Put "mailNickname", login
oUser.Put "name", first_name & " " & last_name
oUser.Put "otherTelephone", "Ext. "& extension
oUser.Put "postalCode", zip
' oUser.SetInfo
oUser.Put "sn", last_name
oUser.Put "st", state
oUser.Put "streetAddress", street & vbCrLf & Suite
oUser.Put "telephoneNumber", phone & "Ext. "& extension
oUser.SetPassword login
' oUser.SetInfo
' oUser.AccountDisabled = False
' oUser.Put "AccountDisabled", False
oUser.AccountDisabled = False
oUser.SetInfo
If Err.Number <> 0 And Err.Number <> -2147019886 Then
x.cells(r, 15).value = err.number & ": " & "ID creation error"
Else
objSheet.cells(r, 15).value = "created"
end if
r = r + 1
set objOU = Nothing
set oUser = Nothing
set o = nothing
Err.Clear
Loop
set x = nothing
msgbox "done"
If I use oUser.SetInfo any where between or under the oUser.Put lines, then I get the following error " The specified directory service attribute or value does not exist".
Thanks
Far
' Creates users based on Excel spreadsheet information.
' Excel spreadsheet formatting:
' Row 1 to have headings
' Column A (1) to have login
' Column B (2) to have first_name
' Column C (3) to have last_name
' Column D (4) to have phone
' Column E (5) to have extension
' Column F (6) to have fax
' Column G (7) to have email
' Column H (8) to have street
' Column I (9) to have suite
' Column J (10) to have city
' Column K (11) to have state
' Column L (12) to have zip
'=====================================
' Option Explicit
Dim login, first_name, last_name, phone, extension, fax, fullName
Dim email, street, suite, city, state, zip
Dim x,o, r
Dim strExcelPath, objExcel, objSheet, objOU
Dim fn, ln
Dim oUser
Dim oRoot, oDomain
strExcelPath = "C:\NewUsers2.xls"
set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.open strExcelPath
set objSheet = objExcel.ActiveWorkbook.Worksheets(1)
r = 2
Const c = "US"
Const co = "UNITED STATES"
Const company = "ECE"
Const ou_name = "Users"
do until len(ObjSheet.cells(r, 1).value) = 0
login = objSheet.cells(r, 1).value
login = Trim(login)
first_name = objSheet.cells(r, 2).value
first_name = Trim(first_name)
last_name = objSheet.cells(r, 3).value
last_name = Trim(last_name)
phone = objSheet.cells(r, 4).value
phone = Trim(phone)
extension = objSheet.cells(r, 5).value
extension = Trim(extension)
fax = objSheet.cells(r, 6).value
fax = Trim(fax)
email = objSheet.cells(r, 7).value
email = Trim(email)
street = objSheet.cells(r, 8).value
street = Trim(street)
suite = objSheet.cells(r, 9).value
suite = Trim(street)
city = objSheet.cells(r, 10).value
city = Trim(city)
state = objSheet.cells(r, 11).value
state = Trim(state)
zip = cstr(objSheet.cells(r, 12).value)
zip = Trim(zip)
if len(first_name) = 0 then
first_name = " "
fn=0
end if
if len(last_name) = 0 then
last_name = " "
ln=0
end if
if len(phone) = 0 then
phone = " "
end if
if len(extension) = 0 then
extension = " "
end if
if len(fax) = 0 then
extension = " "
end if
if len(email) = 0 then
email = " "
end if
if len(street) = 0 then
street = " "
end if
if len(suite) = 0 then
suite = " "
end if
if len(city) = 0 then
city = " "
end if
if len(state) = 0 then
state = " "
end if
if len(zip) = 0 then
zip = " "
end if
set o = getobject("LDAP://myserver.ece.tce.edu/CN=Users,DC=ece,DC=tce,DC=edu")
if err then
x.cells(r, 15).value = err.number & ": " & err.description
err.Clear
end if
if fn + ln = 0 then
fullName = objSheet.cells(r, 1).value
else
fullName = first_name & " " & last_name
end if
Set oUser = o.Create("user","CN=" & fullName)
oUser.Put "sAMAccountName", login
' oUser.SetInfo
oUser.Put "userPrincipalName", email
' oUser.SetInfo
oUser.Put "c", c
oUser.Put "co", co
oUser.Put "company", company
oUser.Put "displayName", first_name & " " & last_name
' oUser.Put "facsimileTelephoneNumber", fax
oUser.Put "givenName", first_name
' oUser.SetInfo
oUser.Put "l", "Phoenix"
oUser.Put "mail", email
oUser.Put "mailNickname", login
oUser.Put "name", first_name & " " & last_name
oUser.Put "otherTelephone", "Ext. "& extension
oUser.Put "postalCode", zip
' oUser.SetInfo
oUser.Put "sn", last_name
oUser.Put "st", state
oUser.Put "streetAddress", street & vbCrLf & Suite
oUser.Put "telephoneNumber", phone & "Ext. "& extension
oUser.SetPassword login
' oUser.SetInfo
' oUser.AccountDisabled = False
' oUser.Put "AccountDisabled", False
oUser.AccountDisabled = False
oUser.SetInfo
If Err.Number <> 0 And Err.Number <> -2147019886 Then
x.cells(r, 15).value = err.number & ": " & "ID creation error"
Else
objSheet.cells(r, 15).value = "created"
end if
r = r + 1
set objOU = Nothing
set oUser = Nothing
set o = nothing
Err.Clear
Loop
set x = nothing
msgbox "done"
If I use oUser.SetInfo any where between or under the oUser.Put lines, then I get the following error " The specified directory service attribute or value does not exist".
Thanks
Far
- Status