Vista - Read from a txt, query AD, write to an xls file?

Hi all. I'm trying to master the art of opening multiple file objects
at once, and having some trouble. I'm trying to simply open a text
file, read the lines, and run an LDAP query using the values as a
filter variable called strLine, then write the FQDN output to a
spreadsheet. Normally I query with WinNT instead of LDAP, but I'm
trying to grow in a direction that lets me modify what the AD
objUser.HomeDrive and HomeDirectory values are, and that's not working
for me with WinNT. Here's the code I've got so far, and it opens the
text file, and creates the spreadsheet, but with no values and no
errors. Could someone help me learn how to work with two different
types of recordsets at once? Thanks!

Option Explicit

Dim strExcelPath, strFilePath, adoConnection, adoCommand, objRootDSE,
strDNSDomain
Dim strFilter, strQuery, adoRecordset, strDN, objExcel, objSheet, k,
objUser, objFSO
Dim objFile, strLine, intIndex

' Check for required arguments.
Const ForReading = 1

' Specify the text file of user names.
strFilePath = "c:\MyFolder\UserList.txt"

' Open the file for read access.
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(strFilePath, ForReading)

' Spreadsheet file to be created.
strExcelPath = "c:\myfolder\selectivelist.xls"

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

' Bind to worksheet.
Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)
objSheet.Name = "Domain User"
objSheet.Cells(1, 1).Value = "User Distinguished Name"

' Use ADO to search the domain for all users.
Set adoConnection = CreateObject("ADODB.Connection")
Set adoCommand = CreateObject("ADODB.Command")
adoConnection.Provider = "ADsDSOOBject"
adoConnection.Open "Active Directory Provider"
Set adoCommand.ActiveConnection = adoConnection

' Determine the DNS domain from the RootDSE object.
Set objRootDSE = GetObject("LDAP://RootDSE")
strDNSDomain = objRootDSE.Get("defaultNamingContext")
strFilter = "(&(objectCategory=person)(objectClass=user)(cn=strLine))"
strQuery = "<LDAP://" & strDNSDomain & ">;" & strFilter _
& ";distinguishedName;subtree"

adoCommand.CommandText = strQuery
adoCommand.Properties("Page Size") = 100
adoCommand.Properties("Timeout") = 30
adoCommand.Properties("Cache Results") = False

' Enumerate all users. Write each user's Distinguished Name to the
' spreadsheet.
k = 2
Set adoRecordset = adoCommand.Execute
Do Until adoRecordset.EOF
'Do Until objFile.AtEndOfStream
strLine = objFile.ReadLine
strDN = adoRecordset.Fields("distinguishedName").Value
' Escape any forward slash characters, "/", with the backslash
' escape character. All other characters that should be escaped
are.
strDN = Replace(strDN, "/", "\/")
objSheet.Cells(k, 1).Value = strDN
k = k + 1
adoRecordset.MoveNext
Loop

adoRecordset.Close

' Format the spreadsheet.
objSheet.Range("A1:A1").Font.Bold = True
objSheet.Select
objExcel.Columns(1).ColumnWidth = 80

' Save the spreadsheet.
objExcel.ActiveWorkbook.SaveAs strExcelPath
objExcel.ActiveWorkbook.Close

' Quit Excel.
objExcel.Application.Quit

' Clean up.
adoConnection.Close
objFile.Close
Set objFile = Nothing
Set objFSO = Nothing
Set objUser = Nothing
Set adoConnection = Nothing
Set adoCommand = Nothing
Set objRootDSE = Nothing
Set adoRecordset = Nothing
Set objSheet = Nothing
Set objExcel = Nothing

Wscript.Echo "Done"

"MattW" <winberrym@xxxxxx> wrote in message
news:c820d84d-c3ad-471a-9e7b-a6b7a4c0b85a@xxxxxxYou must read the file of names in a loop, then for each name run the ADO
query to find the user with the Common Name read from the file. This
involves nested loops. Also, you must concatenate the value of the variable
strLine into the query string. Otherwise, you are searching for the user
with Common Name "strLine". I have not tested, but I think the code below
should work. Watch out for line wrapping:
==========
Option Explicit

Dim strExcelPath, strFilePath, adoConnection, adoCommand, objRootDSE,
strDNSDomain
Dim strFilter, strQuery, adoRecordset, strDN, objExcel, objSheet, k,
objUser, objFSO
Dim objFile, strLine, intIndex

' Check for required arguments.
Const ForReading = 1

' Specify the text file of user names.
strFilePath = "c:\MyFolder\UserList.txt"

' Open the file for read access.
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(strFilePath, ForReading)

' Spreadsheet file to be created.
strExcelPath = "c:\myfolder\selectivelist.xls"

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

' Bind to worksheet.
Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)
objSheet.Name = "Domain User"
objSheet.Cells(1, 1).Value = "User Distinguished Name"

' Use ADO to search the domain for all users.
Set adoConnection = CreateObject("ADODB.Connection")
Set adoCommand = CreateObject("ADODB.Command")
adoConnection.Provider = "ADsDSOOBject"
adoConnection.Open "Active Directory Provider"
Set adoCommand.ActiveConnection = adoConnection

' Determine the DNS domain from the RootDSE object.
Set objRootDSE = GetObject("LDAP://RootDSE")
strDNSDomain = objRootDSE.Get("defaultNamingContext")

adoCommand.Properties("Page Size") = 100
adoCommand.Properties("Timeout") = 30
adoCommand.Properties("Cache Results") = False

k = 2
' Read the text file of names.
Do Until objFile.AtEndOfStream
strLine = Trim(objFile.ReadLine)
' Skip blank lines.
If (strLine <> "") Then
strFilter = "(&(objectCategory=person)(objectClass=user)(cn=" &
strLine & "))"
strQuery = "<LDAP://" & strDNSDomain & ">;" & strFilter _
& ";distinguishedName;subtree"
adoCommand.CommandText = strQuery

' Enumerate all users. Write each user's Distinguished Name to the
' spreadsheet.
Set adoRecordset = adoCommand.Execute
Do Until adoRecordset.EOF
strDN = adoRecordset.Fields("distinguishedName").Value
' Escape any forward slash characters, "/", with the backslash
' escape character. All other characters that should be escaped
are.
strDN = Replace(strDN, "/", "\/")
objSheet.Cells(k, 1).Value = strDN
k = k + 1
adoRecordset.MoveNext
Loop
adoRecordset.Close
End If
Loop

' Format the spreadsheet.
objSheet.Range("A1:A1").Font.Bold = True
objSheet.Select
objExcel.Columns(1).ColumnWidth = 80

' Save the spreadsheet.
objExcel.ActiveWorkbook.SaveAs strExcelPath
objExcel.ActiveWorkbook.Close

' Quit Excel.
objExcel.Application.Quit

' Clean up.
adoConnection.Close
objFile.Close
Set objFile = Nothing
Set objFSO = Nothing
Set objUser = Nothing
Set adoConnection = Nothing
Set adoCommand = Nothing
Set objRootDSE = Nothing
Set adoRecordset = Nothing
Set objSheet = Nothing
Set objExcel = Nothing

Wscript.Echo "Done"

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

> - Show quoted text -

Well, it doesn't produce errors, but it doesn't produce results,
either. As I'm sure you were able to work out, Richard, I had tried
to cobble together 2 of your scripts to do this, thank you for the
resources that you provide!

Ahh, never mind. The first row was skipped, and I only had 1 test
value. Thank you!