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.
Resolving Avaya Denial Event 2378
Fixing Virtual PC 2007 To Run On Windows 7
Blocking A Specific Number from Calling Company Extensions in Avaya CM
Reading a Microsoft Project file (mpp) 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: