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
LVL 1
Mitch SwetskyBusiness AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Outlook

From novice to tech pro — start learning today.