Handling of VBA XML response

[seisbye]

I have created the below code..

Sub VATCHECK()
    Dim sURL As String
    Dim sEnv As String
    Dim xmlhttp As New MSXML2.xmlhttp
    Dim xmlDoc As New MSXML2.DOMDocument 'DOMDocument
    sURL = "http://ec.europa.eu/taxation_customs/vies/services/checkVatService"
     
    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 & "<ns1:checkVatApprox xmlns:ns1='urn:ec.europa.eu:taxud:vies:services:checkVat:types'>"
    sEnv = sEnv & "<ns1:countryCode>ES</ns1:countryCode>"
    sEnv = sEnv & "<ns1:vatNumber>B85364495</ns1:vatNumber>"
    sEnv = sEnv & "<ns1:traderName>Tiger Stores Spain S.L.</ns1:traderName >/"
    sEnv = sEnv & "<ns1:traderCompanyType></ns1:traderCompanyType>"
    sEnv = sEnv & "<ns1:traderStreet></ns1:traderStreet>"
    sEnv = sEnv & "<ns1:traderPostcode></ns1:traderPostcode>"
    sEnv = sEnv & "<ns1:traderCity></ns1:traderCity>"
    sEnv = sEnv & "<ns1:requesterCountryCode>DK</ns1:requesterCountryCode>"
    sEnv = sEnv & "<ns1:requesterVatNumber>15690488</ns1:requesterVatNumber>"
    sEnv = sEnv & "</ns1:checkVatApprox>"
    sEnv = sEnv & "</soapenv:Body>"
    sEnv = sEnv & "</soapenv:Envelope>"
    
    myFile = "c:\temp\text.xml"
    myFile2 = "c:\temp\text2.html"
    
    
    Open myFile For Output As #1
    
    Write #1, sEnv
    
    Close #1
    With xmlhttp
        .Open "POST", sURL, False
        .setRequestHeader "Content-Type", "text/xml;"
        .send sEnv
        
        'xmlDoc.Load ("c:\temp\text2.xml")

'MsgBox .responseText

      
 End With

 End Sub

The Code contacts the webservice provided by the European commision. and then validates the VAT number, and all the other field which are filled with data.

I have 3 problems.

1. I can't store the data reponse like it is here. I have tried to Google it. But i'm simply not adequate enough to compile what i need. I need simple code so i can look up the reponse value by tag. the responce gives the tag traderName 2 times. it shows the request and the responcse. I need the reponse pulled. so I can store it in colomns in Excel.

2. If it's possible(I know it is). I want the reponse to be transformed into valid, invalid or not processed If you look at the WDSL http://ec.europa.eu/taxation_customs/vies/checkVatService.wsdl there are a matchcode. But i'm guessing it needs to be put into the soap envelope? and i have no idea how.

3. I also need to store all the reponses. I can load it into a XML doc. og just save that. But it looks crappy when you open it. So if there are an easy whay to have to transpone, so it easier to read?

In advance THANK YOU SO MUCH..

reg. edit.. The envelope tags keeps disapering, no matter what i do.. So i have attached an image op it.. How do I wrapped in this forum not have it not disapear?

 

[manka]

Wow, this looks great. Thanks for sharing, really appreciate it. Will give it a go in the morning!

Thanks again

 

[manka]

Wow, this looks great. Thanks for sharing, really appreciate it. Will give it a go in the morning!

Thanks again

dcafsd
I couldn't wait to try it!

Unfortunately it doesn't work, but I think this because I don't have the formulae in the offset location to column A based on the formulae. Could you confirm their contents? Or alternatively if I PM you my email address could you send me a working version of your file?

Thanks
Cass