I need to produce a weekly report detailing what work I have carried out. I diligently record this in my Outlook calendar. My weekly report needs to be submitted in Microsoft Excel.
The following macro will pull the entries for the last seven days from my calendar and store it in Excel format.
Sub generateTimesheet() Dim OlApp As Outlook.Application Dim OlNameSpace As Outlook.Namespace Dim objFolder As Outlook.MAPIFolder Dim olAppointments As Object Dim olItems, olFinalItems As Outlook.Items Dim olAppointmentItem As Outlook.AppointmentItem Dim dtStart, dtEnd As Date Dim strRestriction As String dtStart = Format(Date - 7, "dd/mm/yyyy hh:mm AMPM") dtEnd = Format(Date + 1, "dd/mm/yyyy hh:mm AMPM") strRestriction = "[Start] >= '" & dtStart & "' AND [Start] <= '" & dtEnd & "'" Set OlApp = New Outlook.Application Set OlNameSpace = OlApp.GetNamespace("MAPI") Set olAppointments = OlNameSpace.GetDefaultFolder(Outlook.OlDefaultFolders.olFolderCalendar) Set olItems = olAppointments.Items olItems.IncludeRecurrences = True olItems.Sort "[Start]" Set olFinalItems = olItems.Restrict(strRestriction) Excel.ActiveWorkbook.ActiveSheet.Range("a2", Range("a2").End(xlDown).End(xlToRight)).Select Selection.Clear For Each olAppointmentItem In olFinalItems If Excel.ActiveWorkbook.ActiveSheet.Range("a2").Value = "" Then Excel.ActiveWorkbook.ActiveSheet.Range("a2").Select Else Excel.ActiveWorkbook.ActiveSheet.Range("a1").End(xlDown).Offset(1, 0).Select End If ' ActiveCell.Value = Format(olAppointmentItem.Start, "dd/mm/yyyy") ActiveCell.Offset(0, 0).Value = Format(olAppointmentItem.Start, "Dddd") ActiveCell.Offset(0, 1).Value = Format(olAppointmentItem.Start, "Medium Date") ActiveCell.Offset(0, 2).Value = olAppointmentItem.Subject ActiveCell.Offset(0, 3).Value = Format(olAppointmentItem.Start, "hh:mm:ss") ActiveCell.Offset(0, 4).Value = olAppointmentItem.Duration Next Excel.ActiveWorkbook.ActiveSheet.Range("a1").Select End Sub
https://github.com/RossGoodman/Excel/blob/master/CalendarToExcel
This macro should be placed in an excel spreadsheet and from there it will connect to Microsoft Outlook.
Stepping through the code:
- Get the dates for seven days ago and today then build a restriction clause to filter the calendar.
- Create a connection to my default calendar, sort the events by start date and apply the restriction on the dates.
- Clear the contents of the current worksheet – from A2 to the bottom right.
- Loop through each calendar entry
- Use either A2 or the first empty row below A2
- Write the relevant calendar attributes in the appropriate format on the selected row.
- End Loop
- Set the cursor to the top of the sheet.
Any problems, comments or suggestions for other macros/tips; let me know in the comments below.
One reply on “Outlook Calendar To Excel”
Excel Roundup 20140602 By Debra Dalgleish, on June 2nd, 2014 At PC World, Julie Sartain shows how to set up a workbook with a sheet for each month, and 3D formulas to summarize the Year to Data expenses. It’s not the way I’d set it up, but that’s a topic for another blog post.
What struck me was the comment,
“One of Excel’s greatest tricks is the dimensional or 3D reference…In Excel 2013, Microsoft has beefed it up with enhanced options and formulas.”
There are new functions, and other features in Excel 2013, but I don’t know of any new options in this area – did I miss something?
Contextures Posts
Here’s what I posted last week:
Quick tips for auditing Excel formulas, and doing some troubleshooting on a worksheet.
How to copy and use sample pivot table code in your own workbooks.
Create IFRAME code with formulas, and copy it to your web page or blog article, to embed a YouTube video, or other content.
Finally, for a humorous peek at what other people are saying about spreadsheets, read this week’s collection of Excel tweets, on my Excel Theatre blog.
Other Excel Articles
Here are a few of the Excel articles that I read last week, that you might find useful:
Paul Downs posted a follow-up to his outdated pricing spreadsheet problem, in response to the suggestions he got from readers.
Thomas Nelshoppen shared his workbook for keeping baseball statistics.
If you like the number game, 2048, the Excel team found a couple of Excel versions that you can download.
Chandoo want to know what you struggle to do in Excel. There are over 100 comments, so there’s lots of struggling going on!
Ross Goodman uses a macro to pull his tasks from Outlook to Excel, to show what he’s done for the past week.
Ray Woodcock had trouble deleting a chart, and was finally able to remove it in Open Office.
In the New York Times, Neil Irwin outlines everything you need to know about Thomas Piketty versus The Financial Times.
Excel Resources
Here are some upcoming events, courses and recently published books, related to Excel.
The European Spreadsheet Risks Interest Group – EuSpRIG –has announced the preliminary program for Eursprig 2014. This event will be held in Delft (Netherlands) on July 3, 2014. Read more about it on Patrick O’Beirne’s blog.
Share Your Events and Articles
If you read or wrote any other interesting Excel articles recently, or have upcoming Excel events, please share a link in the comments below, with a brief description. Thanks!
_____________________
Links to Recent Excel Books on Amazon.com
__________________________________
June 2nd, 2014 | Category: Excel tips