Hello,
I found this VBA from automatetheweb and I found it very useful to my daily work.
I want to automate the search we daily do with VAT numbers from the EU vies validation page.
So the VBA is suppose to go to the website http://ec.europa.eu/taxation_customs/vies/, then insert A2 in country code and B2 in the text field, then hit submit to get result.
I then need it to show the result in C2, wether it's valid or not.
My problem is, that when I use debug (F8) function in the VBA, there is no problems, but when I then try to run the macro from developer field, it gets stuck at the result page, it doesnt list the result in C2?
The code is following.
Anyone who can help me out? Thanks.
I found this VBA from automatetheweb and I found it very useful to my daily work.
I want to automate the search we daily do with VAT numbers from the EU vies validation page.
So the VBA is suppose to go to the website http://ec.europa.eu/taxation_customs/vies/, then insert A2 in country code and B2 in the text field, then hit submit to get result.
I then need it to show the result in C2, wether it's valid or not.
My problem is, that when I use debug (F8) function in the VBA, there is no problems, but when I then try to run the macro from developer field, it gets stuck at the result page, it doesnt list the result in C2?
The code is following.
Code:
'start a new subroutine called SearchBotSub SearchBot()
'dimension (declare or set aside memory for) our variables
Dim objIE As InternetExplorer 'special object variable representing the IE browser
Dim aEle As HTMLLinkElement 'special object variable for an (link) element
Dim y As Integer 'integer variable we'll use as a counter
Dim result As String 'string variable that will hold our result link
'initiating a new instance of Internet Explorer and asigning it to objIE
Set objIE = New InternetExplorer
'make IE browser visible (False would allow IE to run in the background)
objIE.Visible = True
'navigate IE to this web page (a pretty neat search engine really)
objIE.navigate "http://ec.europa.eu/taxation_customs/vies/"
'wait here a few seconds while the browser is busy
Do While objIE.Busy = True Or objIE.readyState <> 4: DoEvents: Loop
'in the search box put cell "A2" value, the word "in" and cell "C1" value
objIE.document.getElementById("countryCombobox").Value = _
Sheets("Sheet1").Range("A2").Value
objIE.document.getElementById("number").Value = _
Sheets("Sheet1").Range("B2").Value
'click the 'go' button
objIE.document.getElementById("submit").Click
'wait again for the browser
Do While objIE.Busy = True Or objIE.readyState <> 4: DoEvents: Loop
'the first search result will go in row 2
y = 2
'for each element in the collection of objects with class of 'result__a'...
For Each Text In objIE.document.getElementsByClassName("labelLeft")
'...get the text within the element and print it to the sheet in col D
Sheets("Sheet1").Range("D" & y).Value = Text.innerText
Debug.Print Text.innerText
'is it a yellowpages link?
If InStr(result, "Nej, momsnummeret er ugyldigt") > 0 Or InStr(result, "Nej") > 0 Then
'make the result red
Sheets("Sheet1").Range("C" & y).Interior.ColorIndex = 3
'place a 1 to the left
Sheets("Sheet1").Range("B" & y).Value = 1
End If
'increment our row counter, so the next result goes below
y = y + 1
'repeat times the # of ele's we have in the collection
Next
'close the browser
objIE.Quit
'exit our SearchBot subroutine
End Sub
Anyone who can help me out? Thanks.
Last edited by a moderator: