?
Solved

import excel 2010 into outlook calendar 2010

Posted on 2011-05-05
5
Medium Priority
?
1,975 Views
Last Modified: 2012-05-11
how do i import directly from excel 2010 into microsoft office calendar 2010?  Currently I'm saving the 2010 excel file into a 2003-2007 workbook (that has a named range within it) and then importing it into office calendar 2010.  This is a pain.

the excel 2010 columns look like this

dater        all_day    subjecter
2/1/2011     1           visit aunt jane
2/2/2011     1           goto grocery store
2/3/2011     1           renew EE

Thanks.
0
Comment
Question by:sappledo
  • 2
4 Comments
 
LVL 2

Expert Comment

by:srjacob
ID: 35703582
Go to calendar view in Outlook

Select File tab

Select Open->Import

Select Import from another program of file

Click Next

Try Microsoft Excel 97-2003.  Same the file from Excel 2010 in this format first.
0
 

Author Comment

by:sappledo
ID: 35726005
there is no optoin for Excel 2010 format to import.  I get an error when I try to import a 2010 into a 97-2003 format.

any other suggestions?
0
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 2000 total points
ID: 35738674
The routine below creates an Outlook calendar event. Most event types are supported including recurring events. See the documentation in the code for a description of the parameters.

With the code in place you can write a simple macro like this to push your events into Outlook without doing any importing or exporting:

Public Sub PushMyDataToOutlook()

    Dim Row As Long

    For Row = 2 To 1000
        If Len(Sheet1.Columns("A").Rows(Row).Value) > 0 Then
            AddOutlookEvent Sheet1.Columns("A").Rows(Row).Value, Subject:=Sheet1.Columns("C").Rows(Row).Value, AllDayEvent:=Sheet1.Columns("B").Rows(Row).Value
        End If
    Next Row

End Sub

Here are the support routines. Use only one.

[Begin Code Segment]

Public Sub AddOutlookEvent( _
      ByVal StartDateTime As Date, _
      ByVal Subject As String, _
      Optional ByVal EndDateTime As Date, _
      Optional ByVal Duration As Long, _
      Optional ByVal Location As String, _
      Optional ByVal Body As String, _
      Optional ByVal ReminderMinutesBeforeStart As Long = 15, _
      Optional ByVal Remind As Boolean, _
      Optional ByVal BusyStatus As Outlook.OlBusyStatus = Outlook.olBusy, _
      Optional ByVal AllDayEvent As Boolean, _
      Optional ByVal Recurring As Boolean, _
      Optional ByVal Recurrence As Outlook.OlRecurrenceType, _
      Optional ByVal Interval As Long = 1, _
      Optional ByVal DaysOfWeek As Outlook.OlDaysOfWeek, _
      Optional ByVal DayOfMonth As Long, _
      Optional ByVal MonthOfYear As Long, _
      Optional ByVal Instance As Long, _
      Optional ByVal RecurrenceInterval As Long = 1, _
      Optional ByVal Occurrences = 0, _
      Optional ByVal RecursUntil As Date _
   )

' Add a calendar event to Outlook. To use include a reference to Microsoft
' Outlook X Object Library.
'
' Syntax
'
' AddOutlookEvent(StartDateTime, Subject, [EndDateTime], [Duration], [Location], [Body], [ReminderMinutesBeforeStart],
'   [Remind], [BusyStatus], [AllDayEvent], [Recurring], [Recurrence], [Interval], [DaysOfWeek], [DayOfMonth],
'   [MonthOfYear], [Instance], [RecurrenceInterval], [Occurrences], [RecursUntil]
'
' StartDateTime - The start date and time of the event.
'
' Subject - The subject line of the event.
'
' EndDateTime - The end date and time of the event. Optional. If Duration is
'   specified then Duration has precedence. If neither EndDateTime or Duration
'   are specified then Duration defaults to 60 minutes.
'
' Location - The location of the event. Optional. Default is empty.
'
' Body - The body of the event. Optional. Default is empty.
'
' ReminderMinutesBeforeStart - The number of minutes prior to the start of the
'   to display the reminder. Optional. If omitted then
'   ReminderMinutesBeforeStart defaults to 15 minutes.
'
' Remind - Set to True to remind, False to not remind. Optional. Default is
'   False.
'
' BusyStatus - Set to one of olBusy, olFree, olOutOfOffice, or olTentative.
'
' AllDayEvent - Set to True if an all day event, False otherwise. Optional.
'   Default it False.
'
' Recurring - Set to True if a recurring event, False otherwise. Optional.
'   Default is False.
'
' Recurrence - Set to one of olRecursDaily, olRecursMonthly, olRecursMonthNth,
'   olRecursWeekly, olRecursYearly, or olRecursYearNth. Optional. Default is
'   olRecursDaily. Ignored if Recurring is False.
'
' Interval - Set to the period interval between recurring events. Optional.
'   Default is 1. Ignored if Recurring is False.
'
' DaysOfWeek - Set to the days of the week on which the event occurs. Set to
'   one or more of olSunday, olMonday, ..., olSaturday. Sum multiple values to
'   set multiple days of the week. Optional. Default is olMonday. Applicable if
'   Recurrence is olRecursDaily, olRecursMonthNth, olRecursWeekly, or
'   olRecursYearNth. Ignored if Recurring is False.
'
' DayOfMonth - The day of the month on which the event occurs. Optional.
'   Default is the current day of month. Applicable if Recurrence is
'   olRecursMonthly or olRecursYearly. Ignored if Recurring is False.
'
' MonthOfYear - The month of the year on which the event occurs. Optional.
'   Default is the current month. Applicable if Recurrence is olRecursYearly or
'   olRecursYearNth. Ignored if Recurring is False.
'
' Instance - The count for which the recurrence pattern is valid for a given
'   interval. Optional. Default is the instance of the current date. Applicable
'   if recurrence is olRecursMonthNth and olRecursYearNth.
'
' RecurrenceInterval - The number of periods between occurences. Optional.
'   Default is 1. Applicable if recurrence is olRecursDaily, olRecursMonthly,
'   olRecursMonthNth, or olRecursWeekly.
'
' Occurrences - The total number of occurences. Optional. Default is zero. If
'   zero then RecursUntil is used. If RecursUntil is also omitted then the
'   event recurs indefinately.
'
' RecursUntil - The last occurrence date. Optional. Default is zero. If zero
'   and Occurrences is also zero then the event recurs indefinately.
   
   Dim OutlookApplication As Outlook.Application
   Dim Appointment As Outlook.AppointmentItem
   Dim RecurrencePattern As Outlook.RecurrencePattern
   
   Set OutlookApplication = New Outlook.Application
   Set Appointment = OutlookApplication.CreateItem(Outlook.olAppointmentItem)
   
   With Appointment
      .Start = StartDateTime
      If Duration > 0 Then
         .Duration = Duration
      ElseIf EndDateTime > 0 Then
         .End = EndDateTime
      Else
         .Duration = 60
      End If
      .Subject = Subject
      .Location = Location
      .Body = Body
      .ReminderMinutesBeforeStart = ReminderMinutesBeforeStart
      .ReminderSet = Remind
      .BusyStatus = BusyStatus
      .AllDayEvent = AllDayEvent
      If Recurring Then
         Set RecurrencePattern = .GetRecurrencePattern
         If DaysOfWeek = 0 Then DaysOfWeek = olMonday
         If DayOfMonth = 0 Then DayOfMonth = Day(Now)
         If MonthOfYear = 0 Then MonthOfYear = Month(Now)
         If Instance = 0 Then Instance = Int((Day(Now) - 1) / 7) + 1
         With RecurrencePattern
            .RecurrenceType = Recurrence
            Select Case Recurrence
               Case olRecursDaily
                  .Interval = Interval
                  .DayOfWeekMask = DaysOfWeek
               Case olRecursMonthly
                  .Interval = Interval
                  .DayOfMonth = DayOfMonth
               Case olRecursMonthNth
                  .Interval = Interval
                  .Instance = Instance
                  .DayOfWeekMask = DaysOfWeek
               Case olRecursWeekly
                  .Interval = Interval
                  .DayOfWeekMask = DaysOfWeek
               Case olRecursYearly
                  .DayOfMonth = DayOfMonth
                  .MonthOfYear = MonthOfYear
               Case olRecursYearNth
                  .Instance = Instance
                  .DayOfWeekMask = DaysOfWeek
                  .MonthOfYear = MonthOfYear
            End Select
            .PatternStartDate = Int(StartDateTime)
            .StartTime = StartDateTime - Int(StartDateTime)
            If Occurrences = 0 Then
               If RecursUntil > 0 Then
                  .PatternEndDate = Int(RecursUntil)
               Else
                  .NoEndDate = True
               End If
            Else
               .Occurrences = Occurrences
            End If
         End With
      End If
      .Save
   End With
   
