infolink

Saturday, November 10, 2012

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

8 comments:

  1. Hi James,

    Great work! I'm after doing basically the same thing. I'm not a programmer so I'm a little wet behind the ears on what code goes where. Where precisely does the code for the XML doc go? Am I creating a .docx or a Word doc.xml type project?

    Thanks,
    Adam :)

    ReplyDelete
    Replies
    1. Hi Adam,

      Glad I could help, but I am not sure what you are asking here. I created a function like this: Private Function fnBuildXML(ByVal blnContin As Boolean, ByVal objParams As OrderParameters) As DOMDocument . I will update my example to reflect this.

      Thank you
      James Campbell
      MCP, MCSA, MCSE, Security +

      Delete
    2. Hi,

      Thanks for the reply. Effectively what I'm trying to do is have a Word 2010 document (template) which can submit a string (probably using a pop-up form with some variables on/in) to a web service via a SOAP payload and populate the Word document with selected, returned, details.

      Easy huh :-/

      Thanks,
      Adam

      Delete
    3. CaPPsie,

      If you follow the example, you can do exactly what you are looking to do. You can then get the results using http.responseText. Any questions let me know.

      Thank you,
      Jim

      Delete
  2. while googling for microsoft.xmlhttp in vba access to deal with RSS feeds I found your this blog
    do you have any idea using access 2010 / XMLHTTP etc for web based Access work ?
    any small book on this special subject
    The pains you have gone to get deep with your project is commendable
    as I think this branch of MS Access comes under scripting well outside DAO/VBA we do
    thanks Yogendra ( meta.yogi@hotmail.com)

    ReplyDelete
    Replies
    1. Yogendra,

      Thanks for dropping by. I really haven't worked with access for a while. But I believe you can use VBA for this. I am not exactly sure what you exactly want to do. If you would like to send me a better detailed explanation of what you are trying to do, I would gladly give you a hand.

      I know this is a great book about programming within Microsoft access:
      http://www.amazon.com/Access-Programmers-Reference-Teresa-Hennig/dp/0470591668/ref=sr_1_4?s=books&ie=UTF8&qid=1386573668&sr=1-4&keywords=access+2010

      You can also use some of the VBA namespaces to parse RSS feeds, but I would need a little more information of exactly what you are trying to do.

      Thank you,
      Jim

      Delete
  3. Your blog is really great! Your blog is great for anyone who wants to understand this subject more. SEO Service in India

    ReplyDelete

Amazon1