none
PSI Call in VBA (Project 2007) RRS feed

  • Question

  • Hi everbody,

    I am trying to get a list of all projects on the PWA and some basic attributes (checked out?, status date, ...) of these projects via VBA. As far as I know there is no method to do that out of the box available in VBA, so I am trying to get the information via the PSI. I have successfully retrieved a list of all projects (ReadProjectList()) but I am not able to retrieve additional information about the projects.

    Following code throws a "Type mismatch" error at "Set result = objSOAP.ReadProjectStatus(...)":

    Function ReadProjectStatus(name As String) As String()
      Dim objXmlDoc As DOMDocument
      Dim objSOAP As MSSOAPLib.SoapClient
      Dim test As String
      Dim result As Object
      Dim i As Integer
      Dim v() As String
      Set objSOAP = New MSSOAPLib.SoapClient
      objSOAP.MSSoapInit "https://xxxxx/xxxxx/_vti_bin/PSI/Project.asmx?WSDL"
      objSOAP.ConnectorProperty("AuthUser") = "xxxxxx"
      objSOAP.ConnectorProperty("AuthPassword") = "xxxxxx"
      Set result = objSOAP.ReadProjectStatus("{00000000-0000-0000-0000-000000000000}", 0, name, 0)
      If Not result Is Nothing Then
        Set objXmlDoc = New DOMDocument
        objXmlDoc.LoadXML result(1).XML
        For i = 0 To objXmlDoc.FirstChild.FirstChild.ChildNodes.Length - 1
          ReDim Preserve v(0 To i + 1)
          v(i) = objXmlDoc.FirstChild.FirstChild.ChildNodes(i).ChildNodes(1).nodeTypedValue
        Next
        ReadProjectStatus = v()
      End If
    End Function

    My guess is that the GUID is malformed.

     

    Has somebody successfully called a PSI method requiring a GUID as an input parameter?

    Is something else wrong with the code above?

     

    Thanks in advance for your help,

    Michael

     

    P.S.: I am aware that it is possible to retrieve the information directly from the DB but unfortunately I do not have direct DB access.

     

    Wednesday, March 16, 2011 2:41 PM

Answers

  • Hi,

    I have done something like that some time ago, so please don't ask me for any details ;-). In this code, I needed to insert all projects as sub projects (read-only) into a temporary master projects with some additional information. For sure there is a better way to do that, but it was the first time dealing with PSI in VBA and I was only happy when it finally worked:

    ......
    Set ProjectWebSvc = New SoapClient30
    ProjectWebSvc.MSSoapInit cURL & "/_vti_bin/psi/project.asmx?WSDL"
    ProjectWebSvc.ConnectorProperty("EndPointURL") = cURL & "/_vti_bin/psi/project.asmx"

    Dim RPE, RPS, RPSnode As Object
    Dim nRPS, nRPE As MSXML2.IXMLDOMNode
    Dim dRPS, dRPE As MSXML2.DOMDocument


    '**** Die temporäre Zusammenstellung der Projekte erfolgt in einer neuen Datei
        FileNew

    '**** ReadProjektStatus liefert eine Liste aller Projekte, die der angemeldete User im Projectcenter sehen darf
        Set RPS = ProjectWebSvc.ReadProjectStatus(c_Guid_Empty, "PublishedStore", "", 0)
              Set dRPS = New MSXML2.DOMDocument
              dRPS.LoadXML (RPS.Item(1).XML)

    '****Warnmeldungen unterdrücken, z.B. dass Projekt schon Teilprojekt eines anderen Projekts ist
        Alerts (False)
        ViewApply Name:=c_View_Name
        GroupApply Name:="&No Group"

          For Each nRPS In dRPS.SelectNodes("//Project")
                   Set RPSnode = nRPS.SelectSingleNode("PROJ_NAME")
                   v_ProjName = RPSnode.Text
                    Set RPSnode = nRPS.SelectSingleNode("PROJ_UID")
                   v_ProjGUID = RPSnode.Text

                  Set RPE = ProjectWebSvc.ReadProjectentities(v_ProjGUID, 1, "PublishedStore")
                  Set dRPE = New MSXML2.DOMDocument
                  dRPE.LoadXML (RPE.Item(1).XML)
                  Set nRPE = dRPE.SelectSingleNode("//PROJ_INFO_START_DATE")
                    v_PStart = CDate(Left(nRPE.Text, 10))
                  Set nRPE = dRPE.SelectSingleNode("//PROJ_INFO_FINISH_DATE")
                    v_PFinish = CDate(Left(nRPE.Text, 10))
                Set nRPE = dRPE.SelectSingleNode("//ProjectOwnerID")
                    v_powner = nRPE.Text

                   
                    '****Einzelprojekte öffnen
                    If v_PStart <= v_bis And v_PFinish >= v_von Then
                        SelectTaskField Row:=1, Column:="Name", RowRelative:=False
                       
                        ConsolidateProjects Filenames:="<>\" & v_ProjName & "(R/O)", NewWindow:=False, HideSubtasks:=True
                        OutlineShowTasks OutlineNumber:=pjTaskOutlineShowLevel1
                        SelectTaskCell Row:=0, Column:=c_Owner_Column, RowRelative:=True
                        SetActiveCell v_powner, False
                    End If
              
            Next
    ........

    Perhaps that gives you an idea?
    Regards
    Barbara

    • Marked as answer by W_Michael Thursday, March 17, 2011 8:44 AM
    Thursday, March 17, 2011 7:59 AM
    Moderator

