Excel Geeking: Extracting Outlook Address Book Information To Excel Using VBA

Yeah, I know there are a lot of posts out there about this already. So here’s another.

Truth be told, when I first went searching for items on this topic, I couldn’t find what I was looking for. Granted, it’s not outside the realm of possibility that I simply suck when it comes to a decent Google search. But even when my word choice did happen to land me luckily on a site with information on this topic, I still never really found what I was looking for.

What I really needed was a way to extract some particular columns of data from the Outlook address book. I was looking for Job Title specifically, and Alias if I could get at it. After poking around the web for a bit, and finding some snippets of code here or there, I finally wised up and decided to just follow the Outlook object model.

I love a good object model. Knowing the parent/child relationships from one object to another makes navigating through the application a cinch. For me, the thing I like most about knowing those parent/child relationships, and knowing what the various collections of objects are, is that it lets me iterate through all of the items in those collections.

That’s what I ended up doing with my routine. Once I found the right object that held the properties I was looking for, it was pretty easy to walk back up the model and make sure that I had all the necessary objects in the code.

In this case, because my company is on the Exchange Server, the Outlook object I needed was the ExchangeUser. It has all of the properties I would need, and then some. In addition to what I pulled, you can get address, phone numbers, manager, all kinds of good things. From there I just needed to work backward to figure out what collection did ExchangeUser belong to, and then back up from there. Once that’s been figured out, a simple For..Next loop does the trick.

The only other nuance here is that I need to run this from Excel, so that I could write the data to a worksheet from which I could slice and dice. No problem. Microsoft includes the ability to run other application object models from Excel. All you need to do is to set a reference to the MS Outlook Object Library, and then the Outlook object model is available to you in Excel. The rest is history.

The code for extracting the data is below. The list of properties the ExchangeUser offers can be found here. Enjoy.

Public Sub GetOutlookExchangeUserInformation()
'   This goes into the Global Address List for the MS Exchange Server
'   and returns a selection of data to a worksheet.
'   To get a view off all the potential data poitns that Outlook contains,
'   see the following URL:
'   https://msdn.microsoft.com/en-us/library/microsoft.office.interop.outlook.exchangeuser_properties.aspx

    ' Variable declarations.
    Dim olApp           As Outlook.Application
    Dim olNameSpace     As Namespace
    Dim olAddrList      As AddressList
    Dim olAddrEntry     As AddressEntry
    Dim olExchgnUser    As ExchangeUser
    Dim sh              As Worksheet
    Dim lCnt            As Long

    ' Set the Outlook object variables
    Set olApp = CreateObject("Outlook.Application")
    Set olNameSpace = olApp.GetNamespace("MAPI")
    Set olAddrList = olNameSpace.AddressLists("Global Address List")

    ' Create a new worksheet.
    Set sh = ThisWorkbook.Worksheets.Add

    ' Add some headers for our data.
    With sh
        .Cells(1, 1) = "NAME"
        .Cells(1, 2) = "FIRST NAME"
        .Cells(1, 3) = "LAST NAME"
        .Cells(1, 4) = "ALIAS"
        .Cells(1, 5) = "JOB TITLE"
        .Cells(1, 6) = "DEPARTMENT"
    End With

    ' Start the counter in the second row.
    lCnt = 2

    ' Iterate through the address entires in the address list.
    For Each olAddrEntry In olAddrList.AddressEntries

        ' For each entry, set the an exchange user object.
        ' I'm using t exchange user object because I find that
        ' most companies using Outlook are set up on MS Exchange Server.
        ' You can also use the GetContact whcih will return the
        ' the same information from the Outlook Contact Address Book
        ' (as opposed to the Global Address List).
        Set olExchgnUser = olAddrEntry.GetExchangeUser

        ' Turn off error handling, because occasionally you hit a
        ' record with nothing in it and it throws an error.
        On Error Resume Next

        ' Write the Outlook data to the worksheet.
        With olExchgnUser
            sh.Cells(lCnt, 1) = .Name
            sh.Cells(lCnt, 2) = .FirstName
            sh.Cells(lCnt, 3) = .LastName
            sh.Cells(lCnt, 4) = .Alias
            sh.Cells(lCnt, 5) = .JobTitle
            sh.Cells(lCnt, 6) = .Department
        End With

        ' Because an exchange server could have tens of thousands of entries,
        ' which will cause this routine to run for several minutes,
        ' update the status bar to let the user know that we are in fact
        ' still running and not hung up.
        Application.StatusBar = "Processing record " & lCnt & "..."

        ' If we did not hit an error, increment the counter.
        ' (If we did hit an error, we skip this because if we do not,
        ' we'll get a blank row in the middle of the data set.)
        If Err.Number = 0 Then lCnt = lCnt + 1
        ' Clear any error.
        Err.Clear
        ' Reset the error handling.
        On Error GoTo 0

    Next olAddrEntry

    ' Clear the status bar.
    Application.StatusBar = ""

    ' Prompt the user that we've finished.
    MsgBox "Extract done."

