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:
I basically took his work and repurposed it to work with Project.
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.
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:
- Flags tasks where the Status Manager does not equal the Project Owner.
- 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