All replies

  • other way i would suggest is to use VBA ADODB / Recordset to tap the information directly from project server reporting DB, which is pretty much easy and you can get all relevant information of projects

    You might wanna have a look at the macro here : http://epmxperts.wordpress.com/2011/02/21/open-multiple-project-files-from-within-ms-project-and-do-operation/

     


    Thanks | Sunil Kr Singh | http://epmxperts.wordpress.com
    Wednesday, March 16, 2011 6:01 PM
    Moderator
  • Thanks for the suggestion but as I wrote before I do not have direct access to the DB.
    Thursday, March 17, 2011 7:35 AM
  • Hi,

    I have done something like that some time ago, so please don't ask me for any details ;-). In this code, I needed to insert all projects as sub projects (read-only) into a temporary master projects with some additional information. For sure there is a better way to do that, but it was the first time dealing with PSI in VBA and I was only happy when it finally worked:

    ......
    Set ProjectWebSvc = New SoapClient30
    ProjectWebSvc.MSSoapInit cURL & "/_vti_bin/psi/project.asmx?WSDL"
    ProjectWebSvc.ConnectorProperty("EndPointURL") = cURL & "/_vti_bin/psi/project.asmx"

    Dim RPE, RPS, RPSnode As Object
    Dim nRPS, nRPE As MSXML2.IXMLDOMNode
    Dim dRPS, dRPE As MSXML2.DOMDocument


    '**** Die temporäre Zusammenstellung der Projekte erfolgt in einer neuen Datei
        FileNew

    '**** ReadProjektStatus liefert eine Liste aller Projekte, die der angemeldete User im Projectcenter sehen darf
        Set RPS = ProjectWebSvc.ReadProjectStatus(c_Guid_Empty, "PublishedStore", "", 0)
              Set dRPS = New MSXML2.DOMDocument
              dRPS.LoadXML (RPS.Item(1).XML)

    '****Warnmeldungen unterdrücken, z.B. dass Projekt schon Teilprojekt eines anderen Projekts ist
        Alerts (False)
        ViewApply Name:=c_View_Name
        GroupApply Name:="&No Group"

          For Each nRPS In dRPS.SelectNodes("//Project")
                   Set RPSnode = nRPS.SelectSingleNode("PROJ_NAME")
                   v_ProjName = RPSnode.Text
                    Set RPSnode = nRPS.SelectSingleNode("PROJ_UID")
                   v_ProjGUID = RPSnode.Text

                  Set RPE = ProjectWebSvc.ReadProjectentities(v_ProjGUID, 1, "PublishedStore")
                  Set dRPE = New MSXML2.DOMDocument
                  dRPE.LoadXML (RPE.Item(1).XML)
                  Set nRPE = dRPE.SelectSingleNode("//PROJ_INFO_START_DATE")
                    v_PStart = CDate(Left(nRPE.Text, 10))
                  Set nRPE = dRPE.SelectSingleNode("//PROJ_INFO_FINISH_DATE")
                    v_PFinish = CDate(Left(nRPE.Text, 10))
                Set nRPE = dRPE.SelectSingleNode("//ProjectOwnerID")
                    v_powner = nRPE.Text

                   
                    '****Einzelprojekte öffnen
                    If v_PStart <= v_bis And v_PFinish >= v_von Then
                        SelectTaskField Row:=1, Column:="Name", RowRelative:=False
                       
                        ConsolidateProjects Filenames:="<>\" & v_ProjName & "(R/O)", NewWindow:=False, HideSubtasks:=True
                        OutlineShowTasks OutlineNumber:=pjTaskOutlineShowLevel1
                        SelectTaskCell Row:=0, Column:=c_Owner_Column, RowRelative:=True
                        SetActiveCell v_powner, False
                    End If
              
            Next
    ........

    Perhaps that gives you an idea?
    Regards
    Barbara

    • Marked as answer by W_Michael Thursday, March 17, 2011 8:44 AM
    Thursday, March 17, 2011 7:59 AM
    Moderator
  • Hi Barbara,

    thanks for that piece of code you posted.

    Do you by any chance still know how "c_Guid_Empty" was defined?

    My guess is that this is my only problem.

    The rest of the code should work fine.

     

    Best regards,

    Michael

    Thursday, March 17, 2011 8:20 AM
  • Hi Michael,

    sorry, I missed to post the constants:

    Global Const cURL = "http://intra/pwatest"
    Global Const c_Guid_Empty = "00000000-0000-0000-0000-000000000000"

    Good luck!
    Barbara

    Thursday, March 17, 2011 8:26 AM
    Moderator
  • I finally got it working!

    The problem was that I was using an old version of the SOAP Toolkit.

    As soon as I installed v3 the code started working.

     

    Thanks a lot for your post Barbara.

    Michael

    Thursday, March 17, 2011 8:44 AM
  • Is it working also on a 64bit environment (Windows 7 64bit and Office 64bit)?
    Friday, December 23, 2011 8:46 AM