Mailing List



NerdyHearn - Blog

<< Back To All Blogs

Querying LDAP from Excel directly with VBScript

Tuesday, July 7th, 2009

I recently had a request to directly query LDAP from within Excel, and seeing as I thought this was a pretty cool use case, I figured I would share my solution. Excel uses all VBScript, which can be edited from directly within Excel. I should note that this is Excel 2007 so I have no idea how this performs in earlier versions of Excel.

My particular example case is to query for a computer name and return the computer's description. A fairly straightforward solution, so I'll present the code below.

To insert VBScript and link it to your Excel worksheet, open up the worksheet, hit ALT-F11, click Insert -> Insert Module and paste the code below.

Function ComputerDescription(ComputerName) As String

    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") = 2


    objCommand.CommandText = "SELECT description FROM 'LDAP://dc=mydc,dc=com' WHERE name = '" & ComputerName & "' AND objectClass = 'computer'"

    Set objRecordSet = objCommand.Execute


    If objRecordSet.RecordCount < 1 Then

        ComputerDescription = "No results found"

    End If




    Set myVal = objRecordSet.Fields("description")


    Dim desc

    For Each desc In myVal.Value

        ComputerDescription = desc





End Function

This could obviously be adapted to do a generic LDAP query, or a number of other types of queries, but for my example we are keeping it simple.

You can then reference the function above directly from a cell by using the following syntax:
=ComputerDescription("mycomputername") and it will
populate the cell with the computer's LDAP description.

LDAPin' Tom Out.


Excel Howto

Related Blogs

Resolving ASP.NET Web.Config Inheritance
Resolving Avaya Denial Event 2378
Fixing MOSS Search Crawler issue for "The specified address was excluded..."
Disabling Outlook restricted extensions


Currently no comments.

Add A Comment



Email Address: (not public, used to send notifications on further comments)


Enter the text above, except for the 1st and last character:

NerdyHearn - Latest tech news relating to C#, ASP.NET, SharePoint, PHP, general development, and more. SaveMySerials - Protect yourself from theft, fire, natural disasters and more by recording your serial numbers