Sub SoapCall()
Dim sessiontext As String
Dim ProjectText As String
Dim xogURL As String
  xogURL = "https://cppmXXXXXXXXX.ondemand.ca.com/niku/xog"
  sessiontext = Login("username", "password", xogURL)
  querytext = GetQuery(sessiontext, "ppm_query_id", xogURL)
  logouttext = Logout(sessiontext, xogURL)
End Sub


Function Login(Optional xogUserName As String, Optional xogPassword As String, Optional xogURL As String) As String

        Dim xmlHTTPConnection As MSXML2.XMLHTTP60

    Set xmlHTTPConnection = New MSXML2.XMLHTTP60

 

    If xogURL = "" Then

        xogURL = "https://" & clarityInstance & ".ondemand.ca.com/niku/xog"

    End If

    

    xogMessage = xogMessage & "<soap:Envelope xmlns:soap=""http://schemas.xmlsoap.org/soap/envelope/"" xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" xmlns:tns=""http://www.niku.com/xog/Query"">"

    xogMessage = xogMessage & "   <soap:Header/>"

    xogMessage = xogMessage & "   <soap:Body>"

    xogMessage = xogMessage & "       <tns:Login>"

    xogMessage = xogMessage & "           <tns:Username>" & xogUserName & "</tns:Username>"

    xogMessage = xogMessage & "           <tns:Password>" & xogPassword & "</tns:Password>"

    xogMessage = xogMessage & "       </tns:Login>"

    xogMessage = xogMessage & "   </soap:Body>"

    xogMessage = xogMessage & "</soap:Envelope>"

    

    xmlHTTPConnection.Open "POST", xogURL, False

    xmlHTTPConnection.setRequestHeader "Content-Type", "text/xml"

 

    xmlHTTPConnection.send xogMessage

    

    Dim xogSessionResponse As MSXML2.DOMDocument60

    Set xogSessionResponse = New MSXML2.DOMDocument60

    

    With xogSessionResponse

        .async = False

        .preserveWhiteSpace = False

        .validateOnParse = False

        .resolveExternals = False

        .setProperty "SelectionLanguage", "XPath"

        .setProperty "SelectionNamespaces", "xmlns:xog=""http://www.niku.com/xog"""

    End With

    

    xogSessionResponse.LoadXML (xmlHTTPConnection.responseText)

 

    Login = xogSessionResponse.Text

    

    Set xmlHTTPConnection = Nothing

    Set xogSessionResponse = Nothing

    

End Function


' This Procedure calls XOG and returns results from a Query (it just writes each Property Name and Value pair to the debug window):

Function GetQuery(sSID As String, sQueryID, Optional xogURL As String) As String

Dim sQry As String

Dim sHeader As String

Dim xQuery As XMLHTTP60
Dim xogSessionResponse As DOMDocument
Dim soapenv As IXMLDOMNode
Dim theQueryNode As IXMLDOMNode
Dim QueryProp As IXMLDOMAttribute
 

Dim oS As Worksheet

 

    If xogURL = "" Then

        xogURL = "https://" & clarityInstance & ".ondemand.ca.com/niku/xog"

    End If

 

    Set xQuery = New ServerXMLHTTP60

    sHeader = "xmlns:soap=""http://schemas.xmlsoap.org/soap/envelope/"""

    sHeader = sHeader & " xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"""

    sHeader = sHeader & " xmlns:xsd=""http://www.w3.org/2001/XMLSchema"""

    sHeader = sHeader & " xmlns:tns=""http://www.niku.com/xog/Query"""
    
    sQry = "<soap:Envelope " & sHeader & ">"

    sQry = sQry & " <soap:Header>"

    sQry = sQry & "     <tns:Auth>"

    sQry = sQry & "         <tns:SessionID>" & sSID & "</tns:SessionID>"

    sQry = sQry & "     </tns:Auth>"

    sQry = sQry & " </soap:Header>"

    sQry = sQry & " <soap:Body>"

    sQry = sQry & "     <Query xmlns=""http://www.niku.com/xog/Query"">"

    sQry = sQry & "         <Code>" & sQueryID & "</Code>"
    

    sQry = sQry & "     </Query>"

    sQry = sQry & "   </soap:Body>"

    sQry = sQry & "</soap:Envelope>"

    xQuery.Open "POST", xogURL, False

    xQuery.setRequestHeader "soapAction", "QueryCSV"

    xQuery.send sQry

    Set xogSessionResponse = New DOMDocument

    With xogSessionResponse

        .async = False

        .preserveWhiteSpace = False

        .validateOnParse = False

        .resolveExternals = False

        .setProperty "SelectionLanguage", "XPath"

        .setProperty "SelectionNamespaces", "xmlns:xog=""http://www.niku.com/xog"""

    End With

    xogSessionResponse.LoadXML (xQuery.responseText)

    Set soapenv = xogSessionResponse.ChildNodes(0).ChildNodes(1).ChildNodes(0).ChildNodes(1)
    
    Dim xDoc As DOMDocument
    Set xDoc = New DOMDocument
     
    If Not xDoc.LoadXML(soapenv.xml) Then
        Err.Raise xDoc.parseError.ErrorCode, , xDoc.parseError.reason
        Debug.Print xDoc.parseError.ErrorCode & "-" & xDoc.parseError.reason
    End If

