I have a VBA code in Excel that I use to extract calendar information from my
calendar to an Excel sheet. I would like to extract from a public calendar
instead. Is this possible? and if yes, what changes would I need to make to
the code below to do that. Any assistance would be helpful. Thanks


Private Sub GetMeetings_Click()
Dim ol As New Outlook.Application
Dim ns As Outlook.Namespace
Dim olFolder As Outlook.MAPIFolder
Dim appts As Object
Dim appt As Object
Dim date1 As Date, date2 As Date
Dim i As Integer

Set ns = ol.GetNamespace("MAPI")
Set olFolder = ns.GetDefaultFolder(olFolderCalendar)
Set appts = olFolder.Items

date1 = InputBox("Starting Date: ", "Start Date")
date2 = InputBox("End Date: ", "End Date")

i = 2
For Each appt In appts
If appt.Start = date1 And appt.Start date2 Then
Sheets("rawdata").Cells(i, 2).Value = appt.ConversationTopic
Sheets("rawdata").Cells(i, 4).Value = Format(appt.Start, "short date")
Sheets("rawdata").Cells(i, 5).Value = Format(appt.Start, "medium
time")
Sheets("rawdata").Cells(i, 6).Value = Format(appt.End, "medium time")
Sheets("rawdata").Cells(i, 7).Value = appt.Location
Sheets("rawdata").Cells(i, 3).Value = appt.Organizer
Sheets("rawdata").Cells(i, 8).Value = appt.Body
Sheets("rawdata").Cells(i, 9).Value = appt.RequiredAttendees
Sheets("rawdata").Cells(i, 10).Value = appt.OptionalAttendees
i = i + 1
End If
Next appt

Set ol = Nothing
Set ns = Nothing
Set appt = Nothing
End Sub

You'd need to replace your GetDefaultFolder statement with a statement that returns the public folder. To get a non-default folder, you need to walk the folder hierarchy using the Folders collections or use a function that does that for you. See http://www.outlookcode.com/d/code/getfolder.htm and, especially for public folders, http://www.outlookcode.com/codedetail.aspx?id=1164
--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003
http://www.turtleflock.com/olconfig/index.htm
and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers
http://www.outlookcode.com/jumpstart.aspx

"WD" wrote in message ...
I have a VBA code in Excel that I use to extract calendar information from my
calendar to an Excel sheet. I would like to extract from a public calendar
instead. Is this possible? and if yes, what changes would I need to make to
the code below to do that. Any assistance would be helpful. Thanks


Private Sub GetMeetings_Click()
Dim ol As New Outlook.Application
Dim ns As Outlook.Namespace
Dim olFolder As Outlook.MAPIFolder
Dim appts As Object
Dim appt As Object
Dim date1 As Date, date2 As Date
Dim i As Integer

Set ns = ol.GetNamespace("MAPI")
Set olFolder = ns.GetDefaultFolder(olFolderCalendar)
Set appts = olFolder.Items

date1 = InputBox("Starting Date: ", "Start Date")
date2 = InputBox("End Date: ", "End Date")

i = 2
For Each appt In appts
If appt.Start = date1 And appt.Start date2 Then
Sheets("rawdata").Cells(i, 2).Value = appt.ConversationTopic
Sheets("rawdata").Cells(i, 4).Value = Format(appt.Start, "short date")
Sheets("rawdata").Cells(i, 5).Value = Format(appt.Start, "medium
time")
Sheets("rawdata").Cells(i, 6).Value = Format(appt.End, "medium time")
Sheets("rawdata").Cells(i, 7).Value = appt.Location
Sheets("rawdata").Cells(i, 3).Value = appt.Organizer
Sheets("rawdata").Cells(i, 8).Value = appt.Body
Sheets("rawdata").Cells(i, 9).Value = appt.RequiredAttendees
Sheets("rawdata").Cells(i, 10).Value = appt.OptionalAttendees
i = i + 1
End If
Next appt

Set ol = Nothing
Set ns = Nothing
Set appt = Nothing
End Sub

Thanks Sue -

I looked on the website you provided (outlookcode.com) and went with a
getfolderbyname example because I wasn't sure of the path of the public
folder. I also had a sub-folder included in the folders that I was going to
be searching. Now I am adding an array to pull the strings together from the
multiple calendars that I have before writing them into different tabs in
Excel. thanks.

"Sue Mosher [MVP-Outlook]" wrote:

You'd need to replace your GetDefaultFolder statement with a statement that returns the public folder. To get a non-default folder, you need to walk the folder hierarchy using the Folders collections or use a function that does that for you. See http://www.outlookcode.com/d/code/getfolder.htm and, especially for public folders, http://www.outlookcode.com/codedetail.aspx?id=1164
--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003
http://www.turtleflock.com/olconfig/index.htm
and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers
http://www.outlookcode.com/jumpstart.aspx

"WD" wrote in message ...
I have a VBA code in Excel that I use to extract calendar information from my
calendar to an Excel sheet. I would like to extract from a public calendar
instead. Is this possible? and if yes, what changes would I need to make to
the code below to do that. Any assistance would be helpful. Thanks


Private Sub GetMeetings_Click()
Dim ol As New Outlook.Application
Dim ns As Outlook.Namespace
Dim olFolder As Outlook.MAPIFolder
Dim appts As Object
Dim appt As Object
Dim date1 As Date, date2 As Date
Dim i As Integer

Set ns = ol.GetNamespace("MAPI")
Set olFolder = ns.GetDefaultFolder(olFolderCalendar)
Set appts = olFolder.Items

date1 = InputBox("Starting Date: ", "Start Date")
date2 = InputBox("End Date: ", "End Date")

i = 2
For Each appt In appts
If appt.Start = date1 And appt.Start date2 Then
Sheets("rawdata").Cells(i, 2).Value = appt.ConversationTopic
Sheets("rawdata").Cells(i, 4).Value = Format(appt.Start, "short date")
Sheets("rawdata").Cells(i, 5).Value = Format(appt.Start, "medium
time")
Sheets("rawdata").Cells(i, 6).Value = Format(appt.End, "medium time")
Sheets("rawdata").Cells(i, 7).Value = appt.Location
Sheets("rawdata").Cells(i, 3).Value = appt.Organizer
Sheets("rawdata").Cells(i, 8).Value = appt.Body
Sheets("rawdata").Cells(i, 9).Value = appt.RequiredAttendees
Sheets("rawdata").Cells(i, 10).Value = appt.OptionalAttendees
i = i + 1
End If
Next appt

Set ol = Nothing
Set ns = Nothing
Set appt = Nothing
End Sub