> Does anyone have a script that reads and Execl spread sheet and matches
> username in AD with username in the spreadsheet, then modifies the
> "wwwhomepage" and put a value in the AD general user attribute (web page
> field)?
>
> Clarification:
>
> I want read a list of usernames from a file and then search the entire AD
> tree for a user (by Lastname, Firstname and or username) in a file, if I
> find
> the user, I want to modify the Web Page field (under general user) and add
> a
> value (read from the same file).
>
> Example:
>
> File:
> FirstName  Lastname username  ID Number
> John          Brown      jbrown      123456789
>
> Search for jbrown, if exist, then move 123456789 to Web Page
> (wwwhomepage)field.
>
> or
>
> Search for John and Brown, if exist, then move 123456789 to web page,
> (wwwhomepage) filed if John Brown is a duplicate, write to a file.
> Thanks.

"Richard Mueller [MVP]" wrote:

> spock wrote:
>
> > Does anyone have a script that reads and Execl spread sheet and matches
> > username in AD with username in the spreadsheet, then modifies the
> > "wwwhomepage" and put a value in the AD general user attribute (web page
> > field)?
> >
> > Clarification:
> >
> > I want read a list of usernames from a file and then search the entire AD
> > tree for a user (by Lastname, Firstname and or username) in a file, if I
> > find
> > the user, I want to modify the Web Page field (under general user) and add
> > a
> > value (read from the same file).
> >
> > Example:
> >
> > File:
> > FirstName  Lastname username  ID Number
> > John          Brown      jbrown      123456789
> >
> > Search for jbrown, if exist, then move 123456789 to Web Page
> > (wwwhomepage)field.
> >
> > or
> >
> > Search for John and Brown, if exist, then move 123456789 to web page,
> > (wwwhomepage) filed if John Brown is a duplicate, write to a file.
> > Thanks.
>
> I assume what you call the username is the value of the sAMAccountName
> attribute of the user. This is also called the NT name of the user or the
> "pre-Windows 2000 logon name". If so, you can use the NameTranslate object
> (combined with the NetBIOS name of the domain) to convert to the
> Distinguished Name required by the LDAP provider. Details linked here:
>
> http://www.rlmueller.net/NameTranslateFAQ.htm
>
> This would be more efficient than searching all of AD. However, a potential
> problem I see is how to parse your file. You can use the FileSystemObject to
> read the file one line at a time, but can we be sure all values are
> delimited by spaces? Is it possible that some names could have embedded
> spaces, like say "Mary Ann"? I personally have seen many examples. If you
> can, it would be best to use a delimiter like the semicolon ";".
>
> Assuming we can use the ";" character as a delimiter, the program could be
> similar to:
> ==================
> Option Explicit
>
> Dim objRootDSE, strDNSDomain, strNetBIOSDomain
> Dim objTrans, strFile, objFSO, objFile
> Dim strLine, arrValues, strValue
> Dim strFirst, strLast, strUser, strID
> Dim strUserDN, objUser
>
> Const ForReading = 1
>
> ' Constants for NameTranslate.
> Const ADS_NAME_INITTYPE_GC = 3
> Const ADS_NAME_TYPE_NT4 = 3
> Const ADS_NAME_TYPE_1779 = 1
>
> ' Specify input file.
> strFile = "c:\Scripts\Names.txt"
>
> ' Open the file for read access.
> Set objFSO = CreateObject("Scripting.FileSystemObject")
> Set objFile = objFSO.OpenTextFile(strFile, ForReading)
>
> ' Determine DNS domain name from RootDSE object.
> Set objRootDSE = GetObject("LDAP://RootDSE")
> strDNSDomain = objRootDSE.Get("defaultNamingContext")
>
> ' Use the NameTranslate object to find the NetBIOS domain name
> ' from the DNS domain name.
> Set objTrans = CreateObject("NameTranslate")
> objTrans.Init ADS_NAME_INITTYPE_GC, ""
> objTrans.Set ADS_NAME_TYPE_1779, strDNSDomain
> strNetBIOSDomain = objTrans.Get(ADS_NAME_TYPE_NT4)
> ' Remove trailing backslash.
> strNetBIOSDomain = Left(strNetBIOSDomain, _
>     Len(strNetBIOSDomain) - 1)
>
> ' Read the file.
> Do Until objFile.AtEndOfStream
>    strLine = objFile.ReadLine
>    ' Skip blank lines.
>    If (strLine <> "") Then
>        ' Parse the line into an array of values.
>        arrValues = Split(strLine, ";")
>        ' There must be 4 values per line.
>        If (UBound(arrValues) = 3) Then
>            strFirst = Trim(arrValues(0))
>            strLast = Trim(arrValues(1))
>            strUser = Trim(arrValues(2))
>            strID = Trim(arrValues(3))
>            ' Use NameTranslate to convert NT name to DN.
>            objTrans.Set ADS_NAME_TYPE_NT4, strNetBIOSDomain & "\" & strUser
>            strUserDN = objTrans.Get(ADS_NAME_TYPE_1779)
>            ' Bind to user object.
>            Set objUser = GetObject("LDAP://" & strUserDN)
>            ' Assign wWWHomePage
>            objUser.wWWHomePage = strID
>            objUser.SetInfo
>         End If
>    End If
> Loop
>
> ' Clean up.
> objFile.Close
>
> --
> Richard Mueller
> Microsoft MVP Scripting and ADSI
> Hilltop Lab - http://www.rlmueller.net
> --
>
>
>

