send and receive data with a web site

[blatta]

I am looking at how to send data and receive data from a web-site.

the project I have is as follow:

I have an excel file that contains the VAT number. For each VAT number I need to access the "http://ec.europa.eu/taxation_customs/vies/" web site enter the country code and VAT number (that are in excel) and hit verify to see if the code is valid.

I know VBA but I have no experience with the code for sending data to the website, hit verify and retrieve the results.

Any help greatly appreciated.
Alain

 

[circledchicken]

Hi Alain,

The following resource is great:
http://vba-corner.livejournal.com/4623.html

Try the following (thanks to the above):

1. Add a reference to the "Microsoft Internet Controls" library (in the VBA editor go to 'Tools - References...' and tick 'Microsoft Internet Controls'.
2. Set up your worksheet as follows:
   • In cell A2 enter the page url - "http://ec.europa.eu/taxation_customs/vies/"
   • In cells B2 and below enter the member state index numbers you want to search for (e.g. Austria is 1, Belgium is 2, Slovakia is 27 etc)
   • In cells C2 and below enter the associated VAT numbers you want to search for (ensure the cell format for the data in Column C is 'Text')
3. Enter the following code (referencing the appropriate sheet if necessary):
   

   Option Explicit
   
   'verify VAT numbers
   Sub verify_VAT()
   
   Dim rCell As Range
   Dim page_url As String
   Dim myIE As SHDocVw.InternetExplorer
       
       'create new IE instance
       Set myIE = New SHDocVw.InternetExplorer
       'make IE window visible
       myIE.Visible = True
       'set page url
       page_url = Range("A2").Value
                    
       'loop through VAT data
       For Each rCell In Range(Range("B2"), Range("B2").End(xlDown))
           
           'load page
           Call LoadWebPage(myIE, page_url)
                 
           With myIE.Document
               'enter memberstate ('ms' is the name of the Member State select box on the webpage)
               .getElementsByName("ms").Item(0).selectedIndex = rCell.Value
               'enter vat number ('vat' is the name of the VAT Number input box on the webpage)
               .getElementsByName("vat").Item(0).Value = rCell.Offset(0, 1).Value
               'submit ('BtnSubmitVat' is the name of the Verify button on the webpage)
               .getElementsByName("BtnSubmitVat").Item(0).Click
               'wait till the form has been submitted
               Call ie_wait(myIE)
               
               'check the response to see if the number is valid
               If InStr(1, .body.innerHTML, "Yes, valid VAT number") Then
                   rCell.Offset(0, 2).Value = "Yes"
                   Else
                   rCell.Offset(0, 2).Value = "No"
               End If
           End With
           
       Next rCell
       
   'clean up
   myIE.Quit 'close IE
   Set myIE = Nothing
          
   End Sub
   
   'load web page
   Function LoadWebPage(i_IE As SHDocVw.InternetExplorer, _
                        i_URL As String)
       With i_IE
           .Navigate i_URL 'open page
           Call ie_wait(i_IE) 'wait till it is loaded
       End With
   End Function
           
   'wait until IE finishes loading the page
   Function ie_wait(i_IE As SHDocVw.InternetExplorer)
       Do While i_IE.Busy
           Application.Wait (Now + TimeValue("00:00:01"))
       Loop
   End Function

This 'verify_VAT' procedure should return Yes/No in column D depending on the validity of the VAT numbers.

 

[blatta]

Thanks you so much. this is exactly what I was looking for ))

Best Regards,
Alain

 

[figsy]

Hi,

I found this old thread that guides through how to look up series of VAT numbers on website. Some bits of the code needed updating, as the website has updated the names of the form submission field elements (eg: from "ms" to "memberState").

The code works well, but I have two problems:
1. The check where searching for text "Yes, valid VAT number" on the returned HTML page (in body) does not work.

2. In addition to checking the VAT number is valid, I want to return the data from the HTML page and put it in the workbook in appropriate column. I have tried "getElementsByTagName" and "getElementsByClass", but am unable to capture the data needed.


The Excel worksheet looks like this:

Website	Country	VAT No	Valid	Member State	VAT Number	Date/Time	Name	Address	Consultation No
http://ec.europa.eu/taxation_customs/vies/	IE	9839849W	No
	IE	9590828H	No
	IE	8271023V	No
	GB	2464270	No

<tbody>
</tbody>

Appreciate any help on above. Problem further documented in code extract.

  Option Explicit


'verify VAT numbers
Sub verify_VAT()


Dim rCell As Range
Dim page_url As String
Dim myIE As SHDocVw.InternetExplorer






    
    'create new IE instance
    Set myIE = New SHDocVw.InternetExplorer
    
    'make IE window visible
    myIE.Visible = True
    
    'set page url
    page_url = Range("A2").Value
                 
    'loop through VAT data
    For Each rCell In Range(Range("B2"), Range("B2").End(xlDown))
        
        'load page
        Call LoadWebPage(myIE, page_url)
              
        With myIE.Document
            'enter memberstate ('memberStateCode' is the name of the Member State select box on the webpage)
            .getElementsByName("memberStateCode").Item(0).Value = rCell.Value
            'enter vat number ('number' is the name of the VAT Number input box on the webpage)
            .getElementsByName("number").Item(0).Value = rCell.Offset(0, 1).Value
            'submit ('check' is the name of the Verify button on the webpage)
            .getElementsByName("check").Item(0).Click
            'wait till the form has been submitted
            Call ie_wait(myIE)
            
            
            
            'check the response to see if the number is valid
            ' check for match of "Yes, valid VAT number"
            
            If InStr(1, .body.innerHTML, "Yes,") Then
                rCell.Offset(0, 2).Value = "Yes"
                ' PROBLEM 1
                ' ********* THIS DOES NOT FIND the text "Yes, valid VAT number" in the body. Skips straight to Else *************
                
                ' PROBLEM 2
                ' I want to get the following data from the web page (see commented section below) and enter into excel rows also:
                ' "Yes, valid VAT Number" (in row 1)
                ' "IE" (the Member State in row 2 / col 2)
                ' "IE 9580930P" (the VAT number in row 3 / col 2)
                ' "2018/01/13 21:55:37" (timestamp in row 4 / col 2)
                ' "MCR OUTSOURCING LIMITED" (Name in row 5 / col 2)
                ' "ROY MCCARTHY - HEAD OF FINANCE ,1-3 THE CAPEL BUILDING ,MARY'S ABBEY ,DUBLIN 7" (Address in row 6 / Col 2)
                ' "" (Consultation Number in row 7 / Col 2)
                
                
                
                
                Else
                rCell.Offset(0, 2).Value = "No"
            End If
    
    ' THIS IS PART OF THE HTML PAGE THAT IS RETURNED WHERE I WANT TO TAKE THE DATA FROM:
    '       
    '           
    '               
    '           
    '           
    '               
    '           
    '           
    '               
    '               
    '               
    '           
    '           
    '               
    '               
    '           
    '           
    '               
    '               
    '           
    '            
    '                
    '                
    '           
    '            
    '                
    '                
    '            
    '            
    '                
    '                
    '            
    '       [TABLE]
<tbody>[TR]
[TD="class: labelLeft, colspan: 3"][B]Yes, valid VAT number[/B][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD="class: labelStyle"]Member State[/TD]
[TD]IE[/TD]
[TD="class: errorFormStyle"][/TD]
[/TR]
[TR]
[TD="class: labelStyle"]VAT Number[/TD]
[TD]IE 9580930P[/TD]
[/TR]
[TR]
[TD="class: labelStyle"]Date when request received[/TD]
[TD]2018/01/13 21:55:37[/TD]
[/TR]
[TR]
[TD="class: labelStyle"]Name[/TD]
[TD]MCR OUTSOURCING LIMITED[/TD]
[/TR]
[TR]
[TD="class: labelStyle"]Address[/TD]
[TD]ROY MCCARTHY - HEAD OF FINANCE ,1-3 THE CAPEL BUILDING ,MARY'S ABBEY ,DUBLIN 7[/TD]
[/TR]
[TR]
[TD="class: labelStyle"]Consultation Number[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

    '    

    '    [URL="https://www.mrexcel.com/forum/vatRequest.html"]Back[/URL]

    ' 
    
    
            
        End With
        
    Next rCell
    
'clean up
myIE.Quit 'close IE
Set myIE = Nothing
       
End Sub


'load web page
Function LoadWebPage(i_IE As SHDocVw.InternetExplorer, i_URL As String)
    With i_IE
        .Navigate i_URL 'open page
        Call ie_wait(i_IE) 'wait till it is loaded
    End With
End Function
        
'wait until IE finishes loading the page
Function ie_wait(i_IE As SHDocVw.InternetExplorer)
    Do While i_IE.Busy
        Application.Wait (Now + TimeValue("00:00:01"))
    Loop
End Function

The table in HTML reads like this:

' THIS IS PART OF THE HTML PAGE THAT IS RETURNED WHERE I WANT TO TAKE THE DATA FROM:
'
'
'
'
'
'
'
'
'
'
'
'
'
'
'
'
'
'
'
'
'
'
'
'
'
'
'
'
'
'
'
'
'

Yes, valid VAT number
Member State	IE
VAT Number	IE 9580930P
Date when request received	2018/01/13 21:55:37
Name	MCR OUTSOURCING LIMITED
Address	ROY MCCARTHY - HEAD OF FINANCE ,1-3 THE CAPEL BUILDING ,MARY'S ABBEY ,DUBLIN 7
Consultation Number

<tbody>
</tbody>

'

' Back

'

 

[John_w]

The code works well, but I have two problems:
1. The check where searching for text "Yes, valid VAT number" on the returned HTML page (in body) does not work.

2. In addition to checking the VAT number is valid, I want to return the data from the HTML page and put it in the workbook in appropriate column. I have tried "getElementsByTagName" and "getElementsByClass", but am unable to capture the data needed.

If you look at the entire response HTML (myIE.Document.body.outerHTML), it is just "< body>< /body>". I have seen this problem before with IE11. The actual result page is in a hidden IE window, which is opened when the form is submitted (or a button .Click method is called). The simple solution is to hide the IE window before the .Click (myIE.visible = False) and show it after the wait, and then the hidden IE window isn't opened.

The VAT details are then in a HTML table with id="vatResponseFormTable".

 

[javierc]

If you look at the entire response HTML (myIE.Document.body.outerHTML), it is just "< body>< /body>". I have seen this problem before with IE11. The actual result page is in a hidden IE window, which is opened when the form is submitted (or a button .Click method is called). The simple solution is to hide the IE window before the .Click (myIE.visible = False) and show it after the wait, and then the hidden IE window isn't opened.

The VAT details are then in a HTML table with id="vatResponseFormTable".

Could you please put the complete example to work?

Thanks in advance

 

[javierc]

This is the code that work fine for me, the problem was reference for MSXML60, withe MSXML30 all ok. I not use boolean filed becose is this case is better for me string for get the posible error:

Function EsPaísNIFCorrecto(ByVal PaísDosLetras As String, ByVal NIF As String) As String
   'PaisDosLetras pasar ES para España
   '
    Dim sURL As String
    Dim sEnv As String
    Dim xmlhttp As New MSXML2.XMLHTTP30
    Dim xmlDoc As New MSXML2.DOMDocument30 'DOMDocument
    Dim sCountryCode As String
    Dim sVATNo As String
    
    If PaísDosLetras = "" Then
       EsPaísNIFCorrecto = "Dos dígitos país vacíos"
    Else
       If NIF = "" Then
           EsPaísNIFCorrecto = "NIF vacío"
       Else
           If Not PaísValido(PaísDosLetras) Then
               EsPaísNIFCorrecto = "Las dos letras del país no son válidas"
           Else
               sURL = "http://ec.europa.eu/taxation_customs/vies/services/checkVatService"
               sCountryCode = PaísDosLetras
               sVATNo = NIF
               sEnv = "<soapenv:envelope xmlns:soapenv="" http:="" schemas.xmlsoap.org="" soap="" envelope="" ""="" xmlns:urn="" urn:ec.europa.eu:taxud:vies:services:checkvat:types""="">"
               sEnv = sEnv & "<soapenv:header>"
               sEnv = sEnv & "<soapenv:body>"
               sEnv = sEnv & "<urn:checkvat>"
               sEnv = sEnv & "<urn:countrycode>" & sCountryCode & "</urn:countrycode>"
               sEnv = sEnv & "<urn:vatnumber>" & sVATNo & "</urn:vatnumber>"
               sEnv = sEnv & "</urn:checkvat>"
               sEnv = sEnv & "</soapenv:body>"
               sEnv = sEnv & "</soapenv:header></soapenv:envelope>"
               

                  With xmlhttp
                       .Open "POST", sURL, False
                       .setRequestHeader "Content-Type", "text/xml;"
                       .send sEnv
                       Set xmlDoc = New MSXML2.DOMDocument30
                       xmlDoc.LoadXML .responseText
                       Dim Resultado As String
                       '
                       'this work only for me with reference Microsoft XMLD 3.0 con XMLHTTP30 y DOMDocument30, not with 6.0 versions
                       If LCase(xmlDoc.getElementsByTagName("valid").Item(0).Text) = "true" Then
                           EsPaísNIFCorrecto = "Sí"
                       Else
                          EsPaísNIFCorrecto = "No"

                   End With

           End If
       End If
    End If
End Function