Home
IPhone SMS To Gmail
Consulting
Security Alerts
Blog
Software
Projects
Links
About
Contact
 Blog
 FriendFeed
 Facebook
 Flickr
 Twitter
 Toluu
 Last.fm
 Picasa
 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
First Version of SharePoint Validator now available on CodePlex
Fixing MOSS Search Crawler issue for "The specified address was excluded..."
Determining if a computer is a laptop or desktop in C#
Setting up a real VPN with your every day consumer router and DD-WRT
Fixing MOSS/WSS SharePoint errors with Alternate Access Mappings
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:
|