PDA

View Full Version : Extracting Calendar Info From Outlook/Exchange 2010



Ken Puls
2011-03-30, 08:14 PM
I fully intend to figure this out, but just in case someone has already done some of this in the past and can kick me in the right direction...

We're working on an Excel dashboard for our golf course, and trying to get relevant "future" information into it. (Like the weather forecast from this thread (http://www.excelguru.ca/forums/showthread.php?91-Vlookup-for-picture&p=313#post313).) One of the things that got asked for yesterday was if we could see the next week's worth of events on the dashboard. Cool idea!

We currently have a "user" in our exchange system name "Events". We've delegated permissions for a few users to be able to add/manage appointments on their calendar, and then shared view rights with everyone else. The events are all flagged with specific categories (golf course, food and beverage, etc...) (It's an old school method as we're just now in the process of upgrading from Exchange 2003. One day we will investigate/move to proper shared calendars.)

What I'd like to do is bring the "Events" user's calendar appointments into PowerPivot so that I can categorize them appropriately for dashboarding, filter the dates, and pull them into a pivot table that shows what's happening over the next week.

The question is, what's the best angle to go at this? Some thoughts I had:

Is there an XML feed for a users calendar that can be published? (I don't think so, but never hurts to ask.)
I tried to connect to the Exchange server via PowerPivot, but there isn't a SQL database involved here and none of the other feed options are appropriate either.
I could extract the calendar using VBA, but it seems like that might take a while while doing a daily dashboard refresh. (Have to figure out how to pull up events from another users's calendar, but I'm sure I can do that.)
Any other methods that I'm overlooking that might be easier than the VBA route?

Thanks!

Bob Phillips
2011-04-01, 08:17 PM
PowerPivot does RSS, not XML.

Ken Puls
2011-04-02, 06:18 AM
PowerPivot does RSS? It does ATOM, but I haven't been able to figure out how to get an RSS feed into it...

My angle on the XML was actually to create a web query table in Excel, then upload it into PowerPivot.

lacking any better choices, I've resorted to VBA. It's actually surprisingly fast to get an initial list. The issue I'm having is that all the recurring dates come in with the original date. Just need to figure out how to tell what date I'm really looking at. (Can be done, just need the time to dedidcate to it.)

Bob Phillips
2011-04-02, 11:42 AM
Okay, I see. I recently wanted to pull XML into PP directly, and you can imagine how impressed I was when I found that I would have to b#*t#rdise the XML into a particular format of XML that PP would accept. Your solution is just as ridiculous, we shouldn't need to go to such machinations to get data that is in what is supposed to be 'THE' data transport protocol.

Ken Puls
2011-04-04, 06:15 AM
... we shouldn't need to go to such machinations to get data that is in what is supposed to be 'THE' data transport protocol.

Totally agree. I was hoping I could just drop in an RSS feed, or an XML feed and get data. I'm not sure I've ever encountered an ATOM feed in the wild.

Ken Puls
2011-04-07, 08:21 AM
I stumbled across and article by Jimy Pena that pulls back Outlook appointments (recurring appointments and regular appointments) with the actual appointment dates, all in one shot. A nice piece of work that you can find here: http://www.codeforexcelandoutlook.com/blog/2008/08/extract-calendar-data-from-outlook-into-excel/

I also blogged about what I'm doing with it (http://www.excelguru.ca/blog/2011/04/06/an-interesting-use-for-slicers/).