Dim list As IXMLDOMNodeList
Set list = xDoc.SelectNodes("//Records/Record")

Dim node As IXMLDOMNode, nd As IXMLDOMNode
Dim i As Integer


        With Sheet1.Rows(1)
            .Cells(1).Value = "Project Title"
            .Cells(2).Value = "Project Start Date"
            .Cells(3).Value = "Project End Date"
            .Cells(4).Value = "Project Phase"
            .Cells(5).Value = "Project Manager"
            .Cells(6).Value = "Project Sponsor"
            .Cells(7).Value = "Project Complete 1 = YES 0 = NO"
        End With


i = 1

For Each node In list
        i = i + 1
        With Sheet1.Rows(i)
            .Cells(1).Value = GetNodeValue(node, "prj_name")
            .Cells(2).Value = GetNodeValue(node, "prj_start")
            .Cells(3).Value = GetNodeValue(node, "prj_finish")
            .Cells(4).Value = GetNodeValue(node, "prj_phase")
            .Cells(5).Value = GetNodeValue(node, "prj_manager")
            .Cells(6).Value = GetNodeValue(node, "prj_sponsor")
            .Cells(7).Value = GetNodeValue(node, "prj_complete")
        End With

    Next node
    
    GetQuery = xogSessionResponse.Text
    
    Set xQuery = Nothing

    Set xogSessionResponse = Nothing

End Function

Function GetNodeValue(node As IXMLDOMNode, xp As String)
    Dim n As IXMLDOMNode, nv
    Set n = node.SelectSingleNode(xp)
    If Not n Is Nothing Then nv = n.nodeTypedValue
    GetNodeValue = nv
End Function

 

' This procedure logs out of XOG:

Function Logout(sessionID As String, Optional xogURL As String) As String

    

    Dim xmlHTTPConnection As MSXML2.XMLHTTP60

    Set xmlHTTPConnection = New MSXML2.XMLHTTP60

 

    If xogURL = "" Then

        xogURL = "https://" & clarityInstance & ".ondemand.ca.com/niku/xog"

    End If

    

    xogMessage = xogMessage & "<soap:Envelope xmlns:soap=""http://schemas.xmlsoap.org/soap/envelope/"" xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" xmlns:tns=""http://www.niku.com/xog/Query"">"

    xogMessage = xogMessage & "   <soap:Header/>"

    xogMessage = xogMessage & "   <soap:Body>"

    xogMessage = xogMessage & "       <tns:Logout>"

    xogMessage = xogMessage & "           <tns:SessionID>" & sessionID & "</tns:SessionID>"

    xogMessage = xogMessage & "       </tns:Logout>"

    xogMessage = xogMessage & "   </soap:Body>"

    xogMessage = xogMessage & "</soap:Envelope>"

    

    xmlHTTPConnection.Open "POST", xogURL, False

    xmlHTTPConnection.setRequestHeader "Content-Type", "text/xml"

 

    xmlHTTPConnection.send xogMessage

    

    Dim xogSessionResponse As MSXML2.DOMDocument60

    Set xogSessionResponse = New MSXML2.DOMDocument60

    

    With xogSessionResponse

        .async = False

        .preserveWhiteSpace = False

        .validateOnParse = False

        .resolveExternals = False

        .setProperty "SelectionLanguage", "XPath"

        .setProperty "SelectionNamespaces", "xmlns:xog=""http://www.niku.com/xog"""

    End With

    

    xogSessionResponse.LoadXML (xmlHTTPConnection.responseText)

 

    Logout = xogSessionResponse.Text

    

    Set xmlHTTPConnection = Nothing

    Set xogSessionResponse = Nothing

    

End Function