excel document into outlook calendar ?

  Blues Brothers 16:42 30 Jul 07
Locked

Hi

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?

Thanks

  Zaphod 3 16:45 30 Jul 07

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

  Blues Brothers 16:45 30 Jul 07

BTW its Outlook 2003 and ms office 2003 professional

  VoG II 16:53 30 Jul 07

Some code that you may be able to use click here

  VoG II 17:01 30 Jul 07

click here may help as well.

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

  Blues Brothers 18:13 31 Jul 07

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.

schmitt

  VoG II 18:17 31 Jul 07

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

  Blues Brothers 18:20 31 Jul 07

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

  VoG II 18:56 01 Aug 07

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
.Save
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 _
(olNs.GetDefaultFolder(olFolderCalendar)).Activate
Else
Set olApp.ActiveExplorer.CurrentFolder = olNs.GetDefaultFolder(olFolderCalendar)
olApp.ActiveExplorer.Display
End If

Set olNs = Nothing
Set olApp = Nothing

End Sub

  Blues Brothers 21:33 01 Aug 07

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

Thanks

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

Nintendo Switch review: Hands-on with the intuitive modular console and its disappointing games…

1995-2015: How technology has changed the world in 20 years

This abstract video touches on division in our technologic world

Best alternatives to iTunes for Mac | Best music players for macOS: Free your music from the…