Validator gives me wrong results

[kchriste]

Hi Bodeck I have just tried out the EuropeVatValidator and have the issue that the results are the following: "Web request returned error: Internal Server Error" on all the numbers I tested, and I know that they are valid VAT numbers

Do you have an idea why or has something changed on the server side maybe ? I appreciate your feedback Best regards Kim

[Bodeck]

Hi Kim, Could you please let me know if issue occurs for UK or EU?

[kchriste]

Hi Bodek I think it is both. Here are 2 numbers that I know are valid: DK26177278 Validation message: Web request returned error: Internal Server Error GB112952629 Validation message: A resource with the name in the request can not be found in the API

Cheers Kim

[Bodeck]

Yeah, just found bug for UK - this was easy to fix, I will push updated version in next few minutes. When it comes to EU this seems to be some issue on server site. Hopefully it is only temporary.

[kchriste]

Great, but I still think there is an issue with EU ones as I have tested it both yesterday and today with same result

[Bodeck]

Hi, Problem with UK fixed (you need download updated macro). I need more time to follow up and check what is wrong with EU as there is no info on their website. I will keep this issue open.

[kchriste]

Thanks Bodeck Hopefully there will be a solution to the EU numbers

Cheers Kim

[kchriste]

I can confirm that UK numbers are now working :-)

Kim

[Bodeck]

I have emailed for VIES team for support to check if they are aware and if problem can be fixed on their site. If they do not reply, I will implement some workaround (web scrapping from VIES webpage), but i will take some time to code.

[kchriste]

Thanks, hopefully they will come back to you

[kchriste]

Hi Bodeck I found this small VBA for VIES validation which is actually working. Maybe you can use that to debug your code ?

Option Explicit

If VBA7 And Win64 Then

Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Else

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

End If

Const mTIN_ACTIV As String = "Yes, valid VAT number" Const mTIN_VOID As String = "No, VAT number is void" Const mTIN_BAD_NUMBER As String = "Invalid VAT number" Const mTIN_UNVERIFIED As String = "Unverified"

Sub Test() Dim i As Long Dim objXml As Object Dim Tm As Single

Application.Cursor = xlWait Tm = Timer Set objXml = CreateObject("MSXML2.XMLHTTP")

For i = 1 To 3 'in column A - Country ID 'in column B - VAT number (without country id) 'in column C - verification result Cells(i, 3).Value = Check_VIES(Cells(i, 1).Value, Cells(i, 2).Value, objXml) DoEvents Next i

Application.Cursor = xlDefault MsgBox "Efficiency: " & Round((i - 1) * 60# / (Timer - Tm), 0) & " IDs/min" End Sub

Function Check_VIES(sCountryCode As String, sTINNumber As String, objXml As Object) As Variant 'Author: Artik © 2020

Dim Soap        As String
Dim strResponse As String
Dim strErr      As String
Dim iErrCounter As Long

Soap = "<soapenv:Envelope xmlns:soapenv='http://schemas.xmlsoap.org/soap/envelope/' " & _
       "xmlns:urn='urn:ec.europa.eu:taxud:vies:services:checkVat:types'>"
Soap = Soap & "<soapenv:Header/>"
Soap = Soap & "<soapenv:Body>"
Soap = Soap & "<urn:checkVatApprox>"
Soap = Soap & " <urn:countryCode>" & sCountryCode & "</urn:countryCode>"
Soap = Soap & " <urn:vatNumber>" & sTINNumber & "</urn:vatNumber>"
Soap = Soap & " </urn:checkVatApprox>"
Soap = Soap & "</soapenv:Body>"
Soap = Soap & "</soapenv:Envelope>"

With objXml

OnceAgain: On Error GoTo ErrHndl .Open "POST", "http://ec.europa.eu/taxation_customs/vies/services/checkVatService", False .Send (Soap) While .ReadyState <> 4: DoEvents: Wend strResponse = .ResponseText

    If InStr(1, strResponse, "<faultstring>") > 0 Then
        strErr = UCase(Split(Split(strResponse, "<faultstring>")(1), "</faultstring>")(0))
        Select Case strErr
            Case "INVALID_INPUT"
                Check_VIES = mTIN_BAD_NUMBER
            Case "GLOBAL_MAX_CONCURRENT_REQ", "MS_MAX_CONCURRENT_REQ", "TIMEOUT"
                iErrCounter = iErrCounter + 1
                If iErrCounter > 4 Then
                    Check_VIES = mTIN_UNVERIFIED
                Else
                    If iErrCounter > 0 Then
                    End If
                    Sleep 200
                    GoTo OnceAgain
                End If
            Case "SERVICE_UNAVAILABLE"
                Check_VIES = mTIN_UNVERIFIED
            Case "MS_UNAVAILABLE"
                Check_VIES = mTIN_UNVERIFIED
            Case "FAULT OCCURRED WHILE PROCESSING."
                Check_VIES = mTIN_UNVERIFIED
        End Select
    Else
        On Error Resume Next
        If InStr(1, strResponse, "<ns2:traderName xmlns:xsi=""http://www.w3.org/2001/") > 0 Then
            Check_VIES = CBool(Split(Split(strResponse, "<ns2:valid>")(1), "</ns2:valid>")(0))
            Check_VIES = IIf(Check_VIES, mTIN_ACTIV, mTIN_VOID)
        ElseIf Split(Split(strResponse, "<ns2:traderName>")(1), "</ns2:traderName>")(0) = "---" Then
            Check_VIES = mTIN_BAD_NUMBER
        Else
            Check_VIES = CBool(Split(Split(strResponse, "<ns2:valid>")(1), "</ns2:valid>")(0))
            Check_VIES = IIf(Check_VIES, mTIN_ACTIV, mTIN_VOID)
        End If
    End If
End With
Exit Function

ErrHndl: Check_VIES = mTIN_UNVERIFIED End Function

[Bodeck]

I tested the request in Postman and it still doesn't work. I will change the code to use the same rest API that the VIES website uses. It sends data in JSON format instead of SOAP and XML, so it is more rubust solution.

[kchriste]

Sounds good. Looking forward to that

[Bodeck]

Change has been implemented for EU countries. All should work fine now.