bsharath
asked on
Ads to update just what is mentioned in the excel.
Hi,
I have a excel file that just has details that has to be updated for each user.
Say for user A i need to update Description
For user B need to update Companyname.For userC need to update Designation.For User D i need to update all.So is there a way a script that can do these things.
Ex:
Headers.
Colum A Colum B Colum C Colm D Colum E Colum F Colum G Colum H Colum I Colum J
NTLogin Office Website Title Designa Notes Street City State Zipcode
Any so on with some more to add...
So Colum A has the NTlogin for which ever cell that has data has to be update in the ADS tabs/boxes for the relevent user.
Is there a way this can be done.Just update data that is mentioned in the cells in the row.
Regards
Sharath
I have a excel file that just has details that has to be updated for each user.
Say for user A i need to update Description
For user B need to update Companyname.For userC need to update Designation.For User D i need to update all.So is there a way a script that can do these things.
Ex:
Headers.
Colum A Colum B Colum C Colm D Colum E Colum F Colum G Colum H Colum I Colum J
NTLogin Office Website Title Designa Notes Street City State Zipcode
Any so on with some more to add...
So Colum A has the NTlogin for which ever cell that has data has to be update in the ADS tabs/boxes for the relevent user.
Is there a way this can be done.Just update data that is mentioned in the cells in the row.
Regards
Sharath
ASKER
Chandru...
For some users i shall have it blank.If Blank need to leave it as it is...
Say for user sharath.I need to update just Title so other cells will be blank in the excel...
For some users i shall have it blank.If Blank need to leave it as it is...
Say for user sharath.I need to update just Title so other cells will be blank in the excel...
ASKER
Any help....
Sharath, try this....I have removed the designation column, as I think that's the same as your Title column....
'===============
' Search_LDAP_Using_ADO_To_U pdate_User _Info_From _Excel.vbs
' https://www.experts-exchange.com/questions/22912145/Ads-to-update-just-what-is-mentioned-in-the-excel.html
If Right(LCase(WScript.FullNa me), 11) = "wscript.exe" Then
Set objShell = CreateObject("WScript.Shel l")
objShell.Run "cmd /k cscript """ & WScript.ScriptFullName & """", 1, False
Set objShell = Nothing
WScript.Quit
End If
strExcelFile = "Users_To_Update.xls"
Const xlUp = -4162
Const ADS_PROPERTY_CLEAR = 1
Set objExcel = CreateObject("Excel.Applic ation")
Set objWB = objExcel.Workbooks.Open(Re place(WScr ipt.Script FullName, WScript.ScriptName, "") & strExcelFile)
objExcel.Visible = True
For intRow = 2 To objWB.ActiveSheet.Cells(65 536, "A").End(xlUp).Row
' Get the details per row from the Excel file
strNTLogin = objWB.ActiveSheet.Cells(in tRow, "A").Value
strOffice = objWB.ActiveSheet.Cells(in tRow, "B").Value
strWebsite = objWB.ActiveSheet.Cells(in tRow, "C").Value
strTitle = objWB.ActiveSheet.Cells(in tRow, "D").Value
strNotes = objWB.ActiveSheet.Cells(in tRow, "E").Value
strStreet = objWB.ActiveSheet.Cells(in tRow, "F").Value
strCity = objWB.ActiveSheet.Cells(in tRow, "G").Value
strState = objWB.ActiveSheet.Cells(in tRow, "H").Value
strZipcode = objWB.ActiveSheet.Cells(in tRow, "I").Value
strUserADsPath = Get_LDAP_User_Properties(" user", "samAccountName", strNTLogin, "adsPath")
If InStr(UCase(strUserADsPath ), "LDAP://") > 0 Then
Set objUser = GetObject(strUserADsPath)
WScript.Echo "Processing " & strUserADsPath
If Trim(strOffice) <> "" Then
WScript.Echo "Updating Office for " & strNTLogin
objUser.physicalDeliveryOf ficeName = strOffice
End If
If Trim(strWebsite) <> "" Then
WScript.Echo "Updating Web Home Page for " & strNTLogin
objUser.wWWHomePage = strWebsite
End If
If Trim(strTitle) <> "" Then
WScript.Echo "Updating Title for " & strNTLogin
objUser.title = strTitle
End If
If Trim(strNotes) <> "" Then
WScript.Echo "Updating Notes for " & strNTLogin
objUser.Info = strNotes
End If
If Trim(strStreet) <> "" Then
WScript.Echo "Updating Street Address for " & strNTLogin
objUser.streetAddress = strStreet
End If
If Trim(strCity) <> "" Then
WScript.Echo "Updating City for " & strNTLogin
objUser.l = strCity
End If
If Trim(strState) <> "" Then
WScript.Echo "Updating State for " & strNTLogin
objUser.st = strState
End If
If Trim(strZipcode) <> "" Then
WScript.Echo "Updating Zip Code for " & strNTLogin
objUser.postalCode = strZipcode
End If
objUser.SetInfo
Else
WScript.Echo "Could not find ADsPath for user " & strNTLogin
End If
Next
objExcel.Quit
Set objExcel = Nothing
WScript.Echo "Done"
MsgBox "Done"
Function Get_LDAP_User_Properties(s trObjectTy pe, strSearchField, strObjectToGet, strCommaDelimProps)
' This is a custom function that connects to the Active Directory, and returns the specific
' Active Directory attribute value, of a specific Object.
' strObjectType: usually "User" or "Computer"
' strSearchField: the field by which to seach the AD by. This acts like an SQL Query's WHERE clause.
' It filters the results by the value of strObjectToGet
' strObjectToGet: the value by which the results are filtered by, according the strSearchField.
' For example, if you are searching based on the user account name, strSearchField
' would be "samAccountName", and strObjectToGet would be that speicific account name,
' such as "jsmith". This equates to "WHERE 'samAccountName' = 'jsmith'"
' strCommaDelimProps: the field from the object to actually return. For example, if you wanted
' the home folder path, as defined by the AD, for a specific user, this would be
' "homeDirectory". If you want to return the ADsPath so that you can bind to that
' user and get your own parameters from them, then use "ADsPath" as a return string,
' then bind to the user: Set objUser = GetObject("LDAP://" & strReturnADsPath)
' Now we're checking if the user account passed may have a domain already specified,
' in which case we connect to that domain in AD, instead of the default one.
If InStr(strObjectToGet, "\") > 0 Then
arrGroupBits = Split(strObjectToGet, "\")
strDC = arrGroupBits(0)
strDNSDomain = strDC & "/" & "DC=" & Replace(Mid(strDC, InStr(strDC, ".") + 1), ".", ",DC=")
strObjectToGet = arrGroupBits(1)
Else
' Otherwise we just connect to the default domain
Set objRootDSE = GetObject("LDAP://RootDSE" )
strDNSDomain = objRootDSE.Get("defaultNam ingContext ")
End If
strBase = "<LDAP://" & strDNSDomain & ">"
' Setup ADO objects.
Set adoCommand = CreateObject("ADODB.Comman d")
Set adoConnection = CreateObject("ADODB.Connec tion")
adoConnection.Provider = "ADsDSOObject"
adoConnection.Open "Active Directory Provider"
adoCommand.ActiveConnectio n = adoConnection
' Filter on user objects.
'strFilter = "(&(objectCategory=person) (objectCla ss=user))"
strFilter = "(&(objectClass=" & strObjectType & ")(" & strSearchField & "=" & strObjectToGet & "))"
' Comma delimited list of attribute values to retrieve.
strAttributes = strCommaDelimProps
arrProperties = Split(strCommaDelimProps, ",")
' Construct the LDAP syntax query.
strQuery = strBase & ";" & strFilter & ";" & strAttributes & ";subtree"
adoCommand.CommandText = strQuery
' Define the maximum records to return
adoCommand.Properties("Pag e Size") = 100
adoCommand.Properties("Tim eout") = 30
adoCommand.Properties("Cac he Results") = False
' Run the query.
Set adoRecordset = adoCommand.Execute
' Enumerate the resulting recordset.
strReturnVal = ""
Do Until adoRecordset.EOF
' Retrieve values and display.
For intCount = LBound(arrProperties) To UBound(arrProperties)
If strReturnVal = "" Then
strReturnVal = adoRecordset.Fields(intCou nt).Value
Else
strReturnVal = strReturnVal & VbCrLf & adoRecordset.Fields(intCou nt).Value
End If
Next
' Move to the next record in the recordset.
adoRecordset.MoveNext
Loop
' Clean up.
adoRecordset.Close
adoConnection.Close
Get_LDAP_User_Properties = strReturnVal
End Function
'===============
Regards,
Rob.
'===============
' Search_LDAP_Using_ADO_To_U
' https://www.experts-exchange.com/questions/22912145/Ads-to-update-just-what-is-mentioned-in-the-excel.html
If Right(LCase(WScript.FullNa
Set objShell = CreateObject("WScript.Shel
objShell.Run "cmd /k cscript """ & WScript.ScriptFullName & """", 1, False
Set objShell = Nothing
WScript.Quit
End If
strExcelFile = "Users_To_Update.xls"
Const xlUp = -4162
Const ADS_PROPERTY_CLEAR = 1
Set objExcel = CreateObject("Excel.Applic
Set objWB = objExcel.Workbooks.Open(Re
objExcel.Visible = True
For intRow = 2 To objWB.ActiveSheet.Cells(65
' Get the details per row from the Excel file
strNTLogin = objWB.ActiveSheet.Cells(in
strOffice = objWB.ActiveSheet.Cells(in
strWebsite = objWB.ActiveSheet.Cells(in
strTitle = objWB.ActiveSheet.Cells(in
strNotes = objWB.ActiveSheet.Cells(in
strStreet = objWB.ActiveSheet.Cells(in
strCity = objWB.ActiveSheet.Cells(in
strState = objWB.ActiveSheet.Cells(in
strZipcode = objWB.ActiveSheet.Cells(in
strUserADsPath = Get_LDAP_User_Properties("
If InStr(UCase(strUserADsPath
Set objUser = GetObject(strUserADsPath)
WScript.Echo "Processing " & strUserADsPath
If Trim(strOffice) <> "" Then
WScript.Echo "Updating Office for " & strNTLogin
objUser.physicalDeliveryOf
End If
If Trim(strWebsite) <> "" Then
WScript.Echo "Updating Web Home Page for " & strNTLogin
objUser.wWWHomePage = strWebsite
End If
If Trim(strTitle) <> "" Then
WScript.Echo "Updating Title for " & strNTLogin
objUser.title = strTitle
End If
If Trim(strNotes) <> "" Then
WScript.Echo "Updating Notes for " & strNTLogin
objUser.Info = strNotes
End If
If Trim(strStreet) <> "" Then
WScript.Echo "Updating Street Address for " & strNTLogin
objUser.streetAddress = strStreet
End If
If Trim(strCity) <> "" Then
WScript.Echo "Updating City for " & strNTLogin
objUser.l = strCity
End If
If Trim(strState) <> "" Then
WScript.Echo "Updating State for " & strNTLogin
objUser.st = strState
End If
If Trim(strZipcode) <> "" Then
WScript.Echo "Updating Zip Code for " & strNTLogin
objUser.postalCode = strZipcode
End If
objUser.SetInfo
Else
WScript.Echo "Could not find ADsPath for user " & strNTLogin
End If
Next
objExcel.Quit
Set objExcel = Nothing
WScript.Echo "Done"
MsgBox "Done"
Function Get_LDAP_User_Properties(s
' This is a custom function that connects to the Active Directory, and returns the specific
' Active Directory attribute value, of a specific Object.
' strObjectType: usually "User" or "Computer"
' strSearchField: the field by which to seach the AD by. This acts like an SQL Query's WHERE clause.
' It filters the results by the value of strObjectToGet
' strObjectToGet: the value by which the results are filtered by, according the strSearchField.
' For example, if you are searching based on the user account name, strSearchField
' would be "samAccountName", and strObjectToGet would be that speicific account name,
' such as "jsmith". This equates to "WHERE 'samAccountName' = 'jsmith'"
' strCommaDelimProps: the field from the object to actually return. For example, if you wanted
' the home folder path, as defined by the AD, for a specific user, this would be
' "homeDirectory". If you want to return the ADsPath so that you can bind to that
' user and get your own parameters from them, then use "ADsPath" as a return string,
' then bind to the user: Set objUser = GetObject("LDAP://" & strReturnADsPath)
' Now we're checking if the user account passed may have a domain already specified,
' in which case we connect to that domain in AD, instead of the default one.
If InStr(strObjectToGet, "\") > 0 Then
arrGroupBits = Split(strObjectToGet, "\")
strDC = arrGroupBits(0)
strDNSDomain = strDC & "/" & "DC=" & Replace(Mid(strDC, InStr(strDC, ".") + 1), ".", ",DC=")
strObjectToGet = arrGroupBits(1)
Else
' Otherwise we just connect to the default domain
Set objRootDSE = GetObject("LDAP://RootDSE"
strDNSDomain = objRootDSE.Get("defaultNam
End If
strBase = "<LDAP://" & strDNSDomain & ">"
' Setup ADO objects.
Set adoCommand = CreateObject("ADODB.Comman
Set adoConnection = CreateObject("ADODB.Connec
adoConnection.Provider = "ADsDSOObject"
adoConnection.Open "Active Directory Provider"
adoCommand.ActiveConnectio
' Filter on user objects.
'strFilter = "(&(objectCategory=person)
strFilter = "(&(objectClass=" & strObjectType & ")(" & strSearchField & "=" & strObjectToGet & "))"
' Comma delimited list of attribute values to retrieve.
strAttributes = strCommaDelimProps
arrProperties = Split(strCommaDelimProps, ",")
' Construct the LDAP syntax query.
strQuery = strBase & ";" & strFilter & ";" & strAttributes & ";subtree"
adoCommand.CommandText = strQuery
' Define the maximum records to return
adoCommand.Properties("Pag
adoCommand.Properties("Tim
adoCommand.Properties("Cac
' Run the query.
Set adoRecordset = adoCommand.Execute
' Enumerate the resulting recordset.
strReturnVal = ""
Do Until adoRecordset.EOF
' Retrieve values and display.
For intCount = LBound(arrProperties) To UBound(arrProperties)
If strReturnVal = "" Then
strReturnVal = adoRecordset.Fields(intCou
Else
strReturnVal = strReturnVal & VbCrLf & adoRecordset.Fields(intCou
End If
Next
' Move to the next record in the recordset.
adoRecordset.MoveNext
Loop
' Clean up.
adoRecordset.Close
adoConnection.Close
Get_LDAP_User_Properties = strReturnVal
End Function
'===============
Regards,
Rob.
ASKER
Rob this works perfect if all data was filled
Need to add these please.
Home
Mobile
Department
Company
Telephone number
Descripton
Rob should be able to leave any ones blanks.
When i left some blanks i get this error.
C:\>"Update just what needed.vbs"
Microsoft (R) Windows Script Host Version 5.6
Copyright (C) Microsoft Corporation 1996-2001. All rights reserved.
Processing LDAP://CN=Sharath Reddy,OU=Named Accounts,OU=User Accounts,OU=IND,OU=
Countries,DC=Development,D C=Group,DC =co,DC=uk
Updating Office for sharathr
C:\Update just what needed.vbs(39, 19) (null): Unspecified error
Need to add these please.
Home
Mobile
Department
Company
Telephone number
Descripton
Rob should be able to leave any ones blanks.
When i left some blanks i get this error.
C:\>"Update just what needed.vbs"
Microsoft (R) Windows Script Host Version 5.6
Copyright (C) Microsoft Corporation 1996-2001. All rights reserved.
Processing LDAP://CN=Sharath Reddy,OU=Named Accounts,OU=User Accounts,OU=IND,OU=
Countries,DC=Development,D
Updating Office for sharathr
C:\Update just what needed.vbs(39, 19) (null): Unspecified error
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Rob just related to this Q...
https://www.experts-exchange.com/questions/22936422/Find-mismatches-comparing-to-the-excel-and-ADS.html
https://www.experts-exchange.com/questions/22936422/Find-mismatches-comparing-to-the-excel-and-ADS.html
I have a script which updates the attributes as per you requirement but not different field for different users.
for those users you will have the attribute blank or you will have some information that needs to be changed
regards
Chandru