stiana
asked on
VBscript enable and update password if user exists in excel
hi,
hi have this script for importing users from a excel sheet, my only problem is when script finds existing user in AD from excel sheet it stops and i would like the script to enable and changing the attributes in AD from excel, fullname,username,password ,office,ph one,homedi rectory,pr ofilepath.
this is my script:
If LCase(Right(Wscript.FullNa me, 11)) = "wscript.exe" Then
strPath = Wscript.ScriptFullName
strCommand = "%comspec% /k cscript """ & strPath & """"
Set objShell = CreateObject("Wscript.Shel l")
objShell.Run(strCommand), 1, True
Wscript.Quit
End If
' Bind to Active Directory.
Set objRootLDAP = GetObject("LDAP://rootDSE" )
' CONFIGURATION PARAMETERS FOR THE SCRIPT
strExcelFile = Replace(WScript.ScriptFull Name, WScript.ScriptName, "") & "Users_Short.xls"
strOUPath = "OU=studenter," & objRootLDAP.Get("defaultNa mingContex t")
' END CONFIGURATION PARAMETERS
Const xlUp = -4162
Const ADS_UF_DONT_EXPIRE_PASSWD = &H10000
Set objExcel = CreateObject("Excel.Applic ation")
objExcel.Visible = True
objExcel.Workbooks.Open strExcelFile
Set objNetwork = CreateObject("WScript.Netw ork")
strDomainName = objNetwork.UserDomain
For intRow = 2 To objExcel.ActiveSheet.Cells (65536, "A").End(xlUp).Row
strFullName = Trim(Replace(objExcel.Acti veSheet.Ce lls(intRow , "A").Value, ",", ""))
strUserName = Trim(objExcel.ActiveSheet. Cells(intR ow, "B").Value)
strPassword = Trim(objExcel.ActiveSheet. Cells(intR ow, "C").Value)
strHomeDirectory = Trim(Replace(objExcel.Acti veSheet.Ce lls(intRow , "F").Value, "/", "\"))
strprofileDirectory = Trim(Replace(objExcel.Acti veSheet.Ce lls(intRow , "G").Value, "/", "\"))
strOffice = Trim(Replace(objExcel.Acti veSheet.Ce lls(intRow , "D").Value, "/", "\"))
strphone = Trim(Replace(objExcel.Acti veSheet.Ce lls(intRow , "E").Value, "/", "\"))
strFirstName = Trim(left(strFullName, InStrRev(strFullName, " ") - 1))
strLastName = Trim(left(strFullName, InStrRev(strFullName, " ") + 1))
If strFullName <> "" And strUserName <> "" Then
'WScript.Echo "About to create:" & VbCrLf &_
' strFullName & VbCrLf &_
' strFirstName & VbCrLf &_
' strLastName & VbCrLf & _
' strUserName & VbCrLf &_
' strPassword & VbCrLf &_
' strprofileDirectory & VbCrLf &_
' strHomeDirectory & VbCrLf &_
' strOffice & VbCrLf &_
' strphone & VbCrLf &_
' "LDAP://" & strOUPath
' This will add the user to eg. Domain.Local\Users
Set objContainer = GetObject("LDAP://" & strOUPath)
' Check if the user already exists
On Error Resume Next
Set objNewUser = GetObject("LDAP://cn=" & strFullName & "," & strOUPath)
If Err.Number = 0 Then
MsgBox "User " & strFullName & " already exists."
On Error GoTo 0
Else
Err.Clear
On Error GoTo 0
' Build the actual User.
' Attributes listed here: http://support.microsoft.com/kb/555638
Set objNewUser = objContainer.Create("User" , "cn= " & strusername)
If InStr(strUserName, "@") > 0 Then
arrDomUserName = Split(strUserName, "@")
strUserName = arrDomUserName(0)
strSuffix = arrDomUserName(1)
Else
strUserName = strUserName
strSuffix = Replace(Replace(objRootLDA P.Get("def aultNaming Context"), ",", "."), "DC=", "")
End If
objNewUser.Put "userPrincipalName", strUserName & "@" & strSuffix
objNewUser.Put "sAMAccountName", strUserName
objNewUser.Put "givenName", strFirstName
objNewUser.Put "sn", strLastName
objNewUser.Put "displayName", strFullname
objNewUser.Put "profilePath", strprofiledirectory & strUserName
objNewUser.SetInfo
objNewUser.Put "homeDrive", "H:"
objNewUser.Put "homeDirectory", strHomeDirectory & strUserName
objNewUser.SetInfo
objNewUser.Put "streetAddress", "Student"
objNewUser.Put "physicalDeliveryOfficeNam e", strOffice
objNewUser.Put "Telephonenumber", strPhone
objNewUser.Put "description", strFullname
objNewUser.SetPassword strPassword
objNewUser.Put "mail", strusername & "@student.hih.no"
objNewUser.Put "company", "HIH"
objNewUser.AccountDisabled = False
objNewUser.SetInfo
intUserAccountControl = objNewUser.Get("userAccoun tControl")
If Not objNewUser.userAccountCont rol AND ADS_UF_DONT_EXPIRE_PASSWD Then
objNewUser.Put "userAccountControl", objNewUser.userAccountCont rol XOR ADS_UF_DONT_EXPIRE_PASSWD
objNewUser.SetInfo
End If
End If
End If
Next
WScript.Echo "Done"
objExcel.ActiveWorkbook.Cl ose False
objExcel.Quit
Set objExcel = Nothing
hi have this script for importing users from a excel sheet, my only problem is when script finds existing user in AD from excel sheet it stops and i would like the script to enable and changing the attributes in AD from excel, fullname,username,password
this is my script:
If LCase(Right(Wscript.FullNa
strPath = Wscript.ScriptFullName
strCommand = "%comspec% /k cscript """ & strPath & """"
Set objShell = CreateObject("Wscript.Shel
objShell.Run(strCommand), 1, True
Wscript.Quit
End If
' Bind to Active Directory.
Set objRootLDAP = GetObject("LDAP://rootDSE"
' CONFIGURATION PARAMETERS FOR THE SCRIPT
strExcelFile = Replace(WScript.ScriptFull
strOUPath = "OU=studenter," & objRootLDAP.Get("defaultNa
' END CONFIGURATION PARAMETERS
Const xlUp = -4162
Const ADS_UF_DONT_EXPIRE_PASSWD = &H10000
Set objExcel = CreateObject("Excel.Applic
objExcel.Visible = True
objExcel.Workbooks.Open strExcelFile
Set objNetwork = CreateObject("WScript.Netw
strDomainName = objNetwork.UserDomain
For intRow = 2 To objExcel.ActiveSheet.Cells
strFullName = Trim(Replace(objExcel.Acti
strUserName = Trim(objExcel.ActiveSheet.
strPassword = Trim(objExcel.ActiveSheet.
strHomeDirectory = Trim(Replace(objExcel.Acti
strprofileDirectory = Trim(Replace(objExcel.Acti
strOffice = Trim(Replace(objExcel.Acti
strphone = Trim(Replace(objExcel.Acti
strFirstName = Trim(left(strFullName, InStrRev(strFullName, " ") - 1))
strLastName = Trim(left(strFullName, InStrRev(strFullName, " ") + 1))
If strFullName <> "" And strUserName <> "" Then
'WScript.Echo "About to create:" & VbCrLf &_
' strFullName & VbCrLf &_
' strFirstName & VbCrLf &_
' strLastName & VbCrLf & _
' strUserName & VbCrLf &_
' strPassword & VbCrLf &_
' strprofileDirectory & VbCrLf &_
' strHomeDirectory & VbCrLf &_
' strOffice & VbCrLf &_
' strphone & VbCrLf &_
' "LDAP://" & strOUPath
' This will add the user to eg. Domain.Local\Users
Set objContainer = GetObject("LDAP://" & strOUPath)
' Check if the user already exists
On Error Resume Next
Set objNewUser = GetObject("LDAP://cn=" & strFullName & "," & strOUPath)
If Err.Number = 0 Then
MsgBox "User " & strFullName & " already exists."
On Error GoTo 0
Else
Err.Clear
On Error GoTo 0
' Build the actual User.
' Attributes listed here: http://support.microsoft.com/kb/555638
Set objNewUser = objContainer.Create("User"
If InStr(strUserName, "@") > 0 Then
arrDomUserName = Split(strUserName, "@")
strUserName = arrDomUserName(0)
strSuffix = arrDomUserName(1)
Else
strUserName = strUserName
strSuffix = Replace(Replace(objRootLDA
End If
objNewUser.Put "userPrincipalName", strUserName & "@" & strSuffix
objNewUser.Put "sAMAccountName", strUserName
objNewUser.Put "givenName", strFirstName
objNewUser.Put "sn", strLastName
objNewUser.Put "displayName", strFullname
objNewUser.Put "profilePath", strprofiledirectory & strUserName
objNewUser.SetInfo
objNewUser.Put "homeDrive", "H:"
objNewUser.Put "homeDirectory", strHomeDirectory & strUserName
objNewUser.SetInfo
objNewUser.Put "streetAddress", "Student"
objNewUser.Put "physicalDeliveryOfficeNam
objNewUser.Put "Telephonenumber", strPhone
objNewUser.Put "description", strFullname
objNewUser.SetPassword strPassword
objNewUser.Put "mail", strusername & "@student.hih.no"
objNewUser.Put "company", "HIH"
objNewUser.AccountDisabled
objNewUser.SetInfo
intUserAccountControl = objNewUser.Get("userAccoun
If Not objNewUser.userAccountCont
objNewUser.Put "userAccountControl", objNewUser.userAccountCont
objNewUser.SetInfo
End If
End If
End If
Next
WScript.Echo "Done"
objExcel.ActiveWorkbook.Cl
objExcel.Quit
Set objExcel = Nothing
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.