[sims] Sims Room timetable to outlook calender

[PICNIC]

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.

[pubgrub277]

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:

With CreateObject("Outlook.Application").createitem(1)
   .Subject = 
   .Location = 
   .Start = 
   .End = 
   .Save

 

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.

[PICNIC]

Thanks pubgrub277, that gives me plenty to work with, will have a play with that and see what I can produce.

[A10]

Excellent work.

 

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

[pubgrub277]

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.

[pubgrub277]

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.

[rpwillis]

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.

[matt40k]

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.

[vikpaw]

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.

 

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.

[matt40k]

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.

 

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.

[vikpaw]

He just replied to me too. LinkedIn link is wrong. He is developing but had some delays. So watch this space.

[TomClay]

Hi Pubgrub,

 

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?

 

Thanks,

Tom.