Issue with AD + Excel VBScript

[RDSpider]

Hi All,

Currently I am trying to gather information from AD about specific users that I have listed in an excel spreadsheet. The users are located at Domain -> OU -> OU. I want to be able to search for their displayName and match it with the displayName in my spreadsheet and return their department and other details to me.
At this stage I am getting the "table doesnt exist error".
Ill paste in what I have for the VBScript.
Any help would be much appreciated. Cheers.

Const ADS_SCOPE_SUBTREE = 2

Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"

objConnection.Properties("User ID") = "username"
objConnection.Properties("Password") = "password"
objConnection.Properties("Encrypt Password") = TRUE
objConnection.Properties("ADSI Flag") = 1

objConnection.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConnection
objCommand.Properties("Page Size") = 1000
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE
objCommand.Properties("Sort On") = "Department"

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("EXCEL SPREADSHEET LOCATION")
objExcel.Visible = True

i = 1

Do Until objExcel.Cells(i, 1).Value = ""
strName = objExcel.Cells(i,10)
objCommand.CommandText = _
"SELECT displayName, Department FROM 'LDAP://dc=domain,ou=OU1,ou=OU2' WHERE objectCategory='User' " & _
"AND samAccountName='" & strName & "'"
Set objRecordSet = objCommand.Execute
If objRecordset.RecordCount = 1 Then
objExcel.Cells(i,12) = wscript.Echo & objRecordSet.Fields("Department").Value
objRecordSet.MoveNext
Else
objExcel.Cells(i,12) = "Not found"
End If
i = i + 1
objRecordset.Close
Loop
objConnection.Close

 

[tsuji]

>'LDAP://dc=domain,ou=OU1,ou=OU2'
[tt]'LDAP://ou=OU2,ou=OU1,dc=domain'[/tt]

 

[markdmac]

Tsuji has given you the proper syntax. If you want to be able to quickly grab the actual values, check out my FAQ faq329-6614.

I hope you find this post helpful.

Regards,

Mark

Check out my scripting solutions at

http://www.thespidersparlor.com/vbscript

Work SMARTER not HARDER. The Spider's Parlor's Admin Script Pack is a collection of Administrative scripts designed to make IT Administration easier! Save time, get more work done, get the Admin Script Pack.