Results 1 to 5 of 5

Thread: Help with using VB to create a CSV file of my outlook calendar

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Nov 2008
    Posts
    20

    Help with using VB to create a CSV file of my outlook calendar

    I am using a VB script to create a csv file of my outlook calendar so I an upload it to my google calendar. I do know there is an automated way to do this, but because of security at my company, i am not able to do this. As the google calender sync process grabs all appointment data and i cant have that. i only want the subject, start and end dates, and location.

    When I perform an outlook csv file creation, i see future appointments which is what i need. When I run my code, i seem to be getting only old or past appointments.

    Id like to grab 2 weeks previous and 8 weeks in the future. How do i do this?

    Any help is greatly appreciated.

    here is my code so far:

    Code:
    Sub ExportAppointmentsToCSVFile()
    On Error Resume Next
    
    'You must set a reference to the Microsoft Scripting Runtime library to use the FileSystemObject
    
    Dim objNS As Outlook.NameSpace
    Dim objAppointments As Outlook.Items
    Dim objCalendarFolder As Outlook.MAPIFolder
    Dim objAppointment As Outlook.AppointmentItem
    Dim objFS As Scripting.FileSystemObject
    Dim objOutputFile As Scripting.TextStream
    Dim stringStartDate As String, stringStartTime As String
    Dim stringEndDate As String, stringEndTime As String
    Dim stringSubject As String, stringLoc As String
    
    Set objNS = Application.GetNamespace("MAPI")
    Set objCalendarFolder = objNS.GetDefaultFolder(olFolderCalendar)
    Set objAppointments = objCalendarFolder.Items
    
    Set objFS = New Scripting.FileSystemObject
    Set objOutputFile = objFS.OpenTextFile("C:\AppointmentExport.csv", ForWriting, True)
    
    'Write header line
    objOutputFile.WriteLine "Subject,Start Date,Start Time,End Date,End Time,All day event,Reminder on/off,Reminder Date,Reminder Time,Meeting Organizer,Required Attendees,Optional Attendees,Meeting Resources,Billing Information,Categories,Description,Location,Mileage,Priority,Private,Sensitivity,Show time as"
    
    For Each objAppointment In objAppointments
        stringStartDate = FormatDateTime(objAppointment.Start, vbShortDate)
        stringStartTime = FormatDateTime(objAppointment.Start, vbLongTime)
        stringEndDate = FormatDateTime(objAppointment.End, vbShortDate)
        stringEndTime = FormatDateTime(objAppointment.End, vbLongTime)
        stringStartDate = Trim(stringStartDate)
        stringStartTime = Trim(stringStartTime)
        stringEndDate = Trim(stringEndDate)
        stringEndTime = Trim(stringEndTime)
        
        stringSubject = objAppointment.Subject
        stringSubject = Replace(stringSubject, ",", "|")
         
        stringLoc = objAppointment.Location
        stringLoc = Replace(stringLoc, ",", "|")
        
        objOutputFile.WriteLine stringSubject & "," & stringStartDate & "," & stringStartTime & "," & stringEndDate & "," & stringEndTime & ",,,,,,,,,,,," & stringLoc & ",,,,,,"
    
    Next
    objOutputFile.Close
    
    Set objNS = Nothing
    Set objAppointment = Nothing
    Set objAppointments = Nothing
    Set objCalendarFolder = Nothing
    Set objFS = Nothing
    Set objOutputFile = Nothing
    End Sub

  2. #2
    Addicted Member
    Join Date
    Apr 2009
    Location
    Toronto, Ontario
    Posts
    242

    Re: Help with using VB to create a CSV file of my outlook calendar

    I think you will get some insight into your problem when you remove On Error Resume Next.

    There is probably a runtime error lurking in there. Try changing the Resume Next to use the correct usage of Goto <label> like this:
    Code:
    Sub ExportAppointmentsToCSVFile()
        On Error Goto ErrHandler
    ......
        Set objOutputFile = Nothing
        Exit Sub
    ErrHandler:
        MsgBox Err.Mumber & " - " & Err.Description
    End Sub
    and then post the error message that appears.

    -EM
    ---
    REMEMBER: If your issue is resolved, use the Thread Tools menu to set it as such, and be sure to rate the posts that help you the most!


    Just because I was jealous of g4hsean!

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Nov 2008
    Posts
    20

    Re: Help with using VB to create a CSV file of my outlook calendar

    i didnt get any error messages.

    after evaluating my output, i noticed that i do have some appointments for the future, but not all are displaying. it seems my recurring appointments are not "exploding" out to the future. any ideas?

    also i only need to see 2 weeks back and 8 weeks in the future. is there a way to limit that?

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Help with using VB to create a CSV file of my outlook calendar

    check the appropriate date, then limit the ones your write out to textfile
    try like
    vb Code:
    1. For Each objAppointment In objAppointments
    2.   if objAppointment.Start > dateadd("ww", -2, now) and objAppointment.Start < dateadd("ww", 8, now) then
    3.     stringStartDate = trim(FormatDateTime(objAppointment.Start, vbShortDate))
    4.     stringStartTime = trim(FormatDateTime(objAppointment.Start, vbLongTime))
    5.     stringEndDate = trim(FormatDateTime(objAppointment.End, vbShortDate))
    6.     stringEndTime = trim(FormatDateTime(objAppointment.End, vbLongTime))
    7.    
    8.     stringSubject = objAppointment.Subject
    9.     stringSubject = Replace(stringSubject, ",", "|")
    10.      
    11.     stringLoc = objAppointment.Location
    12.     stringLoc = Replace(stringLoc, ",", "|")
    13.    
    14.     objOutputFile.WriteLine stringSubject & "," & stringStartDate & "," & stringStartTime & "," & stringEndDate & "," & stringEndTime & ",,,,,,,,,,,," & stringLoc & ",,,,,,"
    15.   end if
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  5. #5
    Addicted Member
    Join Date
    Apr 2009
    Location
    Toronto, Ontario
    Posts
    242

    Re: Help with using VB to create a CSV file of my outlook calendar

    Quote Originally Posted by fitties View Post
    i didnt get any error messages.

    after evaluating my output, i noticed that i do have some appointments for the future, but not all are displaying. it seems my recurring appointments are not "exploding" out to the future. any ideas?

    also i only need to see 2 weeks back and 8 weeks in the future. is there a way to limit that?
    Did you remove the On Error Resume Next?
    ---
    REMEMBER: If your issue is resolved, use the Thread Tools menu to set it as such, and be sure to rate the posts that help you the most!


    Just because I was jealous of g4hsean!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width