NerdyHearn
Home
Blog

Contact
Mailing List

Software

Active Directory Products
Object Compare
Permission Compare

IPhone Products
Calls To Calendar
SMS To CSV
SMS To Gmail
Voicemail To Gmail

Sites
How Long For Me
SaveMySerials

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

Reading a Microsoft Project file (mpp) in C#
Resolving Odd 5 Minute Timeout in HttpWebRequest.
Fixing MOSS/WSS SharePoint errors with Alternate Access Mappings
Awesome .NET 4: Named and Optional Parameters for Classes and Methods
Blocking A Specific Number from Calling Company Extensions in Avaya CM

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