"spock" <sp***@discussions.microsoft.com> wrote in message
news:B0E01DE9-4E07-4E5E-9643-4209BB0E5162@microsoft.com...
> Richard,
>
> I tested the script and it work great!!
> Thanks.
>
> Other question:
> Would you haapened to have a script to do the following:
>
> In order to create the names.txt file, I am going to have to combine 2
> different spread sheets, from two different systems.  If name matches,
> create
> new record:
>
> First Name Last Name MI  AD Login ID      ID Number
> John          Brown        A  jbrown             123456789
>
> If no MI and duplicate John Brown, skip and write to file
> c:\scripts\exceptions.txt
>
> From AD:
>
> First Name Last Name MI  AD Login ID
> John           Brown       A   jbrown
>
> From HR System:
> First Name Last Name MI  ID Number
> John           Brown       A   123456789
>
> New File:
> First Name Last Name MI  AD Login ID ID Number
> John           Brown       A   jbrown       123456789

"Richard Mueller [MVP]" wrote:

> Years ago I had the task of identifying and deleting accounts (and files) in
> various systems for about 2000 people that had left a large corporation. I
> spent three months on this and it was a major, major task. Every system had
> different names for the same people. I decided that names are the worst way
> to identify people. I used huge Excel spreadsheets to attempt to match
> 25,000 people in the various systems - HR, mainframe, network, email, etc.
>
> If you already have spreadsheets, I would use the spreadsheet functions,
> like HLOOKUP and VLOOKUP, to match people on various worksheets of the same
> workbook. I remember it took me awhile to figure out how to do this, and my
> spreadsheets took 10 minutes to recalculate when I made a change, but this
> might be a better approach. The spreadsheet can tell if all three name
> fields match, and fill in the ID number. It can tell when there is not a
> match and flag it. Then you can export the rows that match and study the
> rest further. This sounds like it would me more difficult to do in code.
>
> --
> Richard Mueller
> Microsoft MVP Scripting and ADSI
> Hilltop Lab - http://www.rlmueller.net
> --
>
> "spock" <sp***@discussions.microsoft.com> wrote in message
> news:B0E01DE9-4E07-4E5E-9643-4209BB0E5162@microsoft.com...
> > Richard,
> >
> > I tested the script and it work great!!
> > Thanks.
> >
> > Other question:
> > Would you haapened to have a script to do the following:
> >
> > In order to create the names.txt file, I am going to have to combine 2
> > different spread sheets, from two different systems.  If name matches,
> > create
> > new record:
> >
> > First Name Last Name MI  AD Login ID      ID Number
> > John          Brown        A  jbrown             123456789
> >
> > If no MI and duplicate John Brown, skip and write to file
> > c:\scripts\exceptions.txt
> >
> > From AD:
> >
> > First Name Last Name MI  AD Login ID
> > John           Brown       A   jbrown
> >
> > From HR System:
> > First Name Last Name MI  ID Number
> > John           Brown       A   123456789
> >
> > New File:
> > First Name Last Name MI  AD Login ID ID Number
> > John           Brown       A   jbrown       123456789
>
>
>