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

chandru_sol

Sharath,
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

bsharath

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...

bsharath

ASKER

Any help....

RobSampson

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_Update_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.FullName), 11) = "wscript.exe" Then
      Set objShell = CreateObject("WScript.Shell")
      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.Application")
Set objWB = objExcel.Workbooks.Open(Replace(WScript.ScriptFullName, WScript.ScriptName, "") & strExcelFile)
objExcel.Visible = True

For intRow = 2 To objWB.ActiveSheet.Cells(65536, "A").End(xlUp).Row
      ' Get the details per row from the Excel file
      strNTLogin = objWB.ActiveSheet.Cells(intRow, "A").Value
      strOffice = objWB.ActiveSheet.Cells(intRow, "B").Value
      strWebsite = objWB.ActiveSheet.Cells(intRow, "C").Value
      strTitle = objWB.ActiveSheet.Cells(intRow, "D").Value
      strNotes = objWB.ActiveSheet.Cells(intRow, "E").Value
      strStreet = objWB.ActiveSheet.Cells(intRow, "F").Value
      strCity = objWB.ActiveSheet.Cells(intRow, "G").Value
      strState = objWB.ActiveSheet.Cells(intRow, "H").Value
      strZipcode = objWB.ActiveSheet.Cells(intRow, "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.physicalDeliveryOfficeName = 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(strObjectType, 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("defaultNamingContext")
End If

strBase = "<LDAP://" & strDNSDomain & ">"
' Setup ADO objects.
Set adoCommand = CreateObject("ADODB.Command")
Set adoConnection = CreateObject("ADODB.Connection")
adoConnection.Provider = "ADsDSOObject"
adoConnection.Open "Active Directory Provider"
adoCommand.ActiveConnection = adoConnection

' Filter on user objects.
'strFilter = "(&(objectCategory=person)(objectClass=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("Page Size") = 100
adoCommand.Properties("Timeout") = 30
adoCommand.Properties("Cache 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(intCount).Value
Else
strReturnVal = strReturnVal & VbCrLf & adoRecordset.Fields(intCount).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.

bsharath

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,DC=Group,DC=co,DC=uk
Updating Office for sharathr
C:\Update just what needed.vbs(39, 19) (null): Unspecified error

ASKER CERTIFIED SOLUTION

RobSampson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bsharath

ASKER