1. We have moved from vBulletin to XenForo and you are viewing the site in the middle of the move. Though the functional aspect of everything is working fine, we are still working on other changes including the new design on Xenforo.
    Dismiss Notice

VBSCRIPT using Excel to Query Active Directory

Discussion in 'Web Design, HTML And CSS Forums' started by itsmaky, Jul 30, 2008.

  1. itsmaky

    itsmaky New Member

    Joined:
    Jul 30, 2008
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    0
    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
     

Share This Page