Extracting Calendar Info From Outlook/Exchange 2010

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,519
Reaction score
4
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
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.) 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

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
PowerPivot does RSS, not XML.
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,519
Reaction score
4
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
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

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
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

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,519
Reaction score
4
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
... 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

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,519
Reaction score
4
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
Top