VAT validator VIES website lookup

[roaze2000]

Hi all,

I have found this code here a few years ago, and for some reason it is not working "very well" anymore.
I am not sure if it is the website returning bad data, or something else:

Public Function VAT(rng As Range) As String
With CreateObject("MSXML2.XMLHTTP")
.Open "GET", "http://vatid.eu/check/" & Left(rng, 2) & "/" & Right(rng, Len(rng) - 2)
.send
Do: DoEvents: Loop Until .ReadyState = 4
VAT = Split(Split(.responsetext, "<valid>")(1), "</valid>")(0)
.abort
End With
End Function



Sub RUNVATCHECK()
Dim lrow As Long, data, obj As Object, i As Long, country, VATnum, webreply As String

lrow = Cells(Rows.Count, 1).End(xlUp).Row
If lrow = 1 Then Exit Sub

If Range("a1") <> "VAT" Then Exit Sub

data = Range("a1:d" & lrow)

Set obj = CreateObject("MSXML2.XMLHTTP")

For i = 2 To lrow
If Len(data(i, 1)) > 2 Then
country = Left(data(i, 1), 2)
VATnum = Right(data(i, 1), Len(data(i, 1)) - 2)
obj.Open "GET", "http://vatid.eu/check/" & country & "/" & VATnum & "/" & country & "/" & VATnum
obj.send
Do: DoEvents: Loop Until obj.ReadyState = 4
webreply = obj.responsetext
If InStr(webreply, "<error>") > 0 Then
data(i, 2) = False
Else
data(i, 2) = Split(Split(webreply, "<valid>")(1), "</valid>")(0)
data(i, 3) = Split(Split(webreply, "<name><![CDATA[")(1), "]]></name>")(0)
data(i, 4) = Split(Split(webreply, "<address><![CDATA[")(1), "]]></address>")(0)
End If
End If
Next

obj.abort

Range("a1:d" & lrow) = data

End Sub

This code is excellent when it works, but now it seems to give me "subscript out of range" on this line:
Else
data(i, 2) = Split(Split(webreply, "<valid>")(1), "</valid>")(0)

Can anyone tell me why it does not work?

thanks in advance

 

[roaze2000]

So basically:

You have the headers: VAT number, False, Name, Street on A1:D1
On A2, copy down a couple of EU VAT Numbers: SE556946032101 and SE556944886201 for example.

Normally, one would go to VIES and key in the number manually, one by one....I have about 5000, and a 100+ new ones every week.
This worked like a charm, but it is very unstable now.

As I said, it might be the website/database causing the issue. Any help or improvement on the code would be highly appreciated.

Thanks in advance