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

Determining if a computer is a laptop or desktop in C#

Resolving Odd 5 Minute Timeout in HttpWebRequest.
Fixing MOSS/WSS SharePoint errors with Alternate Access Mappings


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