-
Dec 30th, 2009, 11:04 AM
#1
Thread Starter
Junior Member
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
-
Dec 30th, 2009, 02:01 PM
#2
Addicted Member
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!
-
Dec 30th, 2009, 03:29 PM
#3
Thread Starter
Junior Member
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?
-
Dec 31st, 2009, 12:53 AM
#4
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:
For Each objAppointment In objAppointments if objAppointment.Start > dateadd("ww", -2, now) and objAppointment.Start < dateadd("ww", 8, now) then stringStartDate = trim(FormatDateTime(objAppointment.Start, vbShortDate)) stringStartTime = trim(FormatDateTime(objAppointment.Start, vbLongTime)) stringEndDate = trim(FormatDateTime(objAppointment.End, vbShortDate)) stringEndTime = trim(FormatDateTime(objAppointment.End, vbLongTime)) stringSubject = objAppointment.Subject stringSubject = Replace(stringSubject, ",", "|") stringLoc = objAppointment.Location stringLoc = Replace(stringLoc, ",", "|") objOutputFile.WriteLine stringSubject & "," & stringStartDate & "," & stringStartTime & "," & stringEndDate & "," & stringEndTime & ",,,,,,,,,,,," & stringLoc & ",,,,,," 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
-
Dec 31st, 2009, 09:15 AM
#5
Addicted Member
Re: Help with using VB to create a CSV file of my outlook calendar
Originally Posted by fitties
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|