Vista - searching active directory while reading/writing to excel

i'm still green when it comes to scripting, but thanks to groups like
this and sites like http://www.rlmueller.net/ i've been able to piece
together most of what i need. i'm running into some problems now
though. i was given a list of around 2000 user ID's (in a
spreadsheet) that i need to verify exist in a.d. i thought i had this
script working, but whether the user ID is found in a.d. or not, it
always gives me the result "user not found". i'm getting no errors,
just wrong info. anyone with some free time care to help? thank in
advance. here's what i have:

Option Explicit

Dim objExcel, strExcelPath, objSheet, intRow, strUserDN, strFirstName
Dim strMiddleInitial, strLastName, objUser

' Bind to Excel object.
On Error Resume Next
Set objExcel = CreateObject("Excel.Application")

strExcelPath = "C:\Documents and Settings\scripts in progress
\vtusers.xls"

' Open specified spreadsheet and select the worksheet.
objExcel.WorkBooks.Open strExcelPath
Set objSheet = objExcel.ActiveWorkbook.Worksheets(2)

' Iterate through the rows of the spreadsheet after the first, until
the
' first blank entry in the first column. For each row, bind to the
user
' specified in the first column and set attributes.
intRow = 2
Do While objSheet.Cells(intRow, 1).Value <> ""
strUserDN = objSheet.Cells(intRow, 1).Value
On Error Resume Next
Set objUser = GetObject("LDAP://" & strUserDN)
If (Err.Number <> 0) Then
On Error GoTo 0
objSheet.Cells(intRow, 2).Value = "User NOT Found"
End If
intRow = intRow + 1
Loop

' Close workbook and quit Excel.
objExcel.ActiveWorkbook.Close
objExcel.Application.Quit

' Clean up.
Set objExcel = Nothing
Set objSheet = Nothing
Set objUser = Nothing

Wscript.Echo "Done"

<greasy.guitar@xxxxxx> wrote in message
news:61608d13-c978-4ac4-ad2f-6b19a70323f1@xxxxxxMost likely your spreadsheet has NT names (pre-Windows 2000 logon names),
such as "JimSmith", rather than the full Distinguished Names of the users,
like "cn=Jim Smith,ou=West,dc=MyDomain,dc=com". Assuming this is the case,
you can use the NameTranslate object to convert the NT form of the name to
the Distinguished Name. For more see this link:

http://www.rlmueller.net/NameTranslateFAQ.htm

If the user does not exist, an error is raised by the Set method. This error
can be trapped. For example, you code could be similar to:
============
Option Explicit

Dim objExcel, strExcelPath, objSheet, intRow, strUserDN, strFirstName
Dim strMiddleInitial, strLastName, objUser
Dim strNTName, objTrans, strDNSDomain, strNetBIOSDomain

' Constants for the NameTranslate object.
Const ADS_NAME_INITTYPE_GC = 3
Const ADS_NAME_TYPE_NT4 = 3
Const ADS_NAME_TYPE_1779 = 1



' Determine DNS name of domain from RootDSE.
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)


' Bind to Excel object.
Set objExcel = CreateObject("Excel.Application")

strExcelPath = "C:\Documents and Settings\scripts in progress\vtusers.xls"

' Open specified spreadsheet and select the worksheet.
objExcel.WorkBooks.Open strExcelPath
Set objSheet = objExcel.ActiveWorkbook.Worksheets(2)

' Iterate through the rows of the spreadsheet after the first, until the
' first blank entry in the first column. For each row, bind to the user
' specified in the first column and set attributes.
intRow = 2
Do While objSheet.Cells(intRow, 1).Value <> ""
strNTName = Trim(objSheet.Cells(intRow, 1).Value)
' Use the Set method to specify the NT format of the object name.
' Trap error if user not found.
On Error Resume Next
objTrans.Set ADS_NAME_TYPE_NT4, strNetBIOSDomain & "\" & strNTName
If (Err.Number <> 0) Then
' User not found.
On Error GoTo 0
objSheet.Cells(intRow, 2).Value = "User NOT Found"
End If
On Error GoTo 0
' Use the Get method to retrieve the RPC 1779 Distinguished Name.
strUserDN = objTrans.Get(ADS_NAME_TYPE_1779)

Set objUser = GetObject("LDAP://" & strUserDN)
intRow = intRow + 1
Loop

' Save the spreadsheet and close the workbook.
objExcel.ActiveWorkbook.SaveAs strExcelPath

' Close workbook and quit Excel.
objExcel.ActiveWorkbook.Close
objExcel.Application.Quit

' Clean up.
Set objExcel = Nothing
Set objSheet = Nothing
Set objUser = Nothing

