Conference Catch Up: Critical Path Drag

Catching up on some of the discussions from the last conference I attended, the Critical Path Management conference.  Had a good hallway conversation about the concept of Critical Path Drag.

Not familiar with the concept of drag?  If you’re at all interested in the world of scheduling, it’s well worth the time to check out this article in Wikipedia.

Read the article and curious how to calculate this in Microsoft Project desktop?  Here’s a helpful macro courtesy of Tom Boyle.

Conference Catch Up: Critical Path Drag

Setting Default Desktop Scheduling Options

One of the takeaways from the recent Construction CPM Scheduling Conference in New Orleans last week (other than a couple of hurricanes on Bourbon street) was an acknowledgement that while Microsoft Project does indeed enable best practice scheduling, by default many of the required features are turned off.  This causes some frustration on behalf of the scheduler who must hunt deep into the Options panel to identify and select the appropriate options for each new project.

To assist in this endeavor, I wrote this little macro.  It basically goes through the options and sets them to optimize the scheduling interface for detailed construction scheduling (and therefore may have to be tweaked to support project scheduling in other domains).  In the past, I’ve triggered this macro whenever I run any other scripts on the schedule, i.e. I’ve written scripts to facilitate the update process….which call this routine up before going into collecting the update data.

Did I forget a key setting?  Let me know and I’ll update it accordingly.

Sub ApplyDefaultSettings()

    Dim Continue As String
    Continue = MsgBox("This macro will now apply the standard PMO settings to the project schedule.", vbOKCancel, "Confirm")
    If Continue = vbOK Then
        '1 - Project Settings
        With Application.ActiveProject
            .AutoTrack = True 'Sets the task status updates resource status setting
            .MoveCompleted = True 'Move completed work prior to the status date.
            .MoveRemaining = True 'Move incomplete work after the status date
            .SpreadPercentCompleteToStatusDate = True 'spread %Complete to the status date
            .NewTasksCreatedAsManual = False 'Turn off manual tasks
            .DisplayProjectSummaryTask = True 'Display project summary task
            .AutoLinkTasks = False 'Do not automatically link tasks when added to the schedule
            .MultipleCriticalPaths = True 'Calculate multiple critical paths
        End With
        '2 - Display Settings
        Application.NewTasksStartOn (pjProjectDate) 'Sets new tasks to default to the Project Start Date
        Application.DisplayEntryBar = True 'Displays the Entry Bar
        Application.Use3DLook = False 'Turns off 3D which used to cause printing issues
        '3 - Gantt Chart Settings
        GridlinesEditEx Item:=12, NormalType:=3 'Set the Status Date line
        GridlinesEditEx Item:=12, NormalColor:=192
        GridlinesEditEx Item:=4, NormalType:=0 'Turn off the Current Date line
        GridlinesEditEx Item:=0, Interval:=3 'Set dotted lines on every third Gantt row
        GridlinesEditEx Item:=0, IntervalType:=3
        GridlinesEditEx Item:=0, IntervalColor:=8355711
        GridlinesEditEx Item:=13, NormalType:=3 'Set dotted lines on every vertical top tier column
        GridlinesEditEx Item:=13, NormalColor:=8355711
    End If

End Sub


Setting Default Desktop Scheduling Options

Adding Tasks to the Timeline with VBA

Toodling around in MPP 2013 today and came up with this quick code.  It runs through each task and adds the ones flagged in the Flag1 field to the timeline.  Note this is barely tested – and I would assume it needs to be run on a view where the timeline actually appears.

Sub AddTasktoTimeline()

    Dim T As Task
    For Each T In ActiveProject.Tasks
        If T.Flag1 = True Then
            SelectRow Row:=T.ID, RowRelative:=False
            SelectRow Row:=T.ID, RowRelative:=False
            TaskOnTimeline Remove:=True
        End If
    Next T

End Sub

Adding Tasks to the Timeline with VBA

Importing Files to Project Online with VBA

In yesterday’s post, I talked about how to run a script to export all of our Microsoft Project schedules into XML files.  In this post, I’ll provide the script to import these into a new instance of Project Server – either on-prem or online.  Note that this script will not import custom fields – but could be easily adapted to capture and transfer custom fields.

Note also that there are other third party tools that do much of the same thing, but better.

Caveats aside, if you followed yesterday’s post, you should now have a directory called C:\Exports that contains all of the exported project schedules in an XML format.


You should also have a Microsoft Project schedule containing a list of all of the projects you wish to import.


Add the following code…

Sub ImportProjects()

    Dim T As Task
    Dim fName As String

    For Each T In ActiveProject.Tasks
        fName = T.Name
        Application.FileOpenEx Name:="C:\Exports\" & fName & ".mpp", ReadOnly:=False, FormatID:="MSProject.MPP"
        Application.FileSaveAs Name:="<>\" & fName, FormatID:=""
        Application.FileCloseEx pjSave, True, False
    Next T

End Sub

…and run to import the files.  You will probably need to click through the prompts as they appear.

