Results 1 to 6 of 6

Thread: Extracting Calendar Info From Outlook/Exchange 2010

  1. #1
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,021
    Articles
    79
    Blog Entries
    14

    Extracting Calendar Info From Outlook/Exchange 2010



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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!
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  2. #2
    PowerPivot does RSS, not XML.

  3. #3
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,021
    Articles
    79
    Blog Entries
    14
    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.)
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  4. #4
    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.

  5. #5
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,021
    Articles
    79
    Blog Entries
    14
    Quote Originally Posted by Bob Phillips View Post
    ... 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, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  6. #6
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,021
    Articles
    79
    Blog Entries
    14
    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.co...ok-into-excel/

    I also blogged about what I'm doing with it.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •