• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 417
  • Last Modified:

Outlook Import calendar events from Excel I'm having a problem with the reminder

I have an excel file that I want to be able to import to outlook. (Attached is a sample)
I have the reminderon set to true. but the reminder is not checked when I view the item in Outlook.

Am I missing something?
Walther-Apr-10.xls
0
Mitch Swetsky
Asked:
Mitch Swetsky
  • 7
  • 6
1 Solution
 
Chris BottomleySoftware Quality Lead EngineerCommented:
Can you provide your code that reads the excel file and creates the appointments?

Chris
0
 
Mitch SwetskyBusiness AnalystAuthor Commented:
The code below tahes a file that has names in col A and dates across in row 2.
I loop through each day capture the text for each row and wcopy it to a sheet for each name.
Then I save individual sheets with range name for import
Sub MakeCal()
'
' Macro1 Macro
' Macro recorded 4/5/2010 by Mitch Swetsky
'

'Need Hotline schedule to have date XX/XX/XXXX in Cell H1
Dim lastRow As Long
Dim lastCol As Long
Dim RSEName, RSEN, SchMo, SchDay, SchNm, SchSt, SchEnd, CurSheet, RSENameFinal


    ChDir "C:\Temp"
    Workbooks.Open Filename:="C:\Temp\DFE 2010 Schedule.xls"
    
    'Get Month of Schedule for Schedule month
    SchMo = Range("H1")
    SchMo = Format((SchMo), "mmm-yy")
    
    'Copy into new book and re-name as Month & year
    ActiveSheet.Copy Before:=Workbooks("MakeCal.xls").Sheets(1)
    Selection.Copy
    ActiveSheet.Name = SchMo
    
    'Close original schedule don't save any changes
    Windows("DFE 2010 Schedule.xls").Activate
    ActiveWorkbook.Close SaveChanges:=False
    
'Get RSE names in column and make a new sheet for each RSE until blank in row
    Range("A3").Select
        
      RSEN = 0
   Do While ActiveCell.Value <> ""

        RSEName = ActiveCell.Value
        'Add new sheet and Name as RSE
        Sheets.Add Before:=Workbooks("MakeCal.xls").Sheets(SchMo)
        ActiveSheet.Name = RSEName
        ActiveSheet.Select
      'Copy Header row from Format sheet
        Sheets("CalendarFormat").Select
        Range("A1").Select
        Selection.EntireRow.Copy
        Sheets(RSEName).Select
        Range("A1").Select
        ActiveSheet.Paste
    
    Sheets(SchMo).Activate
    ActiveCell.Offset(1, 0).Select
      RSEN = RSEN + 1
   Loop
       
    
'Get HL data from First date Col & create Correct format for cal start-end date
  Range("C2").Select

Do Until SchD = DatePart("d", DateSerial(Year(SchDay), Month(SchDay) + 1, 0), vbMonday, vbFirstFourDays)
   
 'Reset Hotline times from previous loops
   
   SchSt = ""
   SchEnd = ""
   
'Start at C2 or move 1 col right
   Sheets(SchMo).Select
   
 If ActiveCell = "" Then
    If ActiveCell.Row = RSEN + 3 Then
      ActiveCell.Offset(-(RSEN + 1), 1).Select
    ElseIf ActiveCell.Row = RSEN - 4 Then
      AcActiveCell.Offset(-1, 1).Select
    End If
 End If
 
'Get day # from row 2 and make date
    SchD = ActiveCell.Value
    SchDay = Format((SchMo), "mm/" & SchD & "/yyyy")
    Selection.NumberFormat = "m/d/yy;@"

'Get HL Shift save as SchNm
    'Find correct column depending on active current cell in loop
    ActiveCell.Offset(1, 0).Select
    Selection.Copy
    SchNm = ActiveCell.Value
    If SchNm = "" Then
        ActiveCell.Offset(-1, 1).Select
        SchD = ActiveCell.Value
        SchDay = Format((SchMo), "mm/" & SchD & "/yyyy")
        Selection.NumberFormat = "m/d/yy;@"

        ActiveCell.Offset(1, 0).Select
        Selection.Copy
        SchNm = ActiveCell.Value
        If SchNm = "" Then
            ActiveCell.Offset(-1, 1).Select
            SchD = ActiveCell.Value
            SchDay = Format((SchMo), "mm/" & SchD & "/yyyy")
            Selection.NumberFormat = "m/d/yy;@"

            ActiveCell.Offset(1, 0).Select
            Selection.Copy
            SchNm = ActiveCell.Value
        End If
    End If
    
    If SchD = 1 Then
        RSchMo = DatePart("M", SchMo) - 1
        RSchyr = DatePart("yyyy", SchMo)
        RemindSchDay = RSchMo & "/" & 28 & "/" & RSchyr
        Selection.NumberFormat = "m/d/yy;@"
    Else
        RemindSchDay = Format((SchMo), "mm/" & SchD - 1 & "/yyyy")
    End If
'If End is blank then start at first sheet for new date
    If SchEnd = "" Then
        ActiveWorkbook.Worksheets(1).Select
    End If
    
'Copy Dates into RSE sheets
 Do Until ActiveSheet.Name = SchMo
  CurSheet = ActiveSheet.Name
  Sheets(CurSheet).Activate
   
'If End is blank then start at first sheet for new date
'If Next sheet is cal (aka: SchMo) then go to top of next date
   If SchEnd = "" Then
        ActiveWorkbook.Worksheets(1).Select
   Else
        ActiveSheet.Next.Select
        If ActiveSheet.Name = SchMo Then
            ActiveCell.Offset(-(RSEN + 1), 1).Select
            SchD = ActiveCell.Value
        End If
   End If
   
    'Go to beginning of RSE sheet if blank
  If Range("A2").Value = "" Then
    Range("A2").Select
  End If

    'Go to RSE sheet Paste HL Nickname
    Selection.NumberFormat = "@"
 If SchNm = "V" Or SchNm = "V/2" Then
    ActiveCell = "Vacation"
 ElseIf SchNm = "P" Then
    ActiveCell = "Off Hotline"
 Else
    ActiveCell = SchNm & " Hotline"
 End If
    ActiveCell.Offset(0, 1).Select
  
  'Convert nickname to SchSt & SchEnd Hours
   Select Case SchNm
   Case "8-3"
     SchSt = "8:00:00 AM"
     SchEnd = "3:00:00 PM"
   Case "9-5"
     SchSt = "9:00:00 AM"
     SchEnd = "5:00:00 PM"
   Case "10-5"
     SchSt = "10:00:00 AM"
     SchEnd = "5:00:00 PM"
   Case "11-6"
     SchSt = "11:00:00 AM"
     SchEnd = "6:00:00 PM"
   Case "12-8"
     SchSt = "12:00:00 PM"
     SchEnd = "8:00:00 PM"
   Case "10-1"
     SchSt = "10:00:00 AM"
     SchEnd = "1:00:00 PM"
   Case "12-3"
     SchSt = "12:00:00 PM"
     SchEnd = "3:00:00 PM"
   Case "1-5"
     SchSt = "1:00:00 PM"
     SchEnd = "5:00:00 PM"
   Case "1-6"
     SchSt = "1:00:00 PM"
     SchEnd = "6:00:00 PM"
   Case "5-8"
     SchSt = "5:00:00 PM"
     SchEnd = "8:00:00 PM"
   Case "V"
     SchSt = "8:00:00 AM"
     SchEnd = "5:00:00 PM"
   Case "P"
     SchSt = "8:00:00 AM"
     SchEnd = "5:00:00 PM"
   End Select
    
    'Paste Dates in Cols B and D Hotline times in C and E, True for reminder
    Selection.NumberFormat = "m/d/yy;@"
    ActiveCell = SchDay
    ActiveCell.Offset(0, 1).Select
    Selection.NumberFormat = "[$-409]h:mm:ss AM/PM;@"
    ActiveCell = SchSt
    ActiveCell.Offset(0, 1).Select
    Selection.NumberFormat = "m/d/yy;@"
    ActiveCell = SchDay
    ActiveCell.Offset(0, 1).Select
    Selection.NumberFormat = "[$-409]h:mm:ss AM/PM;@"
    ActiveCell = SchEnd
    ActiveCell.Offset(0, 1).Select
    Selection.NumberFormat = "@"
    ActiveCell = "True"
    ActiveCell.Offset(0, 1).Select
    Selection.NumberFormat = "m/d/yy;@"
    ActiveCell = RemindSchDay
    ActiveCell.Offset(0, 1).Select
    Selection.NumberFormat = "[$-409]h:mm:ss AM/PM;@"
    ActiveCell = SchSt
    ActiveCell.Offset(1, 0).Select
    
    Range(Selection, Selection.End(xlToLeft)).Select
    CurSheet = ActiveSheet.Name
    