Pair this with a utility to publish all of the projects like the one Alex Burton wrote here and you’ve now moved your projects from one environment to another.

Importing Files to Project Online with VBA

Exporting MPP Files with VBA

Well, I didn’t deliberately intend to get into a VBA rut, but periodically, I like to clean out some of the half written posts I have lying around, and it turns out that I apparently left a couple VBA posts started and not yet finished.  My resurgent interest in VBA of late has also been driven by my efforts to get up to speed in Project Online, and the realization that I can easily use my traditional VBA skills to interact with the Project client, and quickly upload data to my Project Online tenant.

Hence, I wrote this quick VBA script to export all of the MPP files in an existing Project Server instance, and then import the files into another Project Server instance.  And here’s the beauty of VBA and the Project client interface……the code is essentially version agnostic….meaning I could use it to do a dump of all of my existing project schedules (to prep for export or to perform a backup) from Project Server 2010 and then upload them using a different code into an online version of Project Server 2013.

I’ll also note that there are much more robust partner solutions that pretty much do the same thing – but better.  This code is crude, virtually untested, and has only been used in development environments to generate demos.

Identify the Target List

The first step  is to generate a list of the projects you would like to export.  The easiest way for me to do this was simply to grab the list of projects from the Project Center.


I simply exported the list to Excel, and then copied the results into a Microsoft Project file.  I could have simply run the macro from Excel I suppose, but its simpler for me to just use Project.


Note that I would include custom fields as custom fields in the MPP file if I were planning to transfer them over.  In this case, I just want the raw project data.

Add the macro…

Sub ExportProjects()

    Dim T As Task
    Dim fName As String

    For Each T In ActiveProject.Tasks
        fName = T.Name
        Application.FileOpenEx Name:="<>\" & fName, IgnoreReadOnlyRecommended:=True
        Application.FileSaveAs Name:="C:\Exports\" & fName, FormatID:="MSProject.XML"
        Application.FileCloseEx pjDoNotSave, CheckIn:=True
    Next T

End Sub

…and create a directory on the C drive called “Exports.”  We’ll export the project files as XML files into this directory.  Why XML?  It tends to clean up much of the junk that ends up getting pushed into these files over time.  It also removes user customizations to views – which I consider a good thing to do periodically.  Change the export format to “MPP” to export as an MPP file.  Note however that this increases the chance of the import not working properly.

Run the macro.  You may have to click through a couple times if a project opens and throws out a prompt to accept updates, etc.

You should end up with a directory full of XML files.


Next up….the code to take that directory and load it to a new Project Server instance.

Exporting MPP Files with VBA

Flagging Project Owner Changes with OData and VBA

This question comes up in the forums pretty frequently, and in fact, I’d actually spec’d out a similar procedure for on-prem Project Server installs which involved hitting the Reporting Database to figure out the Project Owner.  I am not sure how useful this code really is, but I was curious about how hard it would be to consume OData values with VBA and then manipulate Project desktop data with the results, and hence spent a couple cycles figuring it out.

In this case, as usual, I owe credit to someone else who did the heavy lifting.  In doing my research, I came across Marcelo Ruiz’s blog where he pretty much did all of the work.  I’d encourage you to check them out:

  1. VBA and OData (Part I)
  2. VBA and OData (Part II)
  3. VBA and OData (Part III)

I basically took his work and repurposed it to work with Project.

The Scenario

Specific fields are available with Project Server but not available within the Project Client.  Most notably, these fields include the Enterprise Project Type field or the Project Owner.  There are a number of scenarios that could be enable through making these fields visible in the Project Client.  For example, I would like to flag the project when the Project Owner has changed, as many organizations forget to change the Status Manager field – and as a result the task updates all flow back to the original Project Owner.

The Solution

In Project Online, we can’t consume the database data, and must instead consume the OData feed from Project Server.

Hence the code below.  It pings the OData feed and returns the value of the Project Owner, then performs two actions:

  1. Flags tasks where the Status Manager does not equal the Project Owner.
  2. Pops up a message box to let the user know the Project Owner has changed.  (Note this pops up the first time the macro is run on a project, but that would be easy enough to fix.)
Option Explicit

'Copied from Marcelo Ruiz's blog:

' References that need to be added:
' Microsoft XML, v6.0
' Microsoft Scripting Runtime

Const ODataErrorFirst As Long = 100
Const ODataCannotReadUrlError As Long = ODataErrorFirst + 1
Const ODataParseError As Long = ODataErrorFirst + 2

