MIS Systems Thread, Sims Room timetable to outlook calender in Technical; Just noticed another thread from some time ago about importing a teachers calender into outlook, it would be very handy ...
1st February 2012, 09:29 AM #1
Sims Room timetable to outlook calender
Just noticed another thread from some time ago about importing a teachers calender into outlook, it would be very handy if i could import room timetables to outlook. We use MRBS for resource and room booking for IT rooms and a new feature they are building into mrbs is the ability to import ical files. This would save me a massive amount of time during the summer hols. I am going to have a play with it when i get any spare time (not often enough), but if anyone has any ideas they would be greatly received. I know I may need to do 1 room at a time, but even this would save time.
IDG Tech News
1st February 2012, 04:26 PM #2
i've created this as a proof of concept.Create Outlook Timetable.zip just to see if i could do it really.
Paste a staff timetable (or room i suppose) from the standard Sims timetable reports into the sheet (a1:f19).
Run the "Makeweek" Macro and hey presto, from the 06/02/2012 the timetable appears in the outlook calender as appointments for one week (this could easily be looped for half term/term/year).
The code doesn't have any notation yet but i'm adding that shortly. This will only work for my timetable cycle and not everyone's but you could amend the blue table to suit your times of day.
The key really is the coding for creating the outlook items:
Making each of the parameters dynamic for dates/periods is the problem, and depending on how you produce the timetable or report is key.
I'm trying to create a report in sims now that will give me something easier to work with.
3 Thanks to pubgrub277:
PICNIC (1st February 2012), TheScarfedOne (14th May 2013), TomClay (14th May 2013)
1st February 2012, 04:46 PM #3
Thanks pubgrub277, that gives me plenty to work with, will have a play with that and see what I can produce.
10th May 2012, 10:54 AM #4
- Rep Power
I don't do VBA, but I've been trying to play with this. I had a few thoughts on how to improve it, however I've had no success so far. I'm hoping someone with some experience might be able to help:
Presumably all lessons are the same length. So instead of filling in the J column of the spread sheet, you could replace the 'End' statement and variables with a 'Duration=' statement. Also I'd guess that a duration of 00:59:59 would prevent any clashes
' Start' can be used as 'Start= "date" & " " & "time". My idea was to have column J just have the start times of each lesson. If you then had a list of dates for the Mondays in your week A cycle it would be possible to get the script to step through the dates filling in each week and allowing for holidays. A separate spreadsheet with the Monday week B dates and you could do a 2 week timetable for the whole year easily.
We need a method to prevent this adding a blank event where there is nothing timetabled. I'm guessing some sort of IF ="" THEN statement...
There are lots of potential uses for this. I'm looking to use the Room Mailbox feature in Ms Exchange to produce automated room bookings
10th May 2012, 11:19 AM #5
Hi A10, Welcome to the forum.
This was an initial proof of concept to run a basic report in Sims and transfer it to outlook. It is total customizable and doesn't have to rely on the timetable view specified.
thinking about this a little more an easier way would be to use a report like this.Report:Room-Lessons.zip sample output:
Example of room report.xlsx
If you have your timetable and rooms set-up like ours you can get the room, start and end time of each lesson.
This output could then be used to generate you outlook appointments much easier as their would be no blanks to skip.
A two week timetable would need a little more complexity in how you define the dates for an appointment but should be do-able.
Thanks to pubgrub277 from:
10th May 2012, 11:23 AM #6
also, i think duration is calculated after the creation of an item using start and end, so i don't think you can declare "duration" in the way you'd want to.
11th May 2012, 04:50 PM #7
If all you need are ical files, surely it's a lot simpler just to convert the report output to ical format rather thean import to Outlook and then export out again. ical is just a text format so should be simple to convert csv or xml format. You can probably use an xslt stylesheet with xml format.
As someone who has integrated Sims timetables in Exchange I know it's not easy.
BTW be careful of the change to and from BST.
11th May 2012, 11:11 PM #8
I believe @trolley01 who did the SIMS absence cover analysis is creating the SIMS-Exchange Timetable Exporter - SIMS-Exchange Timetable Exporter | Weird & Wonderful IT - Just a note.
2 Thanks to matt40k:
TheScarfedOne (14th May 2013), vikpaw (12th May 2012)
12th May 2012, 08:05 AM #9
Is he still at a school? The LinkedIn link doesn't appear to be for him, and his 'real' LinkinIn profile indicates he no longer works for a school. He may not be developing any more.
Originally Posted by matt40k
12th May 2012, 09:49 PM #10
The school he works at went BSF, so he technical works for Dell. His defo developing it still, spoke to him a little while ago about it.
Originally Posted by vikpaw
13th May 2012, 01:00 PM #11
He just replied to me too. LinkedIn link is wrong. He is developing but had some delays. So watch this space.
14th May 2013, 09:39 AM #12
- Rep Power
Firstly thank you for the zip. I haven't got it spot on yet because we have a 2 week rotation system in place and I would like to be able to do both in one go. Do you have the report or annotated workbook yet?
By simsstuff in forum MIS Systems
Last Post: 24th November 2011, 01:27 PM
By nickbro in forum Home Access Plus+
Last Post: 26th September 2011, 06:26 PM
By Simcfc73 in forum MIS Systems
Last Post: 13th January 2010, 02:29 PM
By the.damon in forum MIS Systems
Last Post: 5th November 2008, 03:55 PM
By russdev in forum Windows
Last Post: 22nd February 2006, 11:18 AM
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)