27 Oct Exporting tasks from Project into your Outlook calendar

<Guinness>

This is something that in the past I have done manually, but due to a discussion on a Web2.0 application somewhere I had the idea that it would be possible to semi automate this.

<Update 8th October 2014 – this is now available as a fully fledged and supported add-in to Project – see here>

I guess the 1st thing to ascertain is why we might want to do this – for me it is basically the requirement to view both my programmed work from multiple projects (held in single MPP plans) and Outlook, (where I plan other things such as personal commitments which in turn often determine which location I need to be in (eg I need to be home for certain social commitments)).  As a consultant, it’s important to be able to tell a client whether I can be onsite and away from home during a certain period, and if I’m working for multiple clients, then I need to consolidate several schedules.  Within each schedule I might be known as Ben,  Ben Howard, Consultant or any other such name, and so there would be little consistency for putting them in a Master Plan.  Of course, it’s much easier if I’m driving the schedule, but sometimes I’m just the hired hand – when I’m driving all of my schedules, I just stick them in Project Server!

So, that’s my excuse for this foray into Project, Excel and Outlook, what’s yours?

Note that I’m using Project 2010, Excel 2010 and Outlook 2010, but this should work in all formats commonly used.

Here is the overall process.

PROJECT 2010

So the first thing I need to do is to save the file in Excel 2003 format.  We need to save it to 2003 format because for some reason Outlook doesn’t have an option to import items from 2010 versions of Excel!

Due to the usual security concerns, we specifically need to change the settings to allow saving in an older format.

Open up Project, go to the Backstage | Options | Trust Center | Trust Center Settings | Prompt when loading files with non-default file format.

The file I’ve chosen to use for this blog is the “Wine tasting fundraiser” template from Office.com.

Click on File | Save As and choose Excel 97-2003 format.  Saving this to Excel 97-2003 format gives the following warning.

Next you are taken through the Export Wizard.  The goal of this wizard is to choose the data to export from Project.  We simply need the resource, task name start and finish dates.  You can optionally put the work in.

I’ll list all the screen shots so you can follow it completely

Now we can begin to select the data.  We need assignment data, and we will include the headers (titles or column names) in our output.

Next, select the fields you want to export from Project.  For me they are Resource Name, Task Name, Work, Start & Finish.  Suggested names will be created in the Excel field – you can rename these as required.

Once this is completed, you have the opportunity to save the map you have created – this is a sensible idea if you are going to do this more than once!  You can see that I called mine “Export for Outlook Calendar import”

So, onto the next stage

Excel 2010.

I now have an Excel 97-2003 Worksheet, with a single workbook called Assignment_Table1.  Opening it up gives the following

I now need some VBA to do the following

1. Remove all the rows (assignments) that are not mine.

2. Add the number of hours work to the task name.  This is because Outlook calendars don’t have a work value field, and so I’m going to add them to the task name so I can have an idea of how much work I have to do.  Note that this value is the assignment work so if I have multiple assignments to a task the work value is correct.

3. Split the Start and Finish time values from the start and finish dates so that the appointment is entered correctly into my calendar (08:00, 13:00 etc).

The following VBA performs the above actions – note that is comes with no warranties etc; and the columns and resource names are hard coded.  You will need to modify this to work for your exact map of exported items from MSProject.

Sub Format_Dates()
‘ Ben Howard – http://applepark.co.uk  October 2011
‘ Excel VBA to modify our

Dim TimePart As String
Dim n As Integer
Dim rowNum As Integer
Dim colNum As Integer

‘ Insert a new column to hold the start time

Columns(“E:E”).Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

‘Removes rows with the name not equalling Chairperson in column A

Set myRng = Range(“A1”).CurrentRegion
FirstRow = myRng.Row
Lastrow = FirstRow + myRng.Rows.Count – 1
For rw = Lastrow To FirstRow Step -1
If Cells(rw, “A”) <> “Chairperson” Then Rows(rw).Delete
Next

rowNum = 1
colNum = 2

While Cells(rowNum, colNum).Value <> “”
‘contactenate the Name and work value
colNum = 2
Cells(rowNum, colNum).Value = Cells(rowNum, colNum).Value & ” – ” & Cells(rowNum, colNum + 1).Value
‘split the start and finish time.
colNum = 4
n = InStr(1, Cells(rowNum, colNum).Value, ” “)
TimePart = Right(Cells(rowNum, colNum).Value, Len(Cells(rowNum, colNum).Value) – n)
Cells(rowNum, colNum + 1).Value = TimePart
colNum = 6
n = InStr(1, Cells(rowNum, colNum).Value, ” “)
TimePart = Right(Cells(rowNum, colNum).Value, Len(Cells(rowNum, colNum).Value) – n)
Cells(rowNum, colNum + 1).Value = TimePart
rowNum = rowNum + 1
Wend

End Sub

(thanks to http://excel.bigresource.com/track/excel-lplxvLaM/ and some others for publishing their code which I have shamelessly copied and modified)

Once you have run the VBA, your Excel file should now look like the following…

The Outlook import needs to find a named range, so highlight the rows and columns to be imported, go to the Name Range cell, and type OutlookImport and hit return.

Save and close Excel – note for some reason Excel chose to default to 5.0/95 format whilst saving, so you might have to Save As a 97-2003 format.

Outlook 2010

Open Outlook2010, and if the Import & Export command isn’t available, then add it either the the Quick Access Toolbar, or put it on the ribbon.

Once it’s on the ribbon, select the Import & Export icon.  It’s an easy wizard so I’ve just included most of the screen shots for you to follow.

Choose the file to import, and select your relevant options.

Choose the calendar to import the file into.  I always set up a new calendar for each project I’m working on.

Select the appropriate named range to import

Map the fields from the Excel chart to Outlook.  You do this by dragging and dropping items from the left to the right.  There is no need to map the hours or name.

The items are imported to the calendar with the correct start and finish times and the correct subject.  Using Outlook’s Calendar Overlay feature, I can see my normal calendar alongside the project one.  I can do this for multiple calendars – it really is a great feature.

Enjoy,  Ben.

Remember to check out the new application here