'Given a URL, reads an OData feed or entry into an XML document.
'Copied from Marcelo Ruiz's blog:
Function ODataReadUrl(ByVal strUrl As String) As MSXML2.DOMDocument60
    Dim objXmlHttp As MSXML2.XMLHTTP60
    Dim objResult As MSXML2.DOMDocument60
    Dim strText As String

    'Make a request for the URL.
    'AL - Note I tweaked the XMLHTTP TO XMLHTTP60
    Set objXmlHttp = New MSXML2.XMLHTTP60
    objXmlHttp.Open "GET", strUrl, False

    If objXmlHttp.Status <> 200 Then
        Err.Raise ODataCannotReadUrlError, "ODataReadUrl", "Unable to get '" & strUrl & "' - status code: " & objXmlHttp.Status
    End If

    ' Get the result as text.
    strText = objXmlHttp.responseText
    Set objXmlHttp = Nothing

    ' Create a document from the text.
    Set objResult = New MSXML2.DOMDocument60
    objResult.LoadXML strText
    If objResult.parseError.ErrorCode <> 0 Then
        Err.Raise ODataParseError, "ODataReadUrl", "Unable to load '" & strUrl & "' - " & objResult.parseError.reason
    End If

    Set ODataReadUrl = objResult
End Function

Public Sub CheckProjectOwner()

    Dim PWAURL As String
    Dim ODataURL As String
    Dim objDocument As MSXML2.DOMDocument60
    Dim GetParam As String
    Dim pName As String
    Dim StartTag As String
    Dim FinTag As String
    Dim txtStart As String
    Dim txtFinish As String
    Dim OValue As String
    Dim T As Task

    'Configure the following parameters
    PWAURL = ""
    GetParam = "ProjectOwnerName"

    'Calculate the appropriate strings
    ODataURL = PWAURL & "/_api/ProjectData/"
    pName = ActiveProject.ProjectSummaryTask.Name
    StartTag = "<d:" & GetParam & ">"
    FinTag = "</d:" & GetParam & ">"

    'Ping OData with the query
    Set objDocument = ODataReadUrl(ODataURL & "Projects()?$filter=ProjectName eq '" _
    & pName & "'&$select=" & GetParam)

    'Parse the result set to look for the value
    txtStart = InStr(1, objDocument.XML, StartTag, vbTextCompare) + Len(StartTag)
    txtFinish = InStr(1, objDocument.XML, FinTag, vbTextCompare)
    OValue = Mid(objDocument.XML, txtStart, txtFinish - txtStart)

    Debug.Print OValue

    'Do something with the returned value
    'This changes a flag field if the value doesn't match the status manager

    For Each T In ActiveProject.Tasks
        T.Text1 = OValue
        If T.StatusManagerName = OValue Then
            T.Flag1 = True
            T.Flag1 = False
        End If
    Next T

    'This compares it with another field to launch a dialog box if it's been changed

    If ActiveProject.ProjectSummaryTask.Text1 <> OValue Then
        MsgBox "The Project Owner has been changed.  You should change the status manager.", vbOKOnly, "Reminder"
    End If

    'Store the current status manager for the next iteration.
    ActiveProject.ProjectSummaryTask.Text1 = OValue

End Sub
Flagging Project Owner Changes with OData and VBA

Task Paths in Project 2013

Here’s probably one of the most interesting new features (well, other than reporting) added to the new Microsoft Project desktop client – the Task Path feature.  This feature allows you to easily review a schedule to assess the impact of a single task.


First off, I point out that fellow MVP Nenad Trajkovski has already posted on this topic.  I wanted to add to his post, and perhaps provide some additional information on how to consume this new feature in the updated Project VBA object model.

In the screenshot above, you see that I have selected a single task.  Each of the driving predecessors of the task are highlighted in orange.  In turn, the driven successors of the task are highlighted in purple.

This is all controlled through the Task Path interface, now found on the Gantt Chart Format tab.


Opening that dropdown, you see the options for the task path display.  Note that these options are not mutually exclusive, i.e. I can actually select all of the options to add informational depth to my Gantt Chart display.


As always, just mouse over the bar in the Gantt Chart to see what the color represents – or doubleclick on the Gantt Chart to see the legend.


So that’s great, but what about if I want to filter on only those tasks that are predecessors and successors?  There’s no field affiliated with that setting, so how can I determine which tasks are relevant?

Enter VBA.  The new setting may be consumed via the VBA object model using:

  • Task.PathDrivenSuccessor
  • Task.PathDrivingPredecessor
  • Task.PathPredecessor
  • Task.PathSuccessor

For example, the following VBA code will label each task within the schedule as a Predecessor or Successor in the Text1 field, and then filter the view on only those tasks which have been flagged.

Sub DefinePath()

    Dim T As Task
    Dim I As Integer

    'Clear the existing filter

    'Capture the selected task
    I = Application.ActiveCell.Task.ID

    'Flag the Predecessors and Successors
    For Each T In ActiveProject.Tasks
        T.Text1 = ""
        If T.ID = I Then
            T.Text1 = "Selected Task"
        End If
        If T.PathPredecessor = True Then
            T.Text1 = "Predecessor"
        End If
        If T.PathSuccessor = True Then
            T.Text1 = "Successor"
        End If
    Next T

    'Apply a filter on the flagged tasks
    SetAutoFilter FieldName:="Text1", FilterType:=pjAutoFilterIn, _
    Criteria1:="Predecessor" & Chr$(9) & "Successor" & Chr$(9) & "Selected Task"

End Sub

Here’s what the results look like…


Task Paths in Project 2013