damoncf1234
asked on
Export shared outlook calendars to single excel spreadsheet
Hello. I'm trying to export multiple shared office 2007 calendars to a single excel spreadsheet each night, listing "appointments" by time. In addition to exporting the appointments from multiple shared calendars, I need to "pull" multiple pieces of information from the "subject" field from each appointment, and populate individual columns within the spreadsheet.
Here's some background information on our calendar setup:
- There are multiple shared calendars (one calendar for each meeting room/location), named "RoomA", "RoomB", "RoomC", etc...
- Meetings in each room are scheduled as "appointments" in each calendar
- The subject field within each "appointment" contains multiple pieces of information -- a typical subject looks like this:
(RoomA) Meeting Title - Hosted by John Doe - Loc1, Loc2, Loc3
- (Room A) would be the meeting room name (same as the calendar name)
- Meeting Title is the name of the meeting
- Hosted by Jane Doe lists the Name of the person holding the meeting
- Loc1, Loc2, Loc3 indicates any external "branches or participants" that we have to connect into the meeting
**Each of these items contained in the appointment "Subject" field need to be split into individual columns in excel (not just one large "Subject" field with all of this information jammed into a single column)
- The location field in the Outlook appointment contains the name of the last person to create/modify a meeting
- The start time and end time fields are also used within the Outlook appointment to specify the start/end time of each meeting
- The "body" of the appointment contains a sentence or two of notes about each meeting
So, we are trying to find a way to export the pieces of information above (from multiple shared calendars) into a single Excel spreadsheet each day. We would like the Spreadsheet to have the following columns:
StartTime EndTime Room MeetingTitle HostedBy ExternalParticipants CreatedBy Notes
Other important notes:
- We use dashes (-) to seperate "fields" within the Subject field of each appointment, after the initial (Room1) portion in quotations, which specifies the room name
- We have a shared calendar for each meeting room, which we'd like to pull the above information from, and use to export into a single Excel spreadsheet (Say, the script would run on the "RoomA" calendar first, then "RoomB", "RoomC", and all appointments from each calendar would populate a single spreadsheet).
I know this is a large request (at least to me it seems to be), but any assistance would be greatly appreciated. I've done some basic vbscripts, but never anything involving exporting and sorting information from Outlook. Thanks for any assistance.
-Chris
Here's some background information on our calendar setup:
- There are multiple shared calendars (one calendar for each meeting room/location), named "RoomA", "RoomB", "RoomC", etc...
- Meetings in each room are scheduled as "appointments" in each calendar
- The subject field within each "appointment" contains multiple pieces of information -- a typical subject looks like this:
(RoomA) Meeting Title - Hosted by John Doe - Loc1, Loc2, Loc3
- (Room A) would be the meeting room name (same as the calendar name)
- Meeting Title is the name of the meeting
- Hosted by Jane Doe lists the Name of the person holding the meeting
- Loc1, Loc2, Loc3 indicates any external "branches or participants" that we have to connect into the meeting
**Each of these items contained in the appointment "Subject" field need to be split into individual columns in excel (not just one large "Subject" field with all of this information jammed into a single column)
- The location field in the Outlook appointment contains the name of the last person to create/modify a meeting
- The start time and end time fields are also used within the Outlook appointment to specify the start/end time of each meeting
- The "body" of the appointment contains a sentence or two of notes about each meeting
So, we are trying to find a way to export the pieces of information above (from multiple shared calendars) into a single Excel spreadsheet each day. We would like the Spreadsheet to have the following columns:
StartTime EndTime Room MeetingTitle HostedBy ExternalParticipants CreatedBy Notes
Other important notes:
- We use dashes (-) to seperate "fields" within the Subject field of each appointment, after the initial (Room1) portion in quotations, which specifies the room name
- We have a shared calendar for each meeting room, which we'd like to pull the above information from, and use to export into a single Excel spreadsheet (Say, the script would run on the "RoomA" calendar first, then "RoomB", "RoomC", and all appointments from each calendar would populate a single spreadsheet).
I know this is a large request (at least to me it seems to be), but any assistance would be greatly appreciated. I've done some basic vbscripts, but never anything involving exporting and sorting information from Outlook. Thanks for any assistance.
-Chris
ASKER
Thanks for the response. We'd like the items from each calendar to go into one sheet. We'd like the sheet to be erased each day (before the script is run for the "new" day). Right now we do this all manually at midnight each night, which is a pain... :)
Thanks
Thanks
Seems simple enough. What timeframe do you want to export for? The entire calendar including appointments that occur in the past, all appointments for the current day, all appointments from today forward, or something else?
I have the basic script ready. I'll finalize and post it as soon as I have the answer to my last question.
ASKER
BlueDevilFan,
Thanks for the quick response. We're looking for the current day (24 hours) (basically, we'll run it each night, right after midnight, and it will "capture" all of the upcoming meetings in all of the conference rooms for the "present" day.
Thanks again for your response.
Thanks for the quick response. We're looking for the current day (24 hours) (basically, we'll run it each night, right after midnight, and it will "capture" all of the upcoming meetings in all of the conference rooms for the "present" day.
Thanks again for your response.
You're welcome.
Ok, this should do it. Follow these instructions to add the code to Outlook.
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
For this to work
1. Outlook must be running.
2. The account that this runs under must have access to the shared calendars
2. The spreadsheet must already exist
To run
1. Run the macro RunExportCalendarsToExcel
Ok, this should do it. Follow these instructions to add the code to Outlook.
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
For this to work
1. Outlook must be running.
2. The account that this runs under must have access to the shared calendars
2. The spreadsheet must already exist
To run
1. Run the macro RunExportCalendarsToExcel
Sub RunExportCalendarsToExcel()
'Change the name of the conference room on the next line. The name must match the name of the mailbox.'
ExportCalendarToExcel "Room A", True
'Repeat the next line for each subsequent conference room. Be sure to change the name.'
ExportCalendarToExcel "Room B"
End Sub
Sub ExportCalendarToExcel(strCalendarName As String, Optional bolClearWorksheet As Boolean)
Dim olkFolder As Outlook.Folder, olkItems As Outlook.Items, olkAppt As Outlook.AppointmentItem, olkRecipient As Outlook.Recipient
Dim excApp As Object, excWkb As Object, excSht As Object, excRng As Object, lngRow As Long
Dim arrTitle As Variant
'Launch Excel and open the spreadsheet'
Set excApp = CreateObject("Excel.Application")
excApp.Visible = True
'Change the name and path of the spreadsheet on the next line'
Set excWkb = excApp.Workbooks.Open("C:\eeTesting\Chris.xlsx")
Set excSht = excWkb.Worksheets(1)
If bolClearWorksheet Then
Set excRng = excSht.Range("A1").CurrentRegion
lngRow = excRng.Rows.count
excApp.Rows(2 & ":" & lngRow).Delete
lngRow = 2
Else
lngRow = excSht.UsedRange.Rows.count + 1
End If
'Connect to and process the shared calendar'
Set olkRecipient = Session.CreateRecipient(strCalendarName)
Set olkFolder = Session.GetSharedDefaultFolder(olkRecipient, olFolderCalendar)
Set olkItems = olkFolder.Items.Restrict("[Start] >= '" & Format(Date & " 0:01am", "ddddd h:nn AMPM") & "' AND [Start] < '" & Format(Date & " 11:59pm", "ddddd h:nn AMPM") & "'")
olkItems.Sort "[Start]"
olkItems.IncludeRecurrences = True
For Each olkAppt In olkItems
arrTitle = Split(olkAppt.Subject, "-")
excSht.Cells(lngRow, 1) = olkAppt.Start
excSht.Cells(lngRow, 2) = olkAppt.End
excSht.Cells(lngRow, 3) = strCalendarName
excSht.Cells(lngRow, 4) = Trim(arrTitle(0))
excSht.Cells(lngRow, 5) = Trim(arrTitle(1))
excSht.Cells(lngRow, 6) = Trim(arrTitle(2))
excSht.Cells(lngRow, 7) = olkAppt.Organizer
excSht.Cells(lngRow, 8) = olkAppt.Body
lngRow = lngRow + 1
Next
'Save the spreadsheet and exit Excel'
Set excRng = Nothing
Set excSht = Nothing
excWkb.Save
Set excWkb = Nothing
excApp.Quit
Set excApp = Nothing
'Clean-up the Outlook objects'
Set olkFolder = Nothing
Set olkItems = Nothing
Set olkAppt = Nothing
End Sub
ASKER
BlueDevilFan,
Sorry for the delay; Each time I try to run the script, I get the following error:
Run-time error '-2147352567 (80020009)':
Outlook does not recognize one or more names.
I click the "debug" button, and the following line is highlighted:
Set olkFolder = Session.GetSharedDefaultFo
If I mouse-over the highlighted line, I can see that it "resolves" olkFolder to the first calendar name.
I've tried changing the way I specify the calendar name(s) up top, and commented-out all but one of the calendars. I have one question -- I mentioned that these are "shared calendars" -- they are located in the public folders -- they're not individual calendars shared from user mailboxes or anything... Does this make a difference?
Thanks
-Chris
Sorry for the delay; Each time I try to run the script, I get the following error:
Run-time error '-2147352567 (80020009)':
Outlook does not recognize one or more names.
I click the "debug" button, and the following line is highlighted:
Set olkFolder = Session.GetSharedDefaultFo
If I mouse-over the highlighted line, I can see that it "resolves" olkFolder to the first calendar name.
I've tried changing the way I specify the calendar name(s) up top, and commented-out all but one of the calendars. I have one question -- I mentioned that these are "shared calendars" -- they are located in the public folders -- they're not individual calendars shared from user mailboxes or anything... Does this make a difference?
Thanks
-Chris
"they are located in the public folders -- they're not individual calendars shared from user mailboxes or anything... Does this make a difference?"
Yes, that makes a huge difference. I had interpreted "shared" to mean a mailbox calendar that had been shared. I'll adjust the code and repost.
Yes, that makes a huge difference. I had interpreted "shared" to mean a mailbox calendar that had been shared. I'll adjust the code and repost.
Here's the modified code. This version assumes that the calendars are all located at the root of the public folders. IN other words
+ Public Folders
+ All Public Folders
+ Room A
+ Room B
If that's not correct, then I need to know if the calendars are all at the same level and the path to the parent folder if they are at the same level. If they are at different levels, then I'll need to make a change to the code.
+ Public Folders
+ All Public Folders
+ Room A
+ Room B
If that's not correct, then I need to know if the calendars are all at the same level and the path to the parent folder if they are at the same level. If they are at different levels, then I'll need to make a change to the code.
Sub RunExportCalendarsToExcel()
'Change the name of the conference room on the next line. The name must match the name of the mailbox.'
ExportCalendarToExcel "Room A", True
'Repeat the next line for each subsequent conference room. Be sure to change the name.'
ExportCalendarToExcel "Room B"
End Sub
Sub ExportCalendarToExcel(strCalendarName As String, Optional bolClearWorksheet As Boolean)
Dim olkFolder As Outlook.Folder, olkItems As Outlook.Items, olkAppt As Outlook.AppointmentItem
Dim excApp As Object, excWkb As Object, excSht As Object, excRng As Object, lngRow As Long
Dim arrTitle As Variant
'Launch Excel and open the spreadsheet'
Set excApp = CreateObject("Excel.Application")
excApp.Visible = True
'Change the name and path of the spreadsheet on the next line'
Set excWkb = excApp.Workbooks.Open("C:\eeTesting\Chris.xlsx")
Set excSht = excWkb.Worksheets(1)
If bolClearWorksheet Then
Set excRng = excSht.Range("A1").CurrentRegion
lngRow = excRng.Rows.count
excApp.Rows(2 & ":" & lngRow).Delete
lngRow = 2
Else
lngRow = excSht.UsedRange.Rows.count + 1
End If
'Connect to and process the shared calendar'
Set olkFolder = Session.GetDefaultFolder(olPublicFoldersAllPublicFolders).Folders(strCalendarName)
Set olkItems = olkFolder.Items.Restrict("[Start] >= '" & Format(Date & " 0:01am", "ddddd h:nn AMPM") & "' AND [Start] < '" & Format(Date & " 11:59pm", "ddddd h:nn AMPM") & "'")
olkItems.Sort "[Start]"
olkItems.IncludeRecurrences = True
For Each olkAppt In olkItems
arrTitle = Split(olkAppt.Subject, "-")
excSht.Cells(lngRow, 1) = olkAppt.Start
excSht.Cells(lngRow, 2) = olkAppt.End
excSht.Cells(lngRow, 3) = strCalendarName
excSht.Cells(lngRow, 4) = Trim(arrTitle(0))
excSht.Cells(lngRow, 5) = Trim(arrTitle(1))
excSht.Cells(lngRow, 6) = Trim(arrTitle(2))
excSht.Cells(lngRow, 7) = olkAppt.Organizer
excSht.Cells(lngRow, 8) = olkAppt.Body
lngRow = lngRow + 1
Next
'Save the spreadsheet and exit Excel'
Set excRng = Nothing
Set excSht = Nothing
excWkb.Save
Set excWkb = Nothing
excApp.Quit
Set excApp = Nothing
'Clean-up the Outlook objects'
Set olkFolder = Nothing
Set olkItems = Nothing
Set olkAppt = Nothing
End Sub
ASKER
BlueDevilFan,
The individual calendars are all in the same folder in the following location:
"All Public Folders\Conference Rooms\"
Thanks
-Chris
The individual calendars are all in the same folder in the following location:
"All Public Folders\Conference Rooms\"
Thanks
-Chris
Chris,
Modify line #29 from
Set olkFolder = Session.GetDefaultFolder(o
to
Set olkFolder = Session.GetDefaultFolder(o
Modify line #29 from
Set olkFolder = Session.GetDefaultFolder(o
to
Set olkFolder = Session.GetDefaultFolder(o
ASKER
BlueDevilFan,
Thanks, that fixed the issue. Is there any way to add some type of "pop-up" that would allow a user to specify a specific date to run this on (for example, 4/27/10) instead of just the current date (I know I didn't ask this originally).
Also, is there a way to add a "static" heading to the top row of each column in the excel spreadsheet, to identify what each column contains?
Thanks,
Chris
Thanks, that fixed the issue. Is there any way to add some type of "pop-up" that would allow a user to specify a specific date to run this on (for example, 4/27/10) instead of just the current date (I know I didn't ask this originally).
Also, is there a way to add a "static" heading to the top row of each column in the excel spreadsheet, to identify what each column contains?
Thanks,
Chris
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
BlueDevilFan,
Yes, we'll be writing to the same spreadsheet each day. I assumed that part of your script would be deleting the spreadsheet (and removing all of the previous day's meetings), which would take out everything, including the first row with the headings.
I'll try running your latest version tomorrow morning, and specifying another date.
Thanks,
-Chris
Yes, we'll be writing to the same spreadsheet each day. I assumed that part of your script would be deleting the spreadsheet (and removing all of the previous day's meetings), which would take out everything, including the first row with the headings.
I'll try running your latest version tomorrow morning, and specifying another date.
Thanks,
-Chris
ASKER
BlueDevilFan,
Sorry for the delay, I had to go on a trip to a remote location, and didn't have access to a particulare network to test your solution. Your last solution worked, and performed the functions that we were looking for. I noticed that some of the people that input meetings on some calendars do not follow our standards, which causes some errors to come up as the macro is run in Outlook. I put in an "on error resume next" in the code, which lets it keep it running if some of the fields are missing.
Is there a way to run this as a "stand alone" vbscript, instead of having to launch it within Outlook?
Thanks for your help.
-Chris
Sorry for the delay, I had to go on a trip to a remote location, and didn't have access to a particulare network to test your solution. Your last solution worked, and performed the functions that we were looking for. I noticed that some of the people that input meetings on some calendars do not follow our standards, which causes some errors to come up as the macro is run in Outlook. I put in an "on error resume next" in the code, which lets it keep it running if some of the fields are missing.
Is there a way to run this as a "stand alone" vbscript, instead of having to launch it within Outlook?
Thanks for your help.
-Chris
Hi, Chris.
No problem on the delay.
To run as a standalone script the code has to be converted from VBA to VBScript. Not too difficult to do. The code below should get the job done.
No problem on the delay.
To run as a standalone script the code has to be converted from VBA to VBScript. Not too difficult to do. The code below should get the job done.
'Change the name of the conference room on the next line. The name must match the name of the mailbox.'
ExportCalendarToExcel "Room A", True
'Repeat the next line for each subsequent conference room. Be sure to change the name.'
ExportCalendarToExcel "Room B", False
WScript.Quit
Sub ExportCalendarToExcel(strCalendarName, bolClearWorksheet)
Const olPublicFoldersAllPublicFolders = 18
Dim olkApp, olkSes, olkFolder, olkItems, olkAppt
Dim excApp, excWkb, excSht, excRng, lngRow
Dim arrTitle, datDate
'Connect to Outlook'
Set olkApp = CreateObject("Outlook.Application")
Set olkSes = olkApp.GetNamespace("MAPI")
'Change the profile name on the next line'
olkSes.Logon "Outlook"
'Launch Excel and open the spreadsheet'
Set excApp = CreateObject("Excel.Application")
excApp.Visible = True
'Change the name and path of the spreadsheet on the next line'
Set excWkb = excApp.Workbooks.Open("C:\eeTesting\Chris.xlsx")
Set excSht = excWkb.Worksheets(1)
If bolClearWorksheet Then
Set excRng = excSht.Range("A1").CurrentRegion
lngRow = excRng.Rows.count
excApp.Rows(2 & ":" & lngRow).Delete
lngRow = 2
Else
lngRow = excSht.UsedRange.Rows.count + 1
End If
'Connect to and process the shared calendar'
Set olkFolder = olkSes.GetDefaultFolder(olPublicFoldersAllPublicFolders).Folders("Conference Rooms").Folders(strCalendarName)
datDate = InputBox("Enter the date you want to export for.", "Export Calendars to Excel", Date)
Set olkItems = olkFolder.Items.Restrict("[Start] >= '" & Format(datDate & " 0:01am", "ddddd h:nn AMPM") & "' AND [Start] < '" & Format(datDate & " 11:59pm", "ddddd h:nn AMPM") & "'")
olkItems.Sort "[Start]"
olkItems.IncludeRecurrences = True
For Each olkAppt In olkItems
arrTitle = Split(olkAppt.Subject, "-")
excSht.Cells(lngRow, 1) = olkAppt.Start
excSht.Cells(lngRow, 2) = olkAppt.End
excSht.Cells(lngRow, 3) = strCalendarName
excSht.Cells(lngRow, 4) = Trim(arrTitle(0))
excSht.Cells(lngRow, 5) = Trim(arrTitle(1))
excSht.Cells(lngRow, 6) = Trim(arrTitle(2))
excSht.Cells(lngRow, 7) = olkAppt.Organizer
excSht.Cells(lngRow, 8) = olkAppt.Body
lngRow = lngRow + 1
Next
'Save the spreadsheet and exit Excel'
Set excRng = Nothing
Set excSht = Nothing
excWkb.Save
Set excWkb = Nothing
excApp.Quit
Set excApp = Nothing
'Clean-up the Outlook objects'
Set olkFolder = Nothing
Set olkItems = Nothing
Set olkAppt = Nothing
olkSes.Logoff
Set olkSes = Nothing
Set olkApp = Nothing
End Sub
Hi David,
Very useful article. i have a question. when i run the macro, i'm getting 2012 dates. any reason for that? i used your modification on the script to select the date. but still i'm getting old date like 2012
Very useful article. i have a question. when i run the macro, i'm getting 2012 dates. any reason for that? i used your modification on the script to select the date. but still i'm getting old date like 2012
this is the code i use
Sub RunExportCalendarsToExcel(
'Change the name of the conference room on the next line. The name must match the name of the mailbox.'
ExportCalendarToExcel "SG-Share Calendar", True
End Sub
Sub ExportCalendarToExcel(strC
Dim olkFolder As Outlook.Folder, olkItems As Outlook.Items, olkAppt As Outlook.AppointmentItem, olkRecipient As Outlook.Recipient
Dim excApp As Object, excWkb As Object, excSht As Object, excRng As Object, lngRow As Long
Dim arrTitle As Variant
'Launch Excel and open the spreadsheet'
Set excApp = CreateObject("Excel.Applic
excApp.Visible = True
'Change the name and path of the spreadsheet on the next line'
Set excWkb = excApp.Workbooks.Open("d:\
Set excSht = excWkb.Worksheets(1)
If bolClearWorksheet Then
Set excRng = excSht.Range("A1").Current
lngRow = excRng.Rows.Count
excApp.Rows(2 & ":" & lngRow).Delete
lngRow = 2
Else
lngRow = excSht.UsedRange.Rows.Coun
End If
'Connect to and process the shared calendar'
Set olkRecipient = Session.CreateRecipient(st
Set olkFolder = Session.GetSharedDefaultFo
datDate = InputBox("Enter the date you want to export for.", "Export Calendars to Excel", Date)
Set olkItems = olkFolder.Items.Restrict("
olkItems.Sort "[Start]"
olkItems.IncludeRecurrence
For Each olkAppt In olkItems
arrTitle = Split(olkAppt.Subject, "-")
excSht.Cells(lngRow, 1) = olkAppt.Start
excSht.Cells(lngRow, 2) = olkAppt.End
excSht.Cells(lngRow, 3) = strCalendarName
excSht.Cells(lngRow, 4) = olkAppt.Organizer
excSht.Cells(lngRow, 5) = olkAppt.Body
lngRow = lngRow + 1
Next
'Save the spreadsheet and exit Excel'
Set excRng = Nothing
Set excSht = Nothing
excWkb.Save
Set excWkb = Nothing
excApp.Quit
Set excApp = Nothing
'Clean-up the Outlook objects'
Set olkFolder = Nothing
Set olkItems = Nothing
Set olkAppt = Nothing
End Sub
attached the result
Sanda.xlsx
Sub RunExportCalendarsToExcel(
'Change the name of the conference room on the next line. The name must match the name of the mailbox.'
ExportCalendarToExcel "SG-Share Calendar", True
End Sub
Sub ExportCalendarToExcel(strC
Dim olkFolder As Outlook.Folder, olkItems As Outlook.Items, olkAppt As Outlook.AppointmentItem, olkRecipient As Outlook.Recipient
Dim excApp As Object, excWkb As Object, excSht As Object, excRng As Object, lngRow As Long
Dim arrTitle As Variant
'Launch Excel and open the spreadsheet'
Set excApp = CreateObject("Excel.Applic
excApp.Visible = True
'Change the name and path of the spreadsheet on the next line'
Set excWkb = excApp.Workbooks.Open("d:\
Set excSht = excWkb.Worksheets(1)
If bolClearWorksheet Then
Set excRng = excSht.Range("A1").Current
lngRow = excRng.Rows.Count
excApp.Rows(2 & ":" & lngRow).Delete
lngRow = 2
Else
lngRow = excSht.UsedRange.Rows.Coun
End If
'Connect to and process the shared calendar'
Set olkRecipient = Session.CreateRecipient(st
Set olkFolder = Session.GetSharedDefaultFo
datDate = InputBox("Enter the date you want to export for.", "Export Calendars to Excel", Date)
Set olkItems = olkFolder.Items.Restrict("
olkItems.Sort "[Start]"
olkItems.IncludeRecurrence
For Each olkAppt In olkItems
arrTitle = Split(olkAppt.Subject, "-")
excSht.Cells(lngRow, 1) = olkAppt.Start
excSht.Cells(lngRow, 2) = olkAppt.End
excSht.Cells(lngRow, 3) = strCalendarName
excSht.Cells(lngRow, 4) = olkAppt.Organizer
excSht.Cells(lngRow, 5) = olkAppt.Body
lngRow = lngRow + 1
Next
'Save the spreadsheet and exit Excel'
Set excRng = Nothing
Set excSht = Nothing
excWkb.Save
Set excWkb = Nothing
excApp.Quit
Set excApp = Nothing
'Clean-up the Outlook objects'
Set olkFolder = Nothing
Set olkItems = Nothing
Set olkAppt = Nothing
End Sub
attached the result
Sanda.xlsx
I might be able to help with this. Since you describe this as a daily process, where does the data go in the spreadsheet? Will there be a separate sheet for each calendar or do the items all go in one sheet? If the latter, does the process simply append the data each day or does it need to wipe the sheet out first before exporting the data?