Connect excel data to SOAP UI

[rexone]

Hi there,

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.

Any other equivalent solution is also appreciated. 

[#2]

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?

[#3]

@Jan Karel Pieterse 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.

[#4]

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)?

[#5]

@Jan Karel Pieterse It is the structure service. I am not familiar with VBA so would really appreciate if you could please guide to any relevant document.

[#6]

@rexone 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 = "<?xml version=""1.0"" encoding=""utf-8""?>"
    sEnv = sEnv & "<soapenv:Envelope xmlns:soapenv=""http://schemas.xmlsoap.org/soap/envelope/"">"
    sEnv = sEnv & "    <soapenv:Body>"
    sEnv = sEnv & "        <urn:checkVat xmlns:urn=""urn:ec.europa.eu:taxud:vies:services:checkVat:types"">"
    sEnv = sEnv & "            <urn:countryCode>_COUNTRYCODE_</urn:countryCode>"
    sEnv = sEnv & "            <urn:vatNumber>_VATNUMBER_</urn:vatNumber>"
    sEnv = sEnv & "        </urn:checkVat>"
    sEnv = sEnv & "    </soapenv:Body>"
    sEnv = sEnv & "</soapenv:Envelope>"


    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 & "\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) > 0 Then
            MsgBox "Error: " & 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" & vbNewLine & companyName & vbNewLine & companyAddress
        Else
            MsgBox "Invalid VAT number"
        End If
    End With
End Sub