Our College Calendar is currently housed in Outlook but only accessible to staff is there a way it could be imported from outlook into moodle so that staff, parents and students can access it.
Attached is a VBScript I wrote to import our school calendar from an Exchange Public Folder to Moodle. It uses the MySQL Connector/ODBC to connect to the Moodle database and WebDAV to access the calendar events from an OWA URL.
If you want to try it, you'll need to update the Consts near the top to point to your Moodle database and calendar URL. The Const daysPast determines how far back the script looks for events to transfer. All future events are added to Moodle. The script sets the uuid field of the Moodle event to the uid from Exchange. This allows the script to update events in Moodle if they are changed in Outlook. The sync is only one way; changes in Moodle are not copied back to Outlook.
Let me know if you have questions about the script.
Nathanael
Wow, wish I had found this earlier, now I just need to persuade out network guy to enable public folders in our exchange server.
Would this work with the full version of Outlook as opposed to OWM?
Fantastic!
We keep our school calendar in a public folder so everyone can access it, but I think the script should work with a personal calendar as well. You would just need to change the URL it tries to access (to something like http://server/exchange/username/Calendar).
By using WebDAV, the information is pulled directly from the Exchange server. It's probably possible to write a script that connects to Outlook to get calendar information, but I haven't tried that.
Nathanael
I get an 'Exepcted End' error on line 108 CHar 2
Remove the entire Sub Force(sScriptEng)...End Sub from the end of the script. This code is not used and doesn't work. Accidently got left in.
Attached is a version of the script without the stray code.
Ahh, that's better. Thanks, now I need to setup the Public Calendar and test.
Cheers
Cheers
Hi Natahnael,
Solved the first problem, had to enable anonymous access in IIS as well as in Outlook.
However, now I am getting "The handle is invalid" on line 93 char 5 which appears to be the End If at the end of the major loop just before the Rs.MoveNext
Any ideas?
Solved the first problem, had to enable anonymous access in IIS as well as in Outlook.
However, now I am getting "The handle is invalid" on line 93 char 5 which appears to be the End If at the end of the major loop just before the Rs.MoveNext
Any ideas?
I removed the line that appeared to be trying to write a dot to the screen at each loop and the error went away and the data imported to the database 
However it did not show in the calendar until I made the userid and courseid = 1
Tried to modify the script to do the same thing, which worked, but now it does not recognise that the event has already been added and adds it again and again and again whenever the script is run.
Can someone who knows VBS please help, I have exhausted my knowledge of it!
However it did not show in the calendar until I made the userid and courseid = 1
Tried to modify the script to do the same thing, which worked, but now it does not recognise that the event has already been added and adds it again and again and again whenever the script is run.
Can someone who knows VBS please help, I have exhausted my knowledge of it!
Hi Simon,
Sorry I haven't had a chance to get back to you sooner. I did spend some time looking into your earlier problem but hadn't gotten it figured out...glad you made progress on your own.
Thanks for catching the bug that didn't set the courseid=1. The attached script has the correct SQL statement that doesn't require you to manually make that change.
I don't know why the script would create duplicate records for you. Can you check in the database if the automatically created events have a value in the uuid field? The script uses this field to match Outlook events with Moodle events so it can tell if the event has already been synced.
One possible explanation is that I may have modified the database structure to increase the size of the uuid field. Can you check if the uuid field in your mdl_events table is varchar(36)?
Sorry it has been such a hassle getting this script working. It was really something I just threw together for our purposes here, and I didn't design it to be general purpose. If I have some time in the future, I may try to convert the code to php so it could be integrated into a Moodle block. But that could be awhile.
Nathanael
Thanks Nathanael, I really appreciate the effort.
uuid is varchar(36).
I suspect the duplicate events are from my attempts at hacking it to get it to set a uuid and courseid - both were originally 0. I found that I needed them to be both set for the event to actually appear in Moodle.
Will run this versio of the script and get back to you.
Cheers
Simon
Dropped in my variables and it worked perfectly first time, and second time - no duplicates. What we might do now is strip out the write messages and set it up to run automatically, so that all the clericals have to do is to update the Outlook calendar and wait.
Thanks again - great demonstration of the power of these forums!
Thanks again - great demonstration of the power of these forums!
Hi Nathanael,
This all looks great stuff apart from one small point - you allow anonymous access to your exchange server!? Not many IT managers would be so forgiving as to allow that, just for moodle.
As I know nothing of moodle really and am browsing for this functionality, is there any chance of it working through https as this is how our server is setup.
Regards
Richard
Hi Richard,
Actually, I don't have anonymous access enabled to the Exchange server. Just Integrated authentication, so the connection is made with the credentials of the currently logged in user.
Also, we use https as well, and the script works fine.
Nathanael
Still having troubloe getting this to work. The script seemed to run but no entries were updated, so we removed the section that ends the script on error
If Lcase(Right(Wscript.FullName, 12)) = "\wscript.exe" Then
CreateObject("Wscript.Shell").Run "cscript.exe " & Wscript.ScriptFullName
Wscript.Quit
End if
Then we started to see error messages, at the moment we are getting an error in this section:
Set Rec = CreateObject("ADODB.Record")
Set Rs = CreateObject("ADODB.Recordset")
Error here - and yes I know there is nothing there!
Rec.Open calURL
Set Rs.ActiveConnection = Rec.ActiveConnection
Assuming the script can't open the public calendar, when we enter the URL into a web browser we have to login to get the calendar, is there a way around this?
If Lcase(Right(Wscript.FullName, 12)) = "\wscript.exe" Then
CreateObject("Wscript.Shell").Run "cscript.exe " & Wscript.ScriptFullName
Wscript.Quit
End if
Then we started to see error messages, at the moment we are getting an error in this section:
Set Rec = CreateObject("ADODB.Record")
Set Rs = CreateObject("ADODB.Recordset")
Error here - and yes I know there is nothing there!
Rec.Open calURL
Set Rs.ActiveConnection = Rec.ActiveConnection
Assuming the script can't open the public calendar, when we enter the URL into a web browser we have to login to get the calendar, is there a way around this?
Hi Simon / Everyone,
Did you or anyone else get this script working? Or did you figure out another way to import the Outlook Calendar into Moodle?
Lael
Did you or anyone else get this script working? Or did you figure out another way to import the Outlook Calendar into Moodle?
Lael
Hi Nathanael,
We are using Moodle 1.9.9 Version; the vbx script that you have posted above is it still valid. We are at the moment using Microsoft Exchange Server Outlook 2010.
Await your reply!
Ashutosh