' Get next HL shift, Copy to SchNm. If blank Loop to new day
    Sheets(SchMo).Select
    ActiveCell.Offset(1, 0).Select
    Selection.Copy
    SchNm = ActiveCell.Value
    Sheets(CurSheet).Activate

    If SchNm = "" Then
        Exit Do
    End If
    

 Loop
 
Loop

'Create a new workbook for each RSE
Do Until ActiveSheet.Name = SchMo
 ActiveWorkbook.Worksheets(1).Select
 RSENameFinal = ActiveSheet.Name
 Sheets(RSENameFinal).Move
    ActiveWorkbook.SaveAs Filename:="C:\Temp\" & RSENameFinal & "_" & SchMo & ".xls", FileFormat _
        :=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
        False, CreateBackup:=False
     
 lastRow = Range("A" & Rows.Count).End(xlUp).Row
 lastCol = Range(Columns.Count & ":1").End(xlToRight).Column
 ActiveWorkbook.Names.Add Name:="Calendar", RefersTo:=ActiveSheet.Range(Cells(1, 1), Cells(lastRow, lastCol))
     
 ActiveWorkbook.Close SaveChanges:=True

Loop

Excel.Application.Quit
ActiveWorkbook.Close SaveChanges:=False

End Sub

Open in new window

AprilCalImage.JPG
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
AS far as I can see the supplied has nothing to do with uploading teh data to outlook, am I wrong?

Chris
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Mitch SwetskyBusiness AnalystAuthor Commented:
No, you are correct.
The attachment above (Walther-Apr-10.xls ) is a small sample of the result
The code creates a file that the  user then goes to outlook File import from file>excel> etc....
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
Ah, diferent approach then.  I'll try and rethink a response

Chris
0
 
Mitch SwetskyBusiness AnalystAuthor Commented:
Thanks for for time, I really appreciate any assistance and chance to learn.
0
 
Mitch SwetskyBusiness AnalystAuthor Commented:
I recently changed something in the field mapping and now I can't import the files at all. It looks like the events are beinig imported but nothing happens.
Is there an outlook macro that can be created to import the events?
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
Probably, indeed for my holidays I use a speadsheet into which I place my holiday details and then if I edit them in the sheet or outlook the other application updates accordingly.

Chris
0
 
Mitch SwetskyBusiness AnalystAuthor Commented:
I am an intermeadite coder with little outlook experience.

Can you suggest anything I can do to get the events imported again?
I dont know how but I must have changed or deleted the field mappings, because I con't import anything now from a file that previously worked.
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
I intend to look at it but i'm on a different challenge at the moment
0
 
GrahamMandenoCommented:
There are two problems:
  1. The column header "Reminderonoff" is not automatically mapping.  Change it to "Reminder on/off"
  2. The content of the cells in that column is a text value "True" and this is not being correctly interpreted by Outlook as a boolean value.  At line 206, instead of:
            ActiveCell = "True"
    try:
            ActiveCell = True
--
Graham

0
 
Mitch SwetskyBusiness AnalystAuthor Commented:
Graham, Thank you very much. Your solution was right on. I made the changes and everything works.
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
Mswetsky:

I'm glad you got an answer, I can only apologise for the increased workload that meant once I understood the approach you had used meant I wasn't in a position to do something useful and timely.

Chris
0
 
Mitch SwetskyBusiness AnalystAuthor Commented:
Thanks for your efforts! I do realize that you are underpaid :-) and a fringe benefit for me.
I appreciate any help and chances to learn.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now