+ Post New Thread
Results 1 to 12 of 12
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 ...
  1. #1

    PICNIC's Avatar
    Join Date
    Oct 2010
    Location
    Zooniverse
    Posts
    292
    Thank Post
    187
    Thanked 84 Times in 45 Posts
    Rep Power
    110

    Question 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.

  2. #2
    pubgrub277's Avatar
    Join Date
    Jul 2010
    Location
    Newcastle
    Posts
    89
    Thank Post
    5
    Thanked 19 Times in 11 Posts
    Rep Power
    17
    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:
    Code:
    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.

  3. 4 Thanks to pubgrub277:

    mp4-dave (10th February 2014), PICNIC (1st February 2012), TheScarfedOne (14th May 2013), TomClay (14th May 2013)

  4. #3

    PICNIC's Avatar
    Join Date
    Oct 2010
    Location
    Zooniverse
    Posts
    292
    Thank Post
    187
    Thanked 84 Times in 45 Posts
    Rep Power
    110
    Thanks pubgrub277, that gives me plenty to work with, will have a play with that and see what I can produce.

  5. #4
    A10
    A10 is offline

    Join Date
    May 2012
    Posts
    2
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    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

  6. #5
    pubgrub277's Avatar
    Join Date
    Jul 2010
    Location
    Newcastle
    Posts
    89
    Thank Post
    5
    Thanked 19 Times in 11 Posts
    Rep Power
    17
    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.

  7. Thanks to pubgrub277 from:

    vikpaw (12th May 2012)

  8. #6
    pubgrub277's Avatar
    Join Date
    Jul 2010
    Location
    Newcastle
    Posts
    89
    Thank Post
    5
    Thanked 19 Times in 11 Posts
    Rep Power
    17
    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.

  9. #7
    rpwillis's Avatar
    Join Date
    Aug 2007
    Location
    SalamanderSoft Ltd
    Posts
    182
    Thank Post
    50
    Thanked 63 Times in 51 Posts
    Rep Power
    26
    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.

  10. Thanks to rpwillis from:

    vikpaw (12th May 2012)

  11. #8

    matt40k's Avatar
    Join Date
    Jun 2008
    Location
    Ipswich
    Posts
    4,442
    Thank Post
    369
    Thanked 650 Times in 531 Posts
    Rep Power
    160
    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.

  12. 2 Thanks to matt40k:

    TheScarfedOne (14th May 2013), vikpaw (12th May 2012)

  13. #9

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,779
    Thank Post
    714
    Thanked 1,437 Times in 1,190 Posts
    Rep Power
    359
    Quote Originally Posted by matt40k View Post
    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.

  14. #10

    matt40k's Avatar
    Join Date
    Jun 2008
    Location
    Ipswich
    Posts
    4,442
    Thank Post
    369
    Thanked 650 Times in 531 Posts
    Rep Power
    160
    Quote Originally Posted by vikpaw View Post
    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.

  15. #11

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,779
    Thank Post
    714
    Thanked 1,437 Times in 1,190 Posts
    Rep Power
    359
    He just replied to me too. LinkedIn link is wrong. He is developing but had some delays. So watch this space.

  16. #12

    Join Date
    Jan 2013
    Location
    Gainsborough
    Posts
    23
    Thank Post
    3
    Thanked 2 Times in 2 Posts
    Rep Power
    4
    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.

SHARE:
+ Post New Thread

Similar Threads

  1. [SIMS] export sims timetable to outlook
    By simsstuff in forum MIS Systems
    Replies: 4
    Last Post: 24th November 2011, 12:27 PM
  2. HAP+ SIMS.net Timetable to Static Bookings
    By nickbro in forum Home Access Plus+
    Replies: 2
    Last Post: 26th September 2011, 05:26 PM
  3. Sims Parent Email records to Outlook
    By Simcfc73 in forum MIS Systems
    Replies: 0
    Last Post: 13th January 2010, 01:29 PM
  4. Exporting your Sims.net Diary to Outlook 2003
    By the.damon in forum MIS Systems
    Replies: 1
    Last Post: 5th November 2008, 02:55 PM
  5. export outlook calender to vcs
    By russdev in forum Windows
    Replies: 3
    Last Post: 22nd February 2006, 10:18 AM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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