End Sub

End Code Segment]

Below is a late binding version of the above routine that does not require a library reference to Outlook.

[Begin Code Segment]

Public Sub AddOutlookEvent( _
      ByVal StartDateTime As Date, _
      ByVal Subject As String, _
      Optional ByVal EndDateTime As Date, _
      Optional ByVal Duration As Long, _
      Optional ByVal Location As String, _
      Optional ByVal Body As String, _
      Optional ByVal ReminderMinutesBeforeStart As Long = 15, _
      Optional ByVal Remind As Boolean, _
      Optional ByVal BusyStatus As Long = 2, _
      Optional ByVal AllDayEvent As Boolean, _
      Optional ByVal Recurring As Boolean, _
      Optional ByVal Recurrence As Long, _
      Optional ByVal Interval As Long = 1, _
      Optional ByVal DaysOfWeek As Long, _
      Optional ByVal DayOfMonth As Long, _
      Optional ByVal MonthOfYear As Long, _
      Optional ByVal Instance As Long, _
      Optional ByVal RecurrenceInterval As Long = 1, _
      Optional ByVal Occurrences = 0, _
      Optional ByVal RecursUntil As Date _
   )

' Add a calendar event to Outlook. To use include a reference to Microsoft
' Outlook X Object Library.
'
' Syntax
'
' AddOutlookEvent(StartDateTime, Subject, [EndDateTime], [Duration], [Location], [Body], [ReminderMinutesBeforeStart],
'   [Remind], [BusyStatus], [AllDayEvent], [Recurring], [Recurrence], [Interval], [DaysOfWeek], [DayOfMonth],
'   [MonthOfYear], [Instance], [RecurrenceInterval], [Occurrences], [RecursUntil]
'
' StartDateTime - The start date and time of the event.
'
' Subject - The subject line of the event.
'
' EndDateTime - The end date and time of the event. Optional. If Duration is
'   specified then Duration has precedence. If neither EndDateTime or Duration
'   are specified then Duration defaults to 60 minutes.
'
' Duration - The duration of the event in seconds. Optional. Default is zero.
'
' Location - The location of the event. Optional. Default is empty.
'
' Body - The body of the event. Optional. Default is empty.
'
' ReminderMinutesBeforeStart - The number of minutes prior to the start of the
'   to display the reminder. Optional. If omitted then
'   ReminderMinutesBeforeStart defaults to 15 minutes.
'
' Remind - Set to True to remind, False to not remind. Optional. Default is
'   False.
'
' BusyStatus - Set to one of olBusy = 2, olFree = 0, olOutOfOffice = 3, or
'   olTentative = 1.
'
' AllDayEvent - Set to True if an all day event, False otherwise. Optional.
'   Default it False.
'
' Recurring - Set to True if a recurring event, False otherwise. Optional.
'   Default is False.
'
' Recurrence - Set to one of olRecursDaily = 0, olRecursMonthly = 2,
'   olRecursMonthNth = 3, olRecursWeekly = 1, olRecursYearly = 5, or
'   olRecursYearNth = 6. Optional. Default is olRecursDaily. Ignored if
'   Recurring is False.
'
' Interval - Set to the period interval between recurring events. Optional.
'   Default is 1. Ignored if Recurring is False.
'
' DaysOfWeek - Set to the days of the week on which the event occurs. Set to
'   one or more of olSunday = 1, olMonday = 2, olTuesday = 4, olWednesday = 8,
'   olThursday = 16, olFriday = 32, olSaturday = 64. Sum multiple values to
'   set multiple days of the week. Optional. Default is olMonday. Applicable if
'   Recurrence is olRecursDaily, olRecursMonthNth, olRecursWeekly, or
'   olRecursYearNth. Ignored if Recurring is False.
'
' DayOfMonth - The day of the month on which the event occurs. Optional.
'   Default is the current day of month. Applicable if Recurrence is
'   olRecursMonthly or olRecursYearly. Ignored if Recurring is False.
'
' MonthOfYear - The month of the year on which the event occurs. Optional.
'   Default is the current month. Applicable if Recurrence is olRecursYearly or
'   olRecursYearNth. Ignored if Recurring is False.
'
' Instance - The count for which the recurrence pattern is valid for a given
'   interval. Optional. Default is the instance of the current date. Applicable
'   if recurrence is olRecursMonthNth and olRecursYearNth.
'
' RecurrenceInterval - The number of periods between occurences. Optional.
'   Default is 1. Applicable if recurrence is olRecursDaily, olRecursMonthly,
'   olRecursMonthNth, or olRecursWeekly.
'
' Occurrences - The total number of occurences. Optional. Default is zero. If
'   zero then RecursUntil is used. If RecursUntil is also omitted then the
'   event recurs indefinately.
'
' RecursUntil - The last occurrence date. Optional. Default is zero. If zero
'   and Occurrences is also zero then the event recurs indefinately.
   
   Dim OutlookApplication As Object ' Outlook.Application
   Dim Appointment As Object ' Outlook.AppointmentItem
   Dim RecurrencePattern As Object ' Outlook.RecurrencePattern
   
   Set OutlookApplication = CreateObject("Outlook.Application") ' New Outlook.Application
   Set Appointment = OutlookApplication.CreateItem(1) ' Outlook.olAppointmentItem = 1
   
   With Appointment
      .Start = StartDateTime
      If Duration > 0 Then
         .Duration = Duration
      ElseIf EndDateTime > 0 Then
         .End = EndDateTime
      Else
         .Duration = 60
      End If
      .Subject = Subject
      .Duration = Duration
      .Location = Location
      .Body = Body
      .ReminderMinutesBeforeStart = ReminderMinutesBeforeStart
      .ReminderSet = Remind
      .BusyStatus = BusyStatus
      .AllDayEvent = AllDayEvent
      If Recurring Then
         Set RecurrencePattern = .GetRecurrencePattern
         If DaysOfWeek = 0 Then DaysOfWeek = 2 ' olMonday = 2
         If DayOfMonth = 0 Then DayOfMonth = Day(Now)
         If MonthOfYear = 0 Then MonthOfYear = Month(Now)
         If Instance = 0 Then Instance = Int((Day(Now) - 1) / 7) + 1
         With RecurrencePattern
            .RecurrenceType = Recurrence
            Select Case Recurrence
               Case olRecursDaily
                  .Interval = Interval
                  .DayOfWeekMask = DaysOfWeek
               Case olRecursMonthly
                  .Interval = Interval
                  .DayOfMonth = DayOfMonth
               Case olRecursMonthNth
                  .Interval = Interval
                  .Instance = Instance
                  .DayOfWeekMask = DaysOfWeek
               Case olRecursWeekly
                  .Interval = Interval
                  .DayOfWeekMask = DaysOfWeek
               Case olRecursYearly
                  .DayOfMonth = DayOfMonth
                  .MonthOfYear = MonthOfYear
               Case olRecursYearNth
                  .Instance = Instance
                  .DayOfWeekMask = DaysOfWeek
                  .MonthOfYear = MonthOfYear
            End Select
            .PatternStartDate = Int(StartDateTime)
            .StartTime = StartDateTime - Int(StartDateTime)
            If Occurrences = 0 Then
               If RecursUntil > 0 Then
                  .PatternEndDate = Int(RecursUntil)
               Else
                  .NoEndDate = True
               End If
            Else
               .Occurrences = Occurrences
            End If
         End With
      End If
      .Save
   End With
   
End Sub

[End Code Segment]

Kevin
0
 

Author Closing Comment

by:sappledo
ID: 36042647
Lots of work put into the code.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Are you looking for the options available for exporting EDB files to PST? You may be confused as they are different in different Exchange versions. Here, I will discuss some options available.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
CodeTwo Sync for iCloud (http://www.codetwo.com/sync-for-icloud?sts=6554) automatically synchronizes your Outlook 2016, 2013, 2010 or 2007 folders with iCloud folders available via iCloud Control Panel. This lets you automatically sync them with…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question