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
DocuTerminal
How Long For Me
My Music To Me
SaveMySerials
TypeCount

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 IPhone Text Messages using C# and SQLite
Using Data Protection Manager 2007 For Disaster Recovery on SharePoint
LastPass Extension not enabled after installation in Chrome
Fixing MOSS Search Crawler issue for "The specified address was excluded..."
Creating High Quality Images with C# and GDI

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. DocuTerminal - Online Filing Cabinet solution. Scan, search and archive your paper documents. SaveMySerials - Protect yourself from theft, fire, natural disasters and more by recording your serial numbers My Music To Me - Stream your subsonic music collection to your Sonos wireless system TypeCount - Count how often you type across different computers! ServrTrackr - Monitor your SSL certificates, website uptime, and DNS configurations