Excel LDAP Search

Version 0.58

5/16/2010

download | change log | Sourceforge home

Author: Paul Brinkmann

 

Contents

Overview

Figure 1. Row-by-row search

Excel LDAP Search (ELS) is an Excel add-in that allows LDAP searches to be made from within Microsoft Excel, and the resulting data to be directly imported from the directory server to the Excel document. ELS can be used to add LDAP data to existing data in an Excel document on a row-by-row basis (see Figure 1), or populate a blank sheet based on a custom query.

Limitations: ELS was designed for a fairly basic LDAP environment. Some features that are (currently) not supported are SSL, certificate authentication, and referral following.

I put together a couple of flash demo movies that show typical ELS usage for a running a query to fill in a blank Excel sheet and for adding new row data to existing Excel entries:

Movie: Filling in a blank worksheet
Movie: Adding new row data from LDAP
 

Requirements

Windows Versions (both 32-bit and 64-bit should work)

* - Note that with Vista and 7, you'll be unable to edit the config file when opened from ELS. You'll need to edit the config file w/administrative privileges (it's in the install dir)

Office Versions (I've only tested on English versions)

It uses the Mozilla LDAP SDK so it should work with any LDAP-v3 compliant directory server. If ELS doesn't work with your directory server, please click here to submit a bug report online. It takes all of 30 seconds, and you can help out the next person who'd encounter the same problem as you.

Installation Instructions

Make sure all instances of Excel are closed.

Run the excelldapsearchinstall_x_y.exe file. This will copy the necessary files, and register everything with the system. It also opens up the config file where you can specify your connection parameters.

Installing on Multiple Versions of Excel

If you have multiple versions of Excel installed, only the latest will have the Tools menu item registered. To manually enable it in an older version:

Known Issues

On some systems, the error message "Compile error: Can't find project or library" will be displayed. This indicates Excel is referencing a module that doesn't exist. The solution is to start the macro editor by pressing ALT-F11, then open the "Tools" menu and select "References...". If the "References..." item is greyed out, reset the project first by click the stop icon on the toolbar, or under the "Run" menu there's a "reset" item. Look for any checked items in the list that start with "MISSING:" and uncheck them. Click the "OK" button and close the macro editor. This process is also described in Microsoft KB208218.

Uninstall Instructions

Make sure all instances of Excel are closed

Remove via Control Panel->Add/Remove Programs.

If you manually added the Tools "Run LDAP Search" menu item to more than one version of Excel, you can remove it by clicking "Tools->Customize...", then opening the Tools menu again and dragging the menu item off.

Usage

After install, fire up Excel and take a peek under the "Tools" menu. There should be a "Run LDAP Search" menu item now. Click that to bring up the search GUI.

Excel 2007 users - since there is no "Tools" menu in 2007, this ends up showing up in the "Add Ins" ribbon bar thingy, but seems to work just fine.

The default option is to "Add LDAP data to existing rows" which requires a column of data where each row has an item that uniquely identfies a user (ex., a column of user IDs or e-mail addresses). If you want to get more information (ex., a mailing address or the user's full name) for each row from your directory server, set the column as the "data range" in step 1)

Make sure the correct attribute is selected in step 2), the "User ID" in this case:

By default, the results will be added one column to the right of the data range. If you want to place them further to the right, increase the column offset

Select whatever attribute(s) you want returned. In this example, two new columns will be added, one for the users' full names and another for their e-mails

Click the search button to run the search.

 

The other option is to run a free-form LDAP query and replace all existing data on the sheet WITHOUT PROMPTING with results from the query. You can enter a query manually using the LDAP filter syntax, or click the "<<" button for help building the query filter. This example query will find all the Pauls:

Select which attribute(s) you want returned and click the search button: