Connect excel data to SOAP UI

Question

Hi there,&nbsp;I have data in excel which I have concatenated as per XML required in SOAP UI. Is there a way to directly connect this to SOAP rather than moving the data manually to SOAP.&nbsp;Any other equivalent solution is also appreciated.&nbsp;

jkpieterse · Answer

It is very likely you'll have to do this using VBA programming. The XML you mention is likely what is called the SOAP Envelope, am I correct?

rexone · Answer

JKPieterse&nbsp;Yes I have basically concatenated the SOAP payload in excel. Two columns have hard text while the other two have dynamic values. Guess this is the envelope you are referring to.

jkpieterse · Answer

You'll have to refer to the documentation of the webservice to find out how to call it properly from VBA. Which service is it (which URL)?

rexone · Answer

JKPieterse&nbsp;It is the structure service. I am not familiar with VBA so would really appreciate if you could please guide to any relevant document.

jkpieterse · Answer

rexone&nbsp;Here is some sample code (To try you need to enter a valid EU VAT number instead of the current placeholder text)). You'll have to change the envelope, the urls and the headings according to the documentation of the webservice you are trying to use.
Please note that this code requires setting a reference in VBA (Tools, References) to the library called "Microsoft XML, V5"
Option Explicit

'Reference: Microsoft XML, V4.0

Sub DoIt()
    Dim sURL As String
    Dim sEnv As String
    Dim xmlhtp As New MSXML2.XMLHTTP
    Dim xmlDoc As New DOMDocument
    Dim result As String
    Dim errorMsg As String
    Dim companyName As String
    Dim companyAddress As String
    'The webservice
    sURL = "http://ec.europa.eu/taxation_customs/vies/services/checkVatService"

'Build the soap envelope the webservice needs

sEnv = "&lt;?xml version=""1.0"" encoding=""utf-8""?&gt;"
    sEnv = sEnv &amp; "&lt;soapenv:Envelope xmlns:soapenv=""http://schemas.xmlsoap.org/soap/envelope/""&gt;"
    sEnv = sEnv &amp; "    &lt;soapenv:Body&gt;"
    sEnv = sEnv &amp; "        &lt;urn:checkVat xmlns:urn=""urn:ec.europa.eu:taxud:vies:services:checkVat:types""&gt;"
    sEnv = sEnv &amp; "            &lt;urn:countryCode&gt;_COUNTRYCODE_&lt;/urn:countryCode&gt;"
    sEnv = sEnv &amp; "            &lt;urn:vatNumber&gt;_VATNUMBER_&lt;/urn:vatNumber&gt;"
    sEnv = sEnv &amp; "        &lt;/urn:checkVat&gt;"
    sEnv = sEnv &amp; "    &lt;/soapenv:Body&gt;"
    sEnv = sEnv &amp; "&lt;/soapenv:Envelope&gt;"

sEnv = Replace(sEnv, "_COUNTRYCODE_", UCase("CountryCodeGoesHere"))
    sEnv = Replace(sEnv, "_VATNUMBER_", "VATNumerGoesHere")

With xmlhtp
        'Open webservice
        .Open "post", sURL, False
        .setRequestHeader "Host", "http://ec.europa.eu/taxation_customs/vies/services"
        .setRequestHeader "Content-Type", "text/xml; charset=utf-8"
        .setRequestHeader "Accept-encoding", "zip"
        'Send it the envelope
        .send sEnv
        
        'Now retrieve the result from the webservice
        xmlDoc.loadXML .responseText
        'To save the result to a file:
        'xmlDoc.Save ThisWorkbook.Path &amp; "\WebQueryResult.xml"
        On Error Resume Next
        result = xmlDoc.getElementsByTagName("valid").Item(0).Text
        errorMsg = xmlDoc.getElementsByTagName("faultstring").Item(0).Text
        On Error GoTo 0
        If Len(errorMsg) &gt; 0 Then
            MsgBox "Error: " &amp; errorMsg
            Exit Sub
        End If
        companyName = xmlDoc.getElementsByTagName("name").Item(0).Text
        companyAddress = xmlDoc.getElementsByTagName("address").Item(0).Text

If LCase(result) = "true" Then
            MsgBox "Valid VAT number" &amp; vbNewLine &amp; companyName &amp; vbNewLine &amp; companyAddress
        Else
            MsgBox "Invalid VAT number"
        End If
    End With
End Sub

&nbsp;

Forum Discussion

Connect excel data to SOAP UI

5 Replies