Wscript.Echo "Done"
===========
If you only need to verify that the user exists, then don't bother to bind
to the user object ("Set objUser"), as that slows down the program. Since
you modify the spreadsheet, I added a statement save.

--
Richard Mueller
MVP Directory Services
Hilltop Lab - http://www.rlmueller.net
--

> If you only need to verify that the user exists, then don't bother to bindworked perfectly (well, after declaring objRootDSE)! thanks again,
Richard. most of my admin scripts have been your doing.

> If you only need to verify that the user exists, then don't bother to bindone last question...i wound up leaving the binding to the user object
in, and as i thought would happen, my boss wanted more info. first,
he wanted to know the email address (if any) of verified users.
second, he wanted to know if any of the verified accounts were
disabled. i added a few lines of code, but the data was a row or two
off from where it should have been (i.e. the "account disabled" was
marked in the column next to a user who was still active). i was able
to go through and correct the email addresses and who was actually
disabled, but i'd like to know if it was the code or the blank rows
created by those with no email address. this was the loop from the
script that pulled email addresses:

intRow = 2
Do While objSheet.Cells(intRow, 1).Value <> ""
strNTName = Trim(objSheet.Cells(intRow, 1).Value)
' Use the Set method to specify the NT format of the object name.
' Trap error if user not found.
On Error Resume Next
objTrans.Set ADS_NAME_TYPE_NT4, strNetBIOSDomain & "\" &
strNTName
If (Err.Number <> 0) Then
User not found.
On Error GoTo 0
objSheet.Cells(intRow, 2).Value = "User NOT Found"
Else objSheet.Cells(intRow, 3).Value = objUser.mail
End If
On Error GoTo 0
' Use the Get method to retrieve the RPC 1779 Distinguished Name.
strUserDN = objTrans.Get(ADS_NAME_TYPE_1779)
Set objUser = GetObject("LDAP://" & strUserDN)
intRow = intRow + 1
Loop

then, i modified it to look for disabled accounts by commenting out a
few lines and adding some:

intRow = 2
Do While objSheet.Cells(intRow, 1).Value <> ""
strNTName = Trim(objSheet.Cells(intRow, 1).Value)
' Use the Set method to specify the NT format of the object name.
' Trap error if user not found.
On Error Resume Next
objTrans.Set ADS_NAME_TYPE_NT4, strNetBIOSDomain & "\" &
strNTName
' If (Err.Number <> 0) Then
' User not found.
' On Error GoTo 0
' objSheet.Cells(intRow, 2).Value = "User NOT Found"
' Else objSheet.Cells(intRow, 3).Value = objUser.mail

If objUser.AccountDisabled = TRUE Then
objSheet.Cells(intRow, 3).Value = "Disabled Account"
End If
On Error GoTo 0
' Use the Get method to retrieve the RPC 1779 Distinguished Name.
strUserDN = objTrans.Get(ADS_NAME_TYPE_1779)
Set objUser = GetObject("LDAP://" & strUserDN)
intRow = intRow + 1
Loop

and, in case anyone was wondering, there were 2048 total users pulled
from the old database.1179 of those were found in AD, and 727 have
email addresses. for some strange reason, they made it impossible to
delete users, only make them inactive in the application.

<greasy.guitar@xxxxxx> wrote in message
news:df27c37f-8d29-4db9-a125-4dda328bf0f9@xxxxxxYou are retrieving values of attributes of the objUser object before the
statement that binds to the object. This accounts for you seeing information
from the previous user. Move all statements that use objUser after the Set
statement. Perhaps:
===========
intRow = 2
Do While objSheet.Cells(intRow, 1).Value <> ""
strNTName = Trim(objSheet.Cells(intRow, 1).Value)
' Use the Set method to specify the NT format of the object name.
' Trap error if user not found.
On Error Resume Next
objTrans.Set ADS_NAME_TYPE_NT4, strNetBIOSDomain & "\" & strNTName
If (Err.Number <> 0) Then
' User not found.
On Error GoTo 0
objSheet.Cells(intRow, 2).Value = "User NOT Found"
Else
On Error GoTo 0
' Use the Get method to retrieve the RPC 1779 Distinguished Name.
strUserDN = objTrans.Get(ADS_NAME_TYPE_1779)
Set objUser = GetObject("LDAP://" & strUserDN)
objSheet.Cells(intRow, 3).Value = objUser.mail
If objUser.AccountDisabled = TRUE Then
objSheet.Cells(intRow, 3).Value = "Disabled Account"
End If
End If
intRow = intRow + 1
Loop

--
Richard Mueller
MVP Directory Services
Hilltop Lab - http://www.rlmueller.net
--