Pull your Global Address Book into Excel

A friend of mine recently asked if there is a way to create an Excel table with employee information from the Outlook Properties dialog box for all employees in his Global Address Book. The Outlook Properties dialog box (seen below) gets its data from the company Active Directory. Active Directory is a special kind of database that is typically used to store organizational data such as employee names, user IDs and location information.

.

It’s often useful to have a table of employee names and contact info. I have pulled this kind of table together in the past with some VBA code. But today, we don’t have to. We can use Microsoft’s free Power Query Add-in to reach into the Active Directory and extract data like email address, employee, department, and telephone number.

.

Today, I’ll show you the steps you need to take to create a refreshable alpha roster of employees and contact info.

.

Step 1: Install Power Query

First, you will need to download and install the free Power Query Add-in.

Note that Microsoft offers Power Query for both Excel 2010 and Excel 2013 in both 32 and 64 bit platforms. Be sure to download the version that matches your version of Excel as well as the platform (32 or 64 bit) of Office your PC is running. Once installed, you’ll need to activate the Add-in by following these steps:

  • Open Excel and look for a Power Query tab on the Excel Ribbon. If you see it, then the Power Query Add-In is already activated. You can skip the remaining steps.
  • Go up to the Excel Ribbon and click File->Options.
  • Choose the Add-Ins option on the left then look at the bottom of the dialog box for the Manage dropdown. Select COM Add-Ins from that dropdown and then click Go.
  • Look for Power Query for Excel in the list of available COM Add-Ins. Check the box next to each one of these options and click OK.
  • Close Excel and restart.

A successful install will result in a new Power Query tab on the Excel Ribbon.

.

.

Step 2: Understand the Active Directory Model

The Active Directory database is huge; it has dozens of tables. It can be difficult to know which field stores which piece of data – especially since the Active Directory field names are often cryptic. To help, I’ve overlaid the Active Directory field names on top of the Outlook properties dialog box to give you an idea which fields we need to capture. It turns out that these fields can be pulled from just three of the tables in Active Directory.


.

.

Step 3: Start a Power Query Data Extract

Click on the Power Query tab and select the From Other Sources command. There, you will find the Active Directory option.


.

Power Query will recognize your Domain and populate the server name for your Global Address book


.

Pressing the OK button will populate the Navigator pane with all the available tables in your Active Directory. You’ll see tons of tables here. Ignore all tables except the user table. Double-click the user table to pull it into the Power Query window.


.

At this point, you’ll have a table that looks like this in your Power Query window. This table is made up of related data sets that can be drilled into.


.

.

Step 4: Remove Unnecessary Columns

The first thing you’ll want to do is delete all columns except for:

  • displayName
  • organizationalPerson
  • person
  • mailRecipient


.

.

Step 5: Select Needed Fields from organizationalPerson

Now it’s time to select the fields you need.

Start by clicking the drill button next to organizationPerson.

This will open up a filter menu.

Click the (Select All Columns) option to clear all the check boxes.

.

Now go down the list of fields and place a check next to:

  • co
  • company
  • department
  • employeeType
  • givenName
  • initials
  • l
  • mail
  • physicalDeliveryOfficeName
  • postalCode
  • st
  • streetAddress
  • title

.

.

Step 6: Select Needed Fields from Person

Next, click the drill button next to Person.

This will open up a filter menu.

Click the (Select All Columns) option to clear all the check boxes.

.

Now go down the list of fields and place a check next to:

  • sn
  • telephoneNumber

.

.

Step 7: Select Needed Fields from mailRecipient

Click the drill button next to mailRecipient.

This will open up a filter menu.

Click the (Select All Columns) option to clear all the check boxes.

.

Now go down the list of fields and place a check next to:

  • mailNickName
  • msExchangeAssistantName

.

.

Step 8: Remove “Dummy” Service Accounts

Because we want this extract to contain only employees, we will want to remove any service account. That is to say, dummy accounts used f6or group email boxes which aren’t tied to individual people. To remove these accounts, find the employeeType field and click the drill down arrow. This will open up a filter menu where you can uncheck the (null) value.

.

.

Step 9: Load Result to Excel

At this point, you can choose to rename and reorder the columns as you see fit.

Once you’re done, click the Close & Load command on the Home tab of the Power Query window.

.

After a few moments, the entire contents of your Global Address book will be imported to a new worksheet. Best of all, you can refresh this table by simply right-clicking anywhere inside this table and choosing the Refresh option.

.

15 thoughts on “Pull your Global Address Book into Excel

  1. AlexJ

    VERY nice.
    But one question. How come the head carpernter is in Accounts Payable? I kinda figgured that J.H.C. should be on the Help Desk.

  2. Omar

    Thank you. I was able to follow along and make that work on our Active Directory. I showed one of our IT guys what I was able to do, and he immediately came up with a possible use for his work.

    It’s nice having some test cases like this working with Power Query. I’m comfortable using MS Query as my starting point for reports, but I’m limited elsewhere. All our data requires ODBC connections to access. Now that I’ve proven to myself that I can do this in Power Query, I plan on testing it further.

    I have a lot of questions though. For example, do all users have to have Power Query in order to refresh the reports?

  3. Florian

    Hmm, in most blogs I read about the point “Active Directory” but can’t find anything. We’re using Office 2013 (on-prem) with Power Query. The only options I have (in category “From other sources”) are: OData Feed, Facebook, other ODBC, Empty/New connection.

    Is it caused by a broken installer or is mine “too new”?

  4. Scott

    Thank you for the well documented steps! It works great for me.

    I’m also trying to pull in the Manager name but it’s not going so well. My steps:
    + drill-down to user table
    + select only manager
    + drill-down to user.manager
    – shows “No columns were found”

    If I click the Record, I can see all the fields including the manager’s display name but can’t figure out how to pull it in to my list.

  5. Charlie

    Scott: I found a Manager value under the OrganizationalPerson object. I also noted that some of my Active Directory setup didn’t quite match the article. I suspect that’s due to customization of the directory by my company

  6. Clark Kent

    Very helpful, thanks. I would note that in Exch 2010 the name of the column “mcExchangeAssistantName” is “msExchAssistantName.”

  7. Darren

    When I try to run the Power Query for my organization and it is not returning the email as I had hoped. Am I doing something wrong or is there a problem in our directory?

  8. Nicole G

    Hi! Thank you for this information! Could you tell me which table/field pulls the information in the Country/Region field on the General Tab in the Address Book?

Leave a Reply

Your email address will not be published. Required fields are marked *