Outlook 2003: Export Public Folder Calendar to Excel

Posted on 2010-01-12
Medium Priority
Last Modified: 2012-05-08
Is there a way to export a public folder calendar to Excel?  The public folder calendars are not available when performing a File > Export within Outlook.  Thanks for any input!  
Question by:bsohn417
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
LVL 15

Accepted Solution

Berkson Wein earned 2000 total points
ID: 26298716
Easiest way that I know of is to create a new subfolder under your personal calendar.  Then copy all calendar items from the public calendar.  Select a list view that shows them all to do this.
THen you should be able to export the new subfolder.
Hope this helps.
LVL 76

Expert Comment

by:David Lee
ID: 26302548
Hi, bsohn417.

I can do this with a fairly simple bit of scripting.  Is that an option?

Author Comment

ID: 26306345
BlueDevilFan, sure.  If you have something handy I'd appreciate it.  
LVL 15

Expert Comment

by:Berkson Wein
ID: 26308969
Is this a one time thing, or do you need to do this frequently?  If it's a one time job, a script is probably overkill...
LVL 76

Expert Comment

by:David Lee
ID: 26416193
@bsohn417 - Apologies, I lost track of this question.  Here's the code for doing this.  Follow these instructions to use it.

1.  Start Outlook
2.  Click Tools > Macro > Visual Basic Editor
3.  If not already expanded, expand Microsoft Office Outlook Objects
4.  If not already expanded, expand Modules
5.  Select an existing module (e.g. Module1) by double-clicking on it or create a new module by right-clicking Modules and selecting Insert > Module.
6.  Copy the code from the Code Snippet box and paste it into the right-hand pane of Outlook's VB Editor window
7.  Edit the code as needed.  I included comments wherever something needs to or can change
8.  Click the diskette icon on the toolbar to save the changes
9.  Close the VB Editor

To export a folder

1.  Select the folder in the navigation window.
2.  Run the script.

Outlook 2000 - 2003 includes a security feature that prohibits scripts/programs from accessing Outlook fields that contain or may contain email addresses without the user's knowledge and permission.  This script is going to trigger that built-in security feature.  When it runs a dialog-box is going to pop up warning that an application is accessing your mailbox and asking for permission to continue.  Say "Yes".
Sub ExportItemsToCSV()
    Const MACRO_NAME = "Export Items to CSV"
    Dim objFSO As FileSystemObject, _
        objFile As TextStream, _
        olkFolder As Object, _
        olkItem As Object, _
        olkProp As Outlook.ItemProperty, _
        strHeader As String, _
        strValues As String, _
        strTemp As String, _
        bolHeaderWritten As Boolean
    On Error Resume Next
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    'Edit the file name and path on the next line.'
    Set objFile = objFSO.CreateTextFile("C:\eeTesting\Export2CSV.csv", True)
    Set olkFolder = Application.ActiveExplorer.CurrentFolder
    If olkFolder.DefaultItemType = olAppointmentItem Then
        olkFolder.Items.Sort "[Start]"
        olkFolder.Items.IncludeRecurrences = True
    End If
    For Each olkItem In olkFolder.Items
        strValues = ""
        For Each olkProp In olkItem.ItemProperties
            If (olkProp.Type <> olOutlookInternal) And (olkProp.Name <> "HTMLBody") Then
                If Not bolHeaderWritten Then
                    strHeader = strHeader & Chr(34) & olkProp.Name & Chr(34) & ","
                End If
                strTemp = olkProp.Value
                If Err.Number <> 0 Then
                    msgbox "Name: " & olkProp.Name & vbCrLf & "Type: " & olkProp.Type & vbCrLf & "Value: " & olkProp.Value, vbCritical + vbOKOnly, "Error Reading Property"
                End If
                Select Case olkProp.Name
                    Case "Body", "Subject"
                        strTemp = Replace(olkProp.Value, Chr(34), Chr(34) & Chr(34))
                End Select
                strValues = strValues & Chr(34) & strTemp & Chr(34) & ","
            End If
        If Not bolHeaderWritten Then
            strHeader = Mid(strHeader, 1, Len(strHeader) - 1)
            objFile.WriteLine strHeader
            bolHeaderWritten = True
        End If
        strValues = Mid(strValues, 1, Len(strValues) - 1)
        objFile.WriteLine strValues
    Set objFile = Nothing
    Set objFSO = Nothing
    Set olkContact = Nothing
    msgbox "Export complete.", vbInformation + vbOKOnly, MACRO_NAME
End Sub

Open in new window


Featured Post

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

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

This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
A couple of months ago we ran into an issue that necessitated re-creating our Edge Subscriptions. However, when we attempted to execute the command: New-EdgeSubscription -filename C:\NewEdgeSub_01.xml we received an error indicating that the LDAP se…
To show how to create a transport rule in Exchange 2013. We show this process by using the Exchange Admin Center. Log into Exchange Admin Center.: First we need to log into the Exchange Admin Center. Navigate to the Mail Flow >> Rules tab.:  To cr…
The video tutorial explains the basics of the Exchange server Database Availability groups. The components of this video include: 1. Automatic Failover 2. Failover Clustering 3. Active Manager
Suggested Courses
Course of the Month12 days, 5 hours left to enroll

752 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