[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

VBscript to search AD againt XLS containing user first and last names

Posted on 2009-04-17
13
Medium Priority
?
453 Views
Last Modified: 2012-05-06
Hi Experts, I have a XLS sheet containing a list of domain user firstname and lastname looks like this:
   First    Last
1 John    Doe
2 Jane    Doe
... about a thousand records
I need a script to search through our AD to see if any matched record returned.. Thanks in advance for your help!
0
Comment
Question by:lo_oscar
  • 6
  • 4
11 Comments
 
LVL 71

Expert Comment

by:Chris Dent
ID: 24170871

Is Excel optional? :) Because I've done this quite a few times but from CSV files an input.

What would you like returned? The distinguished name of the user? And what do you want to do if it bumps into a duplicate name (current script logs it)?

Chris
0
 

Author Comment

by:lo_oscar
ID: 24173083
Hi Chris,
Yeah I can save it as csv, not a problem and I just need a log says the user was found or not found. For duplicated names, can you just log it down as duplicated?
Thanks!
0
 
LVL 14

Expert Comment

by:yehudaha
ID: 24197860
just change this line to your xls file

Set objWorkbook = objExcel.Workbooks.Open("C:\Test.xls")

the file needs to start only with names like this:

john  dowe
mery jane


nothing else need to be in the start with titles like this  :  First    Last

the script will start from the first cells
Const ADS_SCOPE_SUBTREE = 2
 
Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConnection
Set objRootDSE = GetObject("LDAP://rootDSE")
strDomain = "LDAP://" & objRootDSE.Get("defaultNamingContext")
 
objCommand.Properties("Page Size") = 1000
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE 
 
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\Test.xls")
objExcel.Visible = True
 
i = 1
 
Do Until objExcel.Cells(i, 1).Value = ""
    strName = objExcel.Cells(i,1)
    strLastName = objExcel.Cells(i,2)
    objCommand.CommandText = _
    "SELECT sAMAccountName FROM '" & strDomain & "' WHERE objectCategory='user' " & _
        "AND givenName='" & strName & "' AND sn='" & strLastName & "'"
 
    Set objRecordSet = objCommand.Execute
 
    If objRecordset.RecordCount = 1 Then
        objExcel.Cells(i,3) = "Found"
    Else
        objExcel.Cells(i,3) = "Not found"
    End If
    
    i = i + 1
    objRecordset.Close
Loop
 
objConnection.Close
 
Function FindUserPath(User)
                                                
        selectedProperties = "Distinguishedname"
                                                
        Set oCn = CreateObject("ADODB.Connection")
        Set oCmd = CreateObject("ADODB.Command")
        oCn.Provider = "ADsDSOObject"
        oCn.Open "Active Directory Provider"
        Set oCmd.ActiveConnection = oCn
        oCmd.Properties("Page Size") = 1000
        oCmd.Properties("Searchscope") = 2 
        oCmd.CommandText = "SELECT " & selectedProperties & " FROM '" & strDomain & "' WHERE objectCategory='user' AND samAccountName = '" & User & "'"
                                                
        Set oRS = oCmd.Execute
        Do Until oRS.EOF
                FindUserPath = oRS.Fields(selectedProperties).Value
                oRS.MoveNext
        Loop
End Function

Open in new window

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 14

Expert Comment

by:yehudaha
ID: 24197906
i removed a function at the end of the script that wasn't needed
Const ADS_SCOPE_SUBTREE = 2
 
Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConnection
Set objRootDSE = GetObject("LDAP://rootDSE")
strDomain = "LDAP://" & objRootDSE.Get("defaultNamingContext")
 
objCommand.Properties("Page Size") = 1000
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE 
 
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\Test.xls")
objExcel.Visible = True
 
i = 1
 
Do Until objExcel.Cells(i, 1).Value = ""
    strName = objExcel.Cells(i,1)
    strLastName = objExcel.Cells(i,2)
    objCommand.CommandText = _
    "SELECT sAMAccountName FROM '" & strDomain & "' WHERE objectCategory='user' " & _
        "AND givenName='" & strName & "' AND sn='" & strLastName & "'"
 
    Set objRecordSet = objCommand.Execute
 
    If objRecordset.RecordCount = 1 Then
        objExcel.Cells(i,3) = "Found"
    Else
        objExcel.Cells(i,3) = "Not found"
    End If
    
    i = i + 1
    objRecordset.Close
Loop
 
objConnection.Close

Open in new window

0
 

Author Comment

by:lo_oscar
ID: 24198140
Hi Yehudaha,
Thank you for your input. I ran your script and got the error at line 27 (See image attached). BTW, I made the changes as you described.
error.JPG
0
 
LVL 14

Expert Comment

by:yehudaha
ID: 24198772
mmm tested it, works good for me.

try change this part:

 "SELECT sAMAccountName FROM '" & strDomain & "' WHERE objectCategory='user' " & _
        "AND givenName='" & strName & "' AND sn='" & strLastName & "'"
to

"SELECT sAMAccountName FROM 'LDAP://dc=fabrikam,dc=com' WHERE objectCategory='user' " & _
        "AND givenName='" & strName & "' AND sn='" & strLastName & "'"

change the releavent part to your domain in this part  : LDAP://dc=fabrikam,dc=com

last this i attached xls example to be sure you understand me

test.xls
0
 

Author Comment

by:lo_oscar
ID: 24200241
I made the changes you specified, but it still giving me error "Table does not exist" on the same line. Any idea?
0
 
LVL 14

Accepted Solution

by:
yehudaha earned 2000 total points
ID: 24202681
very wiered

please download the txt file i attached and rename the extension to vbs and run it
SearchAD.txt
0
 
LVL 14

Expert Comment

by:yehudaha
ID: 24202688
before you rename it change the xls path and name
0
 

Author Comment

by:lo_oscar
ID: 24208811
It worked this time. Thank you so much!
0
 
LVL 14

Expert Comment

by:yehudaha
ID: 24208912
no problem
glad to help
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a serious pitfall that can happen when deleting shapes using VBA.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question