Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
litLIB case study: Online VAT number validation (Read 5461 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
litLIB case study: Online VAT number validation
09.12.09 at 15:13:44
 
litLIB case study: Online VAT number validation
 
litLIB, the power functions pack for Excel (see www.oraxcel.com/projects/litlib) contains a function to validate VAT numbers. The =ValidateVATNumber only checks the format of the VAT number.  
 
How nice it would be to online validate the VAT number directly from within Excel. In this case study I will show you  just how to do that.
 
A few disclaimers as this method cannot be used commercially and no fitness for any purpose is given:
- The method outlined uses a webpage at http://isvat.appspot.com/.  
Please read their disclaimer:  http://isvat.appspot.com/#details
- The original data is retrieved from the European VIES site. Please also read their disclaimer: http://ec.europa.eu/taxation_customs/vies/viesdisc.do
VAT numbers can be checked using the European VIES site at http://ec.europa.eu/taxation_customs/vies/.  
 
Having checked the disclaimers, let's see how to check VAT numbers directly in Excel.
 
We will use the =WebPage function to call the service at http://isvat.appspot.com/. The following formula checks the VAT number: GB987654321
 
=webpage("http://isvat.appspot.com/GB/987654321/")
 
This function will return false as this is not a valid VAT number. Check it using your own VAT number!
 
Obviously you need to parameterise the function to really make use of this. Using simpel string concatenation you can make a batch checker. For example:
 
     A       B                    C
1   NL      123456         =webpage("http://isvat.appspot.com/" & A1 &"/" & B1 & "/")
2   GB     543245         =webpage("http://isvat.appspot.com/" & A2 &"/" & B2 & "/")
3   DE      143545345   =webpage("http://isvat.appspot.com/" & A3 &"/" & B3 & "/")
4   BE      642452435   =webpage("http://isvat.appspot.com/" & A4 &"/" & B4 & "/")
 
The result (I have used false VAT numbers as I could not find any dummy VAT numbers to use for demonstration purposes):
 
     A       B                    C
1   NL      123456         false
2   GB     543245         true
3   DE      143545345   true
4   BE      642452435   false
 
It is as easy as that.
 
Again, do not use this commercially and no fitness for any purpose is given.
Back to top
 
« Last Edit: 09.12.09 at 15:17:56 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1