End Sub

Excel Geeking: Counting Recurring Instances of the Same Value Using a Formula

A coworker of mine, who works in another department, had a spreadsheet her group uses to manage what gets pushed out to the stores and when. If you’re in retail, and retail IT especially, you know you don’t mess with the stores anymore than necessary. They are the moneymakers, and you don’t want to be the guy responsible for bringing down their systems to where they can’t sell anything.

This spreadsheet has several columns of dates, with each date corresponding to a specific piece of software that will get rolled out or to which a new release gets pushed. These dates should never overlap. No store gets two pushes in the same day.

This leads me to the challenge they had. With so many multiple columns of dates, they wanted a flag of some sort that would pop up if the same date was entered more than once for that record.

Yeah, I can solve that, I thought. And I did. Here’s how.

Using a mock-up file as a playground, I created four columns of dates. Next I added a fifth column called # Fields Used. In this column I added just a straight count formula. If for some reason any of the columns does not have an entry, I don’t want it counted. It’s always possible that some stores have some software that other stores do not. 

The last column is the Overlap column. Into this column I inserted the following formula:

=IF((COUNTIF(B2:E2,B2)+COUNTIF(B2:E2,C2)+COUNTIF(B2:E2,D2)+COUNTIF(B2:E2,E2))>F2,”OVERLAPPING DATES”,””)

Essentially what I’m doing is counting each instance that the value in each column occurs using a COUNTIF. Then I’m adding them all together to be certain I account for all instances. Granted, that creates a double count, but who cares? I’m already over the limit, so over by a little or by a lot, it’s all the same.  

If the count for all the columns where the value equals the first one is more than one, which we want it never to be, then it triggers the TRUE part of the IF statement, in which case I’m writing text to the cell that says “Overlapping Dates”. You could also add some conditional formatting to give it some pop, but I skipped that part in the screen grab below. 

overlapping_values

There is probably a better way of doing this, maybe utilizing FREQUENCY. Truth be told, I didn’t have a lot of time, and this one involved a pretty easy formula to modify, so I went with it. If any had a better method, I’d love to hear about it. 

 

How Was Your Weekend

If you’re American and like a lot of Americans, you probably celebrated the Fourth of July in something like a stereotypical All-American style. There was family and friends and cookouts and parties and drinking and reds whites and blue everywhere, and of course fireworks.

I decided to go against the grain. I decided to remodel my bathroom. By myself. Why? Cause I’m insane.

The truth of the matter is that the assembly inside the toilet was broken so badly that the water wasn’t filling the tank so much as it was exploding out of the assembly, hitting the top of the tank, and thus leaking down the outside of the tank and onto the tile floor.

Know what else? Those tiles in the bathroom floor started to come up. Mainly around the shower. Since I knew I’d need to take the toilet off to replace the tile, and since I was about to take the toilet off to replace it, I figured I might as well just bite the bullet and redo the whole shabang.

