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:
'http://blogs.msdn.com/b/marcelolr/archive/2010/02/16/consuming-odata-with-office-vba-part-i.aspx

' 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:
'http://blogs.msdn.com/b/marcelolr/archive/2010/02/16/consuming-odata-with-office-vba-part-i.aspx
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
    objXmlHttp.send

    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 = "https://ProjectOnline.sharepoint.com/sites/pwa"
    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
        Else
            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
Advertisements
Flagging Project Owner Changes with OData and VBA

One thought on “Flagging Project Owner Changes with OData and VBA

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s