How to consume a web service in Microsoft Word using VBA

A couple of months ago I was asked to take on a project which would use a word document to place an order over the web. The word document would need to post the order to a web service and show a message box to the end user,  letting them know that the order was successfully submitted and return an order number or that an error has occurred and what the error was.

The requirements for my project were as such:

• Create a word document that will capture several fields which will be populated by another proprietary software.(Using Word 2010)
• Create a method that will take the data from the form and put it into an XML envelope to be sent via SOAP.
• Keep the document as backward compatible as possible.
• Submit the SOAP envelope to the web service and handle error or successful submission.

Since I have never consumed or worked with web services in Word or VBA the first thing I did was hit the search engines and found nothing but useless complicated outdated examples on how to consume a web service using VBA in Microsoft Word.

I see that Microsoft recommends using Web Service References Tool:

Calling XML Web Services from Office VBA Using the Web Service References Tool

Which leads to a dead link.:

Microsoft® VBA Web Service References Tool

Plus my concern with using the Web Service References Tool limits the version of Microsoft Word, and I would like to support back to at least Word 2000.

So after much research and trial and error I decided to use SOAP utilizing Microsoft XML 3.0. You could use the same code with Microsoft XML 2.0 also, but I do not have the library available on my current machine, so 3.0 will do.

For demonstration purposes, I will simplify the form for this example.

Here is the code to  create your XML Doc:

Private Function fnBuildXML(ByVal blnContin As Boolean, ByVal objParams As OrderParameters) As DOMDocument

   Set MyParams = New OrderParameters
   Dim objDom As DOMDocument
   Dim objRootElem As IXMLDOMElement
   Dim objRootElem2 As IXMLDOMElement
   Dim objMemberElem As IXMLDOMElement
   Set objDom = New DOMDocument
  
    With MyParams
       
        ' Creates root element
        Set objRootElem = objDom.createElement("myOrderParameters")
        objDom.appendChild objRootElem

 '<PlaceOrder xmlns="http://webservice.com/">
   Dim mystring As String
        ' Creates Member element
        Set objMemberElem = objDom.createElement("OrderTaxSearch")
        objRootElem.appendChild objMemberElem
        mystring = ff("Check1").Result
        objMemberElem.Text = CStr(ff("Check1").Result)
    
        Set objMemberElem = objDom.createElement("OrderAssessmentSearch")
        objRootElem.appendChild objMemberElem
        objMemberElem.Text = ff("Check2").Result
      
        Set objMemberElem = objDom.createElement("OrderUtilitiesSearch")
        objRootElem.appendChild objMemberElem
        objMemberElem.Text = ff("Check3").Result

End If
End With
Set fnBuildXML = objDom
Set objDom = Nothing
End Function

Now that was simplified to just give an idea how to create the XML doc for the SOAP envelope. Here is example code to communicate with the web service:

    Dim envelope As String
    Dim http As New MSXML2.XMLHTTP
    Dim URL As String

    URL = "http://WEBSERVICE.net/neworders.asmx?op=PlaceOrder"

    envelope = "<?xml version='1.0' encoding='UTF-8'?>" & _
    "<soap:Envelope xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' xmlns:xsd='http://www.w3.org/2001/XMLSchema' " & _
    "xmlns:soap='http://schemas.xmlsoap.org/soap/envelope/'>" & _
    "<soap:Body>" & "<PlaceOrder xmlns='http://WEBSERVICE.com/'>" & CStr(objDom.XML) & "</PlaceOrder></soap:Body></soap:Envelope>"

    Call http.Open("POST", URL, False) //make sure to set async to FALSE!!!!!
    http.SetRequestHeader "Content-Type", "text/xml"

        envelope = Replace(envelope, "True", "true")
        envelope = Replace(envelope, "False", "false")
                http.Send (envelope) //Here you are sending the SOAP envelope to the webservice
                MsgBox (http.responseText) //Display results from service
          
      Set http = Nothing
End With
      Set MyParams = Nothing

There ya go, please note this line:
Call http.Open("POST", URL, False)

Make sure to set varAsync to FALSE. As the default is true, word does not handle Async with web services right from what I could tell and this is defaulted to true, so your code will fail if you try to read the response, especially the longer the response. Let it wait, so set this to false, this really caused me a lot of issues until I set it to false. more about  MSXML2.XMLHTTP  here:

XMLHttpRequest object

Hope this helps you save some time, and if you have any questions, please let me know.






Buy Me a Beer