Home
IPhone SMS To Gmail
Consulting
Blog
Software
Projects
Links
About
Contact
 Blog
 Facebook
 Twitter
 LinkedIn
|
<< 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
Using Data Protection Manager 2007 For Disaster Recovery on SharePoint
Restoring Specific Documents from a MOSS 2007 Content Database Backup
Awesome .NET 4: Named and Optional Parameters for Classes and Methods
Comments
Currently no comments.
Add A Comment
Name:
URL:
Email Address: (not public, used to send notifications on further comments)
Comments:
Please enter the text from the image:
|