Hi,
I found this VBA on this forum, but I need some changes applied to it, that I hope you guys can help me with.
This VBA is automated to give me a result on VAT numbers.
There are new requirements to EU regulations, so I have to add another function to the VBA. If you go to EU Validation site, you can see there is 2 options, the Member State section and Requester Member State section. In the current VBA it is automated to fill in the Member State section, and return with result from this.
Now we need to add our VAT number in the Requester section and return with a Consultation Number in the worksheet.
For a example you can type SE556042722001 (Completely random VAT) to see Consultation number example.
Can anyone help me with this matter? Thanks!
I found this VBA on this forum, but I need some changes applied to it, that I hope you guys can help me with.
VBA Code:
Public Function IsVatNumberValid(ByVal countryCode As String, ByVal vatNumber As String) As Variant
Static req As Object
If req Is Nothing Then Set req = CreateObject("MSXML2.XMLHTTP")
On Error GoTo handler
With req
.Open "POST", "http://ec.europa.eu/taxation_customs/vies/services/checkVatService", False
.send "<?xml version=""1.0"" encoding=""UTF-8"" standalone=""no""?><SOAP-ENV:Envelope xmlns:SOAP-ENV=""http://schemas.xmlsoap.org/soap/envelope/"" xmlns:tns1=""urn:ec.europa.eu:taxud:vies:services:checkVat:types"" xmlns:soapenc=""http://schemas.xmlsoap.org/soap/encoding/"" xmlns:impl=""urn:ec.europa.eu:taxud:vies:services:checkVat"" xmlns:apachesoap=""http://xml.apache.org/xml-soap"" xmlns:wsdl=""http://schemas.xmlsoap.org/wsdl/"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" xmlns:wsdlsoap=""http://schemas.xmlsoap.org/wsdl/soap/"" xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" ><SOAP-ENV:Body><tns1:checkVat xmlns:tns1=""urn:ec.europa.eu:taxud:vies:services:checkVat:types""><tns1:countryCode>" & countryCode & "</tns1:countryCode><tns1:vatNumber>" & vatNumber & "</tns1:vatNumber></tns1:checkVat></SOAP-ENV:Body></SOAP-ENV:Envelope>"
'There's a server error or an error with the input
If InStr(.responseText, "faultstring") > 0 Then
IsVatNumberValid = Split(Split(.responseText, "faultstring>")(1), "<")(0)
Exit Function
End If
'There are no validation errors, read whether it is valid
If InStr(.responseText, "valid") > 0 Then
If Split(Split(.responseText, "<valid>")(1), "<")(0) = "true" Then
IsVatNumberValid = "Ja, gyldigt momsregistreringsnummer"
Else
IsVatNumberValid = "Nej, momsnummeret er ugyldigt for grænseoverskridende transaktioner inden for EU."
End If
Exit Function
End If
End With
'If we've got this far there's something else wrong or an error has been raised
handler:
IsVatNumberValid = CVErr(18)
End Function
This VBA is automated to give me a result on VAT numbers.
There are new requirements to EU regulations, so I have to add another function to the VBA. If you go to EU Validation site, you can see there is 2 options, the Member State section and Requester Member State section. In the current VBA it is automated to fill in the Member State section, and return with result from this.
Now we need to add our VAT number in the Requester section and return with a Consultation Number in the worksheet.
For a example you can type SE556042722001 (Completely random VAT) to see Consultation number example.
Can anyone help me with this matter? Thanks!