Home
Blog
Software
Contact
Mailing List
IPhone Products
SMS To Gmail
Voicemail To Gmail
Calls To Calendar
Other Products
TiffWizard
Sites
SaveMySerials
How Long For Me
DocuTerminal
 Blog
 Twitter
|
<< 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
Search and Replace in MySQL
Deploying Reporting Services WebParts to SharePoint WSS and MOSS
Determining if a computer is a laptop or desktop in C#
Using Data Protection Manager 2007 For Disaster Recovery on SharePoint
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:
|