(Caveat: I didn’t touch the tub, which is a vinyl wall tub built right into the sheetrock. I may be insane, but I’m not stupid.)

How’d it go? Short answer: not too bad.

Shorter answer: ouch. I haven’t been this sore in a long time. There was so much up and down, squatting, kneeling, hammer, sawing–I was popping Aleve like a madman.

The truth is, the job itself is not so complex that you can’t wrap your head around it. In a nutshell, it’s just a series of basic steps:

  1. Remove old toilet
  2. Remove old tile
  3. Remove old vanity
  4. Check plywood for rot
  5. Apply thinset, then lay down and screw in the concrete backer board (called Hardiebacker)
  6. Lay out one row over and one row down of tile to determine the arrangement
  7. More thinset and lay down the all the tile
  8. Grout
  9. Install new vanity
  10. Install new toilet
  11. Caulk
  12. Install new baseboard

Easy, right? Looks like a lot of steps, and yes, it will take several days, but no step is so crazy it doesn’t make sense, right?

Well, easy as it may appear, there are a lot of subtleties to doing this job. I’ll spare you the pain of taking you through every little thing I did. This isn’t a how-to blog, and since I’ve done this exactly once and I will likely only do this exactly one time, I’m by no means an expert. What I will offer are a couple of tips I learned (or was told) while doing this:

  1. Ask the folks at Home Depot for advice. They can be incredibly knowledgable and helpful. They like to give advice about this stuff, since they’ve done it thousands of times before.
  2. Write out all of the steps to do that job. Make a detailed list/plan so that you don’t miss anything.
  3. When hammering up the old tile with a hammer and a Wonderbar, make sure you wear goggles. You’ve only got two eyes. Googles saved my eyes multiple times.
  4. If the plywood under the tile is waterstained but the wood is not spongy, you’re safe. Lay the new backer board on top of it.
  5. The Hardiebacker board may feel like you can break through it easy, but you can’t. Use a saw.
  6. Measure three times, then measure again. Then cut. If you dare.
  7. Make sure your drill is fully charged. Drilling corrosion resistant screws into the Hardiebacker goes through a lot of juice quickly.
  8. Be aware that, if you’re replacing a vanity that predates your ownership of the house, when you pull it up you might just find the former owner’s teenager son’s porn stash in the false bottom (I kid you not).
  9. Use your level when setting the tile.
  10. When cutting the tile with a tile cutter and not a wet-saw, make sure you score the tile well and put the breaker bar as far up on the tile as possible for a clean break. Or misery will ensue.
  11. Marble is frickin expensive (see number 9 above).
  12. Clean the grout with a sponge as often as possible. Don’t wait for all the grout to be in before you begin cleaning the excess off the tiles. Otherwise, cue the misery.
  13. Make sure you have enough space at the back of your new vanity for your plumbing, especially if your vanity has drawers (and these days, they all do). You will be cutting out part of the back of the vanity. You do not want to be cutting through any part that the ball bearing glides for the drawers might need.
  14. Check the caulk before you check out at Home Depot. It’s not outside the realm of possibility that some idiot returned a half used tube of caulk to the store you shop at, and that Home Depot restocked it without checking it first, only to have you buy it without checking it first.
  15. However long you think something will take, double it. Then your halfway there.

In the end, the bathroom turned out pretty well. There is still work to do. It needs a coat or two of paint. I still need to get new caulk and finish that up. Once the caulk is done, I can put the baseboard trim on. I need to scrape the ceiling and repaint it since its peeling. But this is minor stuff compared to what I spent two and a half days doing. 

I’m pretty proud that I was able to accomplish it, especially considering I had no prior experience with this kind of work. In my mind, of course, I can see all the little things I did wrong. In my head all the little things I botched are huge glaring errors. But they’re not really. And I really do like how it looks. 

Below you can see the before, during, and after pictures. 

So, how was your weekend?