NerdyHearn
Home
Blog

Contact
Mailing List

Software


Blog
Twitter

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

   

    objRecordSet.MoveFirst

   

    Set myVal = objRecordSet.Fields("description")

   

    Dim desc

    For Each desc In myVal.Value

        ComputerDescription = desc

    Next

   

    objRecordSet.Close

    objConnection.Close

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.

Tags

Excel Howto

Related Blogs

Blocking A Specific Number from Calling Company Extensions in Avaya CM
Creating a PDF with C# and iTextSharp
Validate a Windows Username and Password against Active Directory
Disabling Outlook restricted extensions

Comments

Currently no comments.

Add A Comment

Name:


URL:


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


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