Active Directory Products
Calls To Calendar
SMS To CSV
SMS To Gmail
Voicemail To Gmail
How Long For Me
<< 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"
Set myVal = objRecordSet.Fields("description")
For Each desc In myVal.Value
ComputerDescription = desc
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.
Using Data Protection Manager 2007 For Disaster Recovery on SharePoint
Determining if MOSS is Standard or Enterprise
Resolving Avaya Denial Event 2378
Awesome .NET 4: Named and Optional Parameters for Classes and Methods
Writing console output from a Windows form in C#
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: