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
))
