excel document into outlook calendar ?

  Blues Brothers 16:42 30 Jul 2007


Is it possible to import an excel document such as an annual shift roster into the calendar on outlook so that the shared calendar on outlook will show others the shift being worked by the individual on a particular week?


  Zaphod 3 16:45 30 Jul 2007

Just adding to my postings as if someone has the answer it could be useful for me.

  Blues Brothers 16:45 30 Jul 2007

BTW its Outlook 2003 and ms office 2003 professional

  VoG II 16:53 30 Jul 2007

Some code that you may be able to use click here

  VoG II 17:01 30 Jul 2007

click here may help as well.

Clearly, to do this you have to be fairly proficient in VBA.

  Blues Brothers 18:13 31 Jul 2007

Thx VoG,

It is definitely possible to use excel documents to send info to the outlok calendar and some of that coding is heading in the write direction. I think I will have to spend a bit of time learning Visual Basic in order to get exactly what I want.
If and when I have success i'll post the response in here.
In the mean time, if there are any VB experts out there that can help I would be very grateful.


  VoG II 18:17 31 Jul 2007

We would need to know the layout of your annual shift roster in Excel.

  Blues Brothers 18:20 31 Jul 2007

I could email it to you if you're happy to take a look

  VoG II 18:56 01 Aug 2007

the following seems to work:

Press ALT + F11 to open the Visual Basic Editor. Then Tools > References and make sure that ‘Microsoft Office Outlook 11.0 Object Library’ is ticked. Insert > Module, paste in the following code then exit the VBE.

Sub RosterToOutlook()

Dim OlAppointment As Outlook.AppointmentItem, olApp As Outlook.Application
Dim iRow As Long, Sht As Worksheet, Code As String, Shift As String, Start As Date, iDuration As Date

'get reference to MS Outlook
On Error Resume Next
Set olApp = GetObject(, "Outlook.Application")
If Err.Number <> 0 Then
Set olApp = CreateObject("Outlook.Application")
End If

On Error GoTo 0

Set Sht = ActiveSheet

For iRow = 10 To Sht.Range("J" & Rows.Count).End(xlUp).Row
If Not IsEmpty(Sht.Range("J" & iRow).Value) Then
Code = Sht.Range("J" & iRow).Value
Select Case Code
Case "E"
Shift = "Earlies"
Start = TimeValue("06:00:00")
iDuration = Sht.Range("K" & iRow).Value
Case "L"
Shift = "Lates"
Start = TimeValue("13:00:00")
iDuration = Sht.Range("K" & iRow).Value
Case "N"
Shift = "Nights"
Start = TimeValue("22:00:00")
iDuration = Sht.Range("K" & iRow).Value
Case "D"
Shift = "Days"
Start = TimeValue("08:00:00")
iDuration = Sht.Range("K" & iRow).Value
Case "O"
Shift = "Time off"
Start = TimeValue("00:00:00")
iDuration = TimeValue("23:59:59")
Case "H", "BH"
Shift = "Holiday"
Start = TimeValue("00:00:00")
iDuration = TimeValue("23:59:59")
End Select

Set OlAppointment = olApp.CreateItem(olAppointmentItem)

With OlAppointment
.Start = CDate(DateValue(Sht.Cells(iRow, 2)) + Start)
.Subject = Shift
.Duration = iDuration
.ReminderSet = False
End With
End If
Next iRow
Set OlAppointment = Nothing
Set olApp = Nothing
End Sub

Select the sheet Test then Tools > Macro > Macros, highlight RosterToOutlook and click the Run button.

The following macro will display the Outlook calendar:

Sub ShowCalendar()

Dim olApp As Outlook.Application
Dim olNs As Namespace

On Error Resume Next
Set olApp = GetObject(, "Outlook.Application")

If Err.Number = 429 Then
Set olApp = CreateObject("Outlook.Application")
End If

On Error GoTo 0

Set olNs = olApp.GetNamespace("MAPI")

If olApp.ActiveExplorer Is Nothing Then
olApp.Explorers.Add _
Set olApp.ActiveExplorer.CurrentFolder = olNs.GetDefaultFolder(olFolderCalendar)
End If

Set olNs = Nothing
Set olApp = Nothing

End Sub

  Blues Brothers 21:33 01 Aug 2007

Thanks Vog for all the time and effort you've put into this project, a great job.


This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Xiaomi Mi Mix 2 review

What went wrong at the Designs of the Year 2017

iPhone X news: Release date, price, new features & specs

Comment utiliser Live Photos ?