Handling of VBA XML response

seisbye

New Member
Joined
Oct 27, 2015
Messages
12
I have created the below code..

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?
 
Last edited:

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

jkpieterse

Well-known Member
Joined
Dec 3, 2007
Messages
1,080
The technical info about this webservice is here: VIES

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

Code:
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

New Member
Joined
Oct 27, 2015
Messages
12
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

Well-known Member
Joined
Dec 3, 2007
Messages
1,080

ADVERTISEMENT

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

Seems the forum software wrecks my VBA here.
 

jkpieterse

Well-known Member
Joined
Dec 3, 2007
Messages
1,080
Here is another attempt at posting the code:

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

New Member
Joined
Oct 27, 2015
Messages
12

ADVERTISEMENT

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

Well-known Member
Joined
Dec 3, 2007
Messages
1,080
Yes. to get it properly posted I replaced the less than signs with lt; and the greater than with gt;
 

manka

New Member
Joined
Mar 6, 2017
Messages
3
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

New Member
Joined
Oct 27, 2015
Messages
12
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

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,756
Messages
5,597,933
Members
414,193
Latest member
bb60

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top