Macro to check VAT Numeber

Hello all,
I'm quite sure you have a solution for the matter. I wanted to have an excel spreadsheet where I put in column A a list of VAT numbers. Executing the macro should give me in Column B if it is valid or not.

This is quite beyond my ability in macros

I found somenthings but it is not working, this is giving me an Object error:

Please Login or Register  to view this content.

I also found this string that seems very useful:
http://vatid.eu/check/IT/12345678901
It provides if the VAT is valid or not and the Name and Address, if given.
It should be perceft to have those information on the sheet but I really don't know how to get those information.


Other references are the WSDL from the Vies but I really don't know how to work on it.
http://ec.europa.eu/taxation_customs...atService.wsdl

Thanks for help

hi angelooo, welcome to Excelforum, could you please provide Excel file with some VAT numbers to check?

Hi watersev,

thanks for answer. Attaching you some random picked VATs, have not maked a file since yet.

VAT	Valid	Name	Street
SE55600435480
NL801865554B0
GB681845796
DE147330211
IT03085140832
FR54712035401

Thanks

Hi Richard,

thanks for reply. I'm talking about all countries VAT. DE-IT-FR-FI-etc.
I should not check the algorithm but I should check the VAT on a webservice like VIES or other services like that.

Regards

hi angelooo, please check attachment

Sheet "Function": UDF function VAT is used
Sheet "Code": press Run button to use code

Excellent job!

Mega! THXX!!
How can we get also the VIES "Abfrage-Nummer", beside VAT ID etc. into the check???

One more Thing. Where do I know that there is no Virus in this Makro ? (( Do you thing it is safe? Since I have upload it sometimes my Computer goes down. I made a Virus check, but no Virus was found.

Hi All,

I tested watersev's attached xlsm file; but I it does not give always correct check-values.
For example:

HU13030423 >> macro drops INACTIVE
>> VIES(http://ec.europa.eu/taxation_customs/vies/) drops ACTIVE

Would someone so kind to check this issue? For me will be very important to get exact check values.
Thanks in advance!

Hi,

Tested the code and seems to working fine. Just make sure you added the right libraries to your excel. Watersev referenced MSXML wich is optional and needs to be added.

Good Day,

I am replying to this thread as I have relevant issue
1) EU has changed VIES website
The correct one now is *...*taxation_customs/vies/

2) If I use code from other site

[I]#If VBA7 Then
Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal milliseconds As LongPtr) 'MS Office 64 Bit
#Else
Public Declare Sub Sleep Lib "kernel32" (ByVal milliseconds As Long) 'MS Office 32 Bit
#End If

Option Explicit

Public Sub VIES2()
Application.ScreenUpdating = False
Dim IE As Object

'Uruchomienie Internet Explorera i wstrzymanie dalszej akcji az uzyska stan gotowosci
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = False
IE.navigate "VIES website"
Do While IE.ReadyState <> 4: DoEvents: Loop

'Wypelnienie formularza odpowiednimi wartosciami i klikniecie przycisku sprawdzenia
IE.document.getElementById("countryCombobox").Value = "IT"
IE.document.getElementById("number").Value = "01802840023"
IE.document.getElementById("requesterCountryCombobox").Value = "PL"
IE.document.getElementById("requesterNumber").Value = "5242617178"
IE.document.getElementById("submit").Click

sleep (5000) 'or increase to 10000
Dim tbl As Object

Set tbl = IE.document.getElementById("vatResponseFormTable")

Dim ws As Worksheet
Set ws = ActiveWorkbook.Worksheets.Add
ws.Name = "Results"
Dim rng As Range, currentRow As Object, currentColumn As Object, i As Long, outputRow As Long

outputRow = outputRow + 1
Set rng = ws.Range("B" & outputRow)

For Each currentRow In tbl.Rows
For Each currentColumn In currentRow.Cells
rng.Value = currentColumn.outerText
Set rng = rng.Offset(, 1)
i = i + 1
Next currentColumn

outputRow = outputRow + 1
Set rng = rng.Offset(1, -i)
i = 0
Next currentRow
Application.ScreenUpdating = True
End Sub

I would get the following table with info

Tak, numer VAT aktywny [Yes number is valid]

Państwo Członkowskie IT
Numer VAT IT 01802840023
Data zapytania 2019-08-29 08:56
Nazwa FINAGRIT SRL
Adres "VIA MONTE DI PIETA' N 36
13100 VERCELLI VC"
Identyfikator zapytania WAPIAAAAWzcKas_a

As I have thousand of numbers to be checked on a weekly basis I cannot handle response formatted by default.
In order to make sense I need to move some info from columns to rows etc.

What I am trying to achieve is below so to move:
1st item in row to the last column instead(so "F")
2nd item in row to the first column "A"
3rd item in row to column "B"
4rd item in row to column "C"
5th item in row to column "D"
6th item in row to column "E"


Państwo Członkowskie Numer VAT Data zapytania Nazwa Adres Identyfikator
IT IT 01802840023 2019-08-29 08:56 FINAGRIT SRL "VIA MONTE DI PIETA' N 36 13100 VERCELLI VC" WzcKas_a


Status
Tak, numer VAT aktywny


1)How can I transpose the response from VIES site from rows to columns and change sequence ?
2) Once this is done how can I instead of "hardcoded"
client number i.e.
IE.document.getElementById("countryCombobox").Value = "IT"
IE.document.getElementById("number").Value = "01802840023"

input subsequent number from 2 columns in Excel - 1st with country code, second with number to be checked and loop until there is no more data in Excel in those columns ("A" and "B"?)

LM

Hello. Can someone update this code??? Im trying to do it but gives some nerd errors.

Help would be appreciate!!

Hi Guys,

I am new here and was trying to use macro of Watersev. Unfortunately it gives me an error:runtime error, access denied... Anybody know why? I have around 3k VAT IDs to validate and was thinking that macro would be the best solution in this case...