- Joined
- Mar 13, 2011
- Messages
- 2,531
- Reaction score
- 6
- 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:
Thanks!
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.)
Thanks!