Solved

Create a SOAP Request/Response using XML from VBA

Posted on 2011-09-30
8
6,368 Views
Last Modified: 2012-05-12

I am trying to create a SOAP call from within Excel VBA using XML.  It the final solution I don't know much about the machine that will run the code so I can't expect or install the SOAP toolkit or anything like that.  I have to use very basic elements.  I am making the assumption that XML will be installed but will use late binding to 'try' for different versions.

Based on some research and the client's "development" document that details the ExportData function I have got the code below.

The commented out XMLDOM code is not needed as yet (I think) but will be used to process the result.

Currently if I run it I get nonsense back - a stream of question marks with the odd dot or numeric in it.

If I run the SOAP request in a test software like SoapUI then I get an "Invalid Path" error but thats due to my export being configured wrongly I think - possibly the test/testExpo table isn't present on my test web service.  I'm working on that aspect with the client though if anything obvious is wrong please let me know!

I want, for now, to get the same error back via XML that I get from SoapUI as I know that is the server responding at least.

Thanks

Simon

---

Dim oXML    As Object
Dim oDom    As Object
Dim sReq    As String

    sReq = ""
    sReq = sReq & "<soapenv:Envelope " & vbCrLf
    sReq = sReq & " xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" " & vbCrLf
    sReq = sReq & " xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" " & vbCrLf
    sReq = sReq & " xmlns:soapenv=""http://schemas.xmlsoap.org/soap/envelope/"" " & vbCrLf
    sReq = sReq & " xmlns:enab=""enablon"">" & vbCrLf
    sReq = sReq & "   <soapenv:Header>" & vbCrLf
    sReq = sReq & "      <tns:AuthHeader id=""h_id1"">" & vbCrLf
    sReq = sReq & "         <EnaUserName xsi:type=""xsd:string"">name</EnaUserName>" & vbCrLf
    sReq = sReq & "         <EnaPassword xsi:type=""xsd:string"">pass</EnaPassword>" & vbCrLf
    sReq = sReq & "      </tns:AuthHeader>" & vbCrLf
    sReq = sReq & "   </soapenv:Header>" & vbCrLf
    sReq = sReq & "   <soapenv:Body>" & vbCrLf
    sReq = sReq & "      <enab:ExportData soapenv:encodingStyle=""http://schemas.xmlsoap.org/soap/encoding/"">" & vbCrLf
    sReq = sReq & "         <Table xsi:type=""xsd:string"">/test/testExpo</Table>" & vbCrLf
    sReq = sReq & "         <FolderId xsi:type=""xsd:int"">0</FolderId>" & vbCrLf
    sReq = sReq & "         <Fields xsi:type=""xsd:string"">f1|f2</Fields>" & vbCrLf
    sReq = sReq & "         <Filter xsi:type=""xsd:string""></Filter>" & vbCrLf
    sReq = sReq & "         <CSVSeparator xsi:type=""xsd:int"">1</CSVSeparator>" & vbCrLf
    sReq = sReq & "         <FormatOptions xsi:type=""xsd:int"">22</FormatOptions>" & vbCrLf
    sReq = sReq & "         <FormatType xsi:type=""xsd:int"">2</FormatType>" & vbCrLf
    sReq = sReq & "         <ExportOptions xsi:type=""xsd:int"">1</ExportOptions>" & vbCrLf
    sReq = sReq & "      </enab:ExportData>" & vbCrLf
    sReq = sReq & "   </soapenv:Body>" & vbCrLf
    sReq = sReq & "</soapenv:Envelope>" & vbCrLf


Set oXML = CreateObject("MSXML2.ServerXMLHTTP")
'Set oDom = CreateObject("Microsoft.XMLDOM")

oXML.Open "POST", "https://rct2.enablon.com/6.0-6/sedex/rpc_encoded.wsdl", False
oXML.setRequestHeader "Content-Type", "text/xml"

'oDom.loadXML sReq

oXML.sEnd sReq

Debug.Print oXML.responseBody
0
Comment
Question by:RogueSolutions
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
8 Comments
 
LVL 17

Accepted Solution

by:
andrewssd3 earned 500 total points
ID: 36891150
Your code is working fine - it's just that responsebody is being returned as an array of byte character codes - when you cast this to a string (as you do when you debug.print it), VBA expects it will be a unicode string with two bytes per character.  Yours has only one byte per character, so if you add this code instead of your debug.print you get a proper error message:
Debug.Print oXML.responsebody

Dim i As Long
Dim aBody() As Byte
aBody = oXML.responsebody

For i = LBound(aBody) To UBound(aBody)
    Debug.Print Chr$(aBody(i));
Next i

Open in new window


You can either code round this as I have done, or see if there is some option at the server side to send back a unicode string instead
0
 
LVL 17

Assisted Solution

by:andrewssd3
andrewssd3 earned 500 total points
ID: 36891165
Actually responseText contains the same message without doing my code bit.  It's in xml, but I think response xml is not populated bacuase of the unicode thing.  To get a dom object from the xml you probably have to Load the string from responseText.
0
 
LVL 5

Author Comment

by:RogueSolutions
ID: 36891360
Andrew

Thanks for that, have added the loop and I now get proper text out.

Unfortunately its an HTML page indicating "The page you are looking for cannot be displayed because an invalid method (HTTP verb) was used to attempt access."

I suspect that means my WSDL location is incorrect?  The address used in the VBA is the same as I gave SoapUI and that worked ok, the web service responded at least.  Do I need to change how I address the WSDL?
0
Automating Your MSP Business

The road to profitability.
Delivering superior services is key to ensuring customer satisfaction and the consequent long-term relationships that enable MSPs to lock in predictable, recurring revenue. What's the best way to deliver superior service? One word: automation.

 
LVL 17

Assisted Solution

by:andrewssd3
andrewssd3 earned 500 total points
ID: 36891505
I'm afraid I don't know the answer to that.  I think you're right that you need a different address to post to, but you'll need to ask the owner of web service what it is.  I don't think you normally post to the wsdl location.
0
 
LVL 17

Assisted Solution

by:andrewssd3
andrewssd3 earned 500 total points
ID: 36891582
A couple of thoughts - I've never done this to an https server, only ever to http - are there additional requirements? Also although it was a few years ago that I did this, when we changed from IIS to an apache server I had to change the request header to reference the name of the method being iinvoked:
   With m_xmlSoapTransport
        .Open "POST", m_strURL, True
        .setRequestHeader "SOAPAction", "#" & strMethodName
        .send m_xmlSoapMessage
    End With

Open in new window

You might have to do something like this, but it is server specific, so I think you do need to speak to the server owner
0
 
LVL 5

Author Comment

by:RogueSolutions
ID: 36891622
Yep, thanks to both of you for your thoughts.

I am working on speaking to the web service supplier (third party rather than my actual client)
0
 
LVL 50
ID: 37412272
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

To properly understand GitHub, let’s divide it into two words ‘Git’ and ‘Hub’. Git is basically a ‘Distribution Version Control’ (DVC) and ‘Source Code Management’ (SCM) system widely used by software programmers while Hub means the efficient centre…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question