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?

 

[seisbye]

 

[jkpieterse]

The technical info about this webservice is here: VIES

This code returns whether or not you have a valid VAT number:

Sub VATCHECK()
    Dim sURL As String
    Dim sEnv As String
    Dim xmlhttp As New MSXML2.xmlhttp
    Dim xmlDoc As New MSXML2.DOMDocument    'DOMDocument
    Dim sCountryCode As String
    Dim sVATNo As String
    sURL = "http://ec.europa.eu/taxation_customs/vies/services/checkVatService"
    sCountryCode = "ES"
    sVATNo = "B85364495"
    sEnv = "<soapenv:Envelope xmlns:soapenv=""http://schemas.xmlsoap.org/soap/envelope/"" xmlns:urn=""urn:ec.europa.eu:taxud:vies:services:checkVat:types"">"
    sEnv = sEnv & "<soapenv:Header/>"
    sEnv = sEnv & "<soapenv:Body>"
    sEnv = sEnv & "<urn:checkVat>"
    sEnv = sEnv & "<urn:countryCode>" & sCountryCode & "</urn:countryCode>"
    sEnv = sEnv & "<urn:vatNumber>" & sVATNo & "</urn:vatNumber>"
    sEnv = sEnv & "</urn:checkVat>"
    sEnv = sEnv & "</soapenv:Body>"
    sEnv = sEnv & "</soapenv:Envelope>"

    With xmlhttp
        .Open "POST", sURL, False
        .setRequestHeader "Content-Type", "text/xml;"
        .send sEnv
        Set xmlDoc = New MSXML2.DOMDocument
        xmlDoc.LoadXML .responseText
        If LCase(xmlDoc.getElementsByTagName("valid").Item(0).Text) = "true" Then
            MsgBox "Valid VAT number"
        Else
            MsgBox "Invalid VAT number"
        End If
    End With

End Sub

 

[seisbye]

Thanks for the input. This got be further, that i can now search for the reponses. I did find the page with the techinal info, which were how i managed to make the Soap envelope. but it dosn't explain how to substitue the response from numbers to text.. i have found a description on it, here. XML Schema Element Substitution But i can only find examples on how to create the WDSL with the info, not how to extract it via a Soap envelope through VBA.

So i would still very much appriciate anyone who could help with problem 2 and 3.. But thank you very much for solving problem 1. which is of course my biggest hurdle..

 

[jkpieterse]

WTF, that is absolutely NOT the code that I originally posted?

Seems the forum software wrecks my VBA here.

 

[jkpieterse]

Here is another attempt at posting the code:

Option Explicit

Sub VATCHECK()
    Dim sURL As String
    Dim sEnv As String
    Dim xmlhttp As New MSXML2.xmlhttp
    Dim xmlDoc As New MSXML2.DOMDocument    'DOMDocument
    Dim sCountryCode As String
    Dim sVATNo As String
    sURL = "http://ec.europa.eu/taxation_customs/vies/services/checkVatService"
    sCountryCode = "ES"
    sVATNo = "B85364495"
    sEnv = "<soapenv:Envelope xmlns:soapenv=""http://schemas.xmlsoap.org/soap/envelope/"" xmlns:urn=""urn:ec.europa.eu:taxud:vies:services:checkVat:types"">"
    sEnv = sEnv & "<soapenv:Header/>"
    sEnv = sEnv & "<soapenv:Body>"
    sEnv = sEnv & "<urn:checkVat>"
    sEnv = sEnv & "<urn:countryCode>" & sCountryCode & "</urn:countryCode>"
    sEnv = sEnv & "<urn:vatNumber>" & sVATNo & "</urn:vatNumber>"
    sEnv = sEnv & "</urn:checkVat>"
    sEnv = sEnv & "</soapenv:Body>"
    sEnv = sEnv & "</soapenv:Envelope>"

    With xmlhttp
        .Open "POST", sURL, False
        .setRequestHeader "Content-Type", "text/xml;"
        .send sEnv
        Set xmlDoc = New MSXML2.DOMDocument
        xmlDoc.LoadXML .responseText
        If LCase(xmlDoc.getElementsByTagName("valid").Item(0).Text) = "true" Then
            MsgBox "Valid VAT number"
        Else
            MsgBox "Invalid VAT number"
        End If
    End With

End Sub

 

[seisbye]

That is why i posted the image Don't know what happens. but i'm guessing it the HTML addapt feature. so when you use <> brackets. it sees it as an html code??

 

[jkpieterse]

Yes. to get it properly posted I replaced the less than signs with lt; and the greater than with gt;

 

[manka]

Hi

So I found this code, and I was really hoping someone could help me with pulling the request identifier based on the structure of the WDSL file if we specify a requestervatnumber and requesterID (it should work if you use the same VAT number for both?).

Thanks - really appreciate any help that can be provided.

 

[seisbye]

Here's the total request script I build.. version 1. i made it to loop through the list. give me response name adress etc. This was version 1. If you need to ask to spanish companies, you don't get the adress return. you need to send what you know, and it will reply if it's valid. this is not build into my version 1 script. this is however not a legal requirement. Only to save the responses including the request ID.. that it what the " myfile = path & C.Value & ".xml" is about.. you just need to set a path.

I also put in the responses into the excel list, to find out which aren't valid. hope it helps. took me some time to figure o

Sub VATCHECK()    Dim sURL As String
    Dim sEnv As String
    Dim xmlhttp As New MSXML2.xmlhttp
    Dim xmlDoc As New MSXML2.DOMDocument 'DOMDocument
    Dim sCountryCode As String
    Dim sVATNo As String
    Dim result As Integer
    Dim test As String
    Dim path As String
    Dim regvat As String
    Dim reqland As String
    
      With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With
    
    regvat = Sheets("Noter").Range("B4").Value
    regland = Sheets("Noter").Range("B5").Value
       
       
    path = Sheets("Noter").Range("B1").Value & "\" & Sheets("Noter").Range("B2").Value & "\"
    
    If Len(Dir(path, vbDirectory)) = 0 Then
       MkDir path
    End If
    
    sURL = "http://ec.europa.eu/taxation_customs/vies/services/checkVatService"
    
    Sheets("REQ").Select
    
    
    'renser tidligere resultater
    Range("O4:Z40000").ClearContents
        
    Cells.Interior.ColorIndex = xlNone
     
    'starter forespørgelser
     
    For Each C In Range("A4:A40000")
   
        
        If C.Value = "" Then GoTo ejvalue
    
    '    myFile2 = "c:\temp\text2.html"
        
    
          
                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>" & C.Offset(0, 5).Value & "</ns1:countrycode>"
                sEnv = sEnv & "<ns1:vatnumber>" & C.Offset(0, 7).Value & "</ns1:vatnumber>"
                sEnv = sEnv & "<ns1:tradername>" & C.Offset(0, 2).Value & "</ns1:tradername>/"
                sEnv = sEnv & "<ns1:tradercompanytype>" & C.Offset(0, 9).Value & "</ns1:tradercompanytype>"
                sEnv = sEnv & "<ns1:traderstreet>" & C.Offset(0, 6).Value & "</ns1:traderstreet>"
                sEnv = sEnv & "<ns1:traderpostcode>" & C.Offset(0, 3).Value & "</ns1:traderpostcode>"
                sEnv = sEnv & "<ns1:tradercity>" & C.Offset(0, 8).Value & "</ns1:tradercity>"
                sEnv = sEnv & "<ns1:requestercountrycode>" & regland & "</ns1:requestercountrycode>"
                sEnv = sEnv & "<ns1:requestervatnumber>" & regvat & "</ns1:requestervatnumber>"
                sEnv = sEnv & "</ns1:checkvatapprox>"
                sEnv = sEnv & "</soapenv:body>"
                sEnv = sEnv & "</soapenv:envelope>"
            
                   With xmlhttp
                    .Open "POST", sURL, False
                    .setRequestHeader "Content-Type", "text/xml;"
                    .send sEnv
                    Set xmlDoc = New MSXML2.DOMDocument
                    xmlDoc.LoadXML .responseText
                    
                    'MsgBox .responseText
                    
                    myfile = path & C.Value & ".xml"


                    Open myfile For Output As #1
                    
                    Write #1, .responseText
                    
                    Close #1
                   
                   
                   
                            
                    'VAT TEST
                    
                            
                      If LCase(xmlDoc.getElementsByTagName("valid").Item(0).text) = "true" Then
                      
                        C.Offset(0, 7).Interior.ColorIndex = 4
                        
                        C.Offset(0, 14).Value = xmlDoc.getElementsByTagName("requestDate").Item(0).text
                       
                        C.Offset(0, 15).Value = xmlDoc.getElementsByTagName("requestIdentifier").Item(0).text
                        
                      Else
                          
                        C.EntireRow.Interior.ColorIndex = 3
                        
                      End If
                      
                      
                    'TraderName


                    C.Offset(0, 21).Value = xmlDoc.getElementsByTagName("traderName").Item(0).text


                    'TraderPostcode
                    
                    'C.Offset(0, 22).Value = xmlDoc.getElementsByTagName("traderPostcode").Item(0).Text


                    'TraderStreet or adress
                   On Error Resume Next
                    
                   
                   C.Offset(0, 23).Value = xmlDoc.getElementsByTagName("traderAddress").Item(0).text
                    
                   C.Offset(0, 23).Value = xmlDoc.getElementsByTagName("traderStreet").Item(0).text
                    
                     'TraderCity


                   C.Offset(0, 24).Value = xmlDoc.getElementsByTagName("traderCity").Item(0).text
                                
                  End With
                   
       
           
ejvalue:


           
     Next C
    
    Rows("4:40000").Select


    With Selection
        .WrapText = False
    End With
    Range("X4:X40000").Select
    Selection.Replace What:="" & Chr(10) & "", Replacement:=" ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Range("A4").Select
    
        With Application
        .ScreenUpdating = True
        .Calculation = CalcMode
    End With
    
     
 End Sub