Go4Expert

Go4Expert (http://www.go4expert.com/)
-   Web Design, HTML And CSS Forums (http://www.go4expert.com/forums/web-design-forum/)
-   -   VBSCRIPT using Excel to Query Active Directory (http://www.go4expert.com/forums/vbscript-using-excel-query-active-t12578/)

itsmaky 30Jul2008 19:48

VBSCRIPT using Excel to Query Active Directory
 
Hello,

I've been trying to figure out how to do this for a week now and I'm slowly going nuts. :cryin:
Here's the deal:

I've got an Excel spreadsheet with users' first names in column A and last name in column B. I need to write a VBscript to use this spreadsheet in order to query Active Directory for usernames and output those to column C.

By trade I'm a network admin and I can usually frankenstein VBscripts together to do what I need, but this one is too complicated for me. I've found a script which is fairly close to what I need to do, but it just queries AD then outputs whether or not the user account was found. It doesn't output usernames. I'm just not knowledgeable enough to make the necessary modifications. I've pasted it below.
__________________________________________________ _______________________
Query AD for user using first and last name

Code:

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

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)
   
        strFirstname = left(strName, instr(strName, " ")-1)
        strSurname = mid(strName, instr(strName, " ")+1, 1)
                   
    objCommand.CommandText = _
        "SELECT sAMAccountName FROM 'LDAP://dc=dot,dc=state,dc=oh,dc=us' WHERE " _
            & "givenName='" & strName & "' AND sn='" & strSurnameInitial & "'"
    Set objRecordSet = objCommand.Execute

    If objRecordset.RecordCount = 1 Then
        objExcel.Cells(i,2) = "Found"
    Else
        objExcel.Cells(i,2) = "Not found"
    End If
   
    i = i + 1
    objRecordset.Close
Loop

_____________________
Any help would be GREATLY appreciated!
Thanks,
Kevin


All times are GMT +5.5. The time now is 05:35.