# VBA automate IE actions



## Rasmussen (Jun 10, 2019)

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.


```
'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.


----------



## ZVI (Jun 10, 2019)

Hi and welcome to the forum!
Try inserting this code just before your For Each Text... part

```
' --> Wait for the document to load
    While objIE.Document Is Nothing
      DoEvents
    Wend
    ' <-- End of waiting
```


----------



## Rasmussen (Jun 10, 2019)

ZVI said:


> Hi and welcome to the forum!
> Try inserting this code just before your For Each Text... part
> 
> ```
> ...



Hello, thanks.
It still gets stuck at IE window, it does the search and gets result in IE, but it doesnt transfer result to excel.


----------



## ZVI (Jun 10, 2019)

Rasmussen said:


> ...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?...


My assumption was based on the fact the code is working in debugging mode.
But seems this line of the code is missing: result = Text.innerText inside the For-Each loop.
In the posted code the result variable was not populate.
I'd also sugest this declaration in the code: Dim Text As Variant or Dim Text As Object


----------



## Rasmussen (Jun 10, 2019)

ZVI said:


> My assumption was based on the fact the code is working in debugging mode.
> But seems this line of the code is missing: result = Text.innerText inside the For-Each loop.
> In the posted code the result variable was not populate.
> I'd also sugest this declaration in the code: Dim Text As Variant or Dim Text As Object



Thanks for the input. Where in the loop would you place result=?


----------



## ZVI (Jun 10, 2019)

Rasmussen said:


> Thanks for the input. Where in the loop would you place result=?


See the new code line in Red:

```
' --> Wait for the document to load
    While objIE.Document Is Nothing
      DoEvents
    Wend
    ' <-- End of waiting
   
    'for each  element in the collection of objects with class of 'result__a'...
    For Each Text In objIE.Document.getElementsByClassName("labelLeft")
       
        result = Text.innerText ' <-- New code line is here
       
        '...get the text within the element and print it to the sheet in col D
```


----------



## ZVI (Jun 10, 2019)

If code doesn't work, to test on myside, what are valid values of A2 and B2 in Sheet1?


----------



## Rasmussen (Jun 11, 2019)

ZVI said:


> If code doesn't work, to test on myside, what are valid values of A2 and B2 in Sheet1?


Thank you, it works perfectly !


----------



## Kyle123 (Jun 11, 2019)

This would be much more efficent:

```
Public Function IsVatNumberValid(ByVal countryCode As String, ByVal vatNumber As String) As Boolean

    Static req As Object
    If req Is Nothing Then Set req = CreateObject("MSXML2.XMLHTTP")
    
    With req
        .Open "POST", "http://ec.europa.eu/taxation_customs/vies/vatResponse.html", False
        .setrequestheader "Content-Type", "application/x-www-form-urlencoded"
        .send "memberStateCode=" & countryCode & "&Number=" & vatNumber & "&Action=check&check=verify"
        IsVatNumberValid = InStr(result, "Nej, momsnummeret er ugyldigt") > 0 Or InStr(result, "Nej") >
    End With
    

End Function
```

You can also use it as a worksheet function, like this:

```
=IsVatNumberValid(A1,B1)
```

It returns True for a valid vat number, or False for invalid, it's also much faster than automating internet explorer


----------



## Rasmussen (Jun 11, 2019)

One quick question. It keeps looping back to result = Text.innerText when it reaches Next. How can I remove this, so it only show the result once and not keeps looping?


----------



## Rasmussen (Jun 10, 2019)

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.


```
'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.


----------



## Rasmussen (Jun 11, 2019)

Kyle123 said:


> This would be much more efficent:
> 
> ```
> Public Function IsVatNumberValid(ByVal countryCode As String, ByVal vatNumber As String) As Boolean
> ...



I'm getting syntax error expected expression on this line
sVatNumberValid = InStr(result, "Nej, momsnummeret er ugyldigt") > 0 Or InStr(result, "Nej")>


----------



## Rasmussen (Jun 11, 2019)

Rasmussen said:


> I'm getting syntax error expected expression on this line
> sVatNumberValid = InStr(result, "Nej, momsnummeret er ugyldigt") > 0 Or InStr(result, "Nej")>



Sorry, its syntax error, when trying to insert the function to sheet.


----------



## Kyle123 (Jun 11, 2019)

Sorry, I'd copied and pasted yours without altering. Try this:

```
Public Function IsVatNumberValid(ByVal countryCode As String, ByVal vatNumber As String) As Boolean

    Static req As Object
    If req Is Nothing Then Set req = CreateObject("MSXML2.XMLHTTP")
    
    With req
        .Open "POST", "http://ec.europa.eu/taxation_customs/vies/vatResponse.html", False
        .setrequestheader "Content-Type", "application/x-www-form-urlencoded"
        .send "memberStateCode=" & countryCode & "&Number=" & vatNumber & "&Action=check&check=verify"
        IsVatNumberValid = InStr(.responsetext, "Nej, momsnummeret er ugyldigt") > 0 Or InStr(.responsetext, "Nej") > 0
    End With
    

End Function
```


----------



## Rasmussen (Jun 11, 2019)

Kyle123 said:


> Sorry, I'd copied and pasted yours without altering. Try this:
> 
> ```
> Public Function IsVatNumberValid(ByVal countryCode As String, ByVal vatNumber As String) As Boolean
> ...



Thanks. 

I keep getting False result on valid numbers?


----------



## Kyle123 (Jun 11, 2019)

Could you provide an example?


----------



## Kyle123 (Jun 11, 2019)

I can't test the above as I don't see the website in Danish, try this though, it should be language agnostic:

```
Public Function IsVatNumberValid(ByVal countryCode As String, ByVal vatNumber As String) As Boolean

    Static req As Object
    If req Is Nothing Then Set req = CreateObject("MSXML2.XMLHTTP")
    
    With req
        .Open "POST", "http://ec.europa.eu/taxation_customs/vies/vatResponse.html", False
        .setrequestheader "Content-Type", "application/x-www-form-urlencoded"
        .send "memberStateCode=" & countryCode & "&Number=" & vatNumber & "&Action=check&check=verify"
        IsVatNumberValid = InStr(.responsetext, "class=""validStyle""")
    End With
    

End Function
```


----------



## Rasmussen (Jun 11, 2019)

Kyle123 said:


> I can't test the above as I don't see the website in Danish, try this though, it should be language agnostic:
> 
> ```
> Public Function IsVatNumberValid(ByVal countryCode As String, ByVal vatNumber As String) As Boolean
> ...



Still getting false result. I tried for example DK 25099400, which is valid if I look it up on VIES, but result is false in excel.


----------



## Rasmussen (Jun 11, 2019)

Ah. I see that it's because the VIES servers doesnt work for this country code at the moment.

But this could also be a problem, if I want to check, and the server is unavailable, the result will be false, even though it's valid?

Is it possible to change the code, so the result in excel is the innertext from VIES result?


----------



## Kyle123 (Jun 11, 2019)

Yes, but specifically whuch part of the result do you want?


----------



## Rasmussen (Jun 11, 2019)

Kyle123 said:


> Yes, but specifically whuch part of the result do you want?



The class "labelLeft"


----------



## Rasmussen (Jun 10, 2019)

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.


```
'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.


----------



## Kyle123 (Jun 11, 2019)

OK, here's a better approach, don't use webscraping when the site offers a webservice. This code will return true when valid, false when invalid, if the input is invalid, the error description from the server is returned. If there is an unhandled error, a generic Excel error is listed.

For your DK example above, a TIMEOUT error is returned (as from the website).

```
Public Function IsVatNumberValid(ByVal countryCode As String, ByVal vatNumber As String) As Variant

    Static req As Object
    If req Is Nothing Then Set req = CreateObject("MSXML2.XMLHTTP")
    
    On Error GoTo handler
    
    With req
        .Open "POST", "http://ec.europa.eu/taxation_customs/vies/services/checkVatService", False
        .send "<?xml version=""1.0"" encoding=""UTF-8"" standalone=""no""?><SOAP-ENV:Envelope xmlns:SOAP-ENV=""http://schemas.xmlsoap.org/soap/envelope/"" xmlns:tns1=""urn:ec.europa.eu:taxud:vies:services:checkVat:types"" xmlns:soapenc=""http://schemas.xmlsoap.org/soap/encoding/"" xmlns:impl=""urn:ec.europa.eu:taxud:vies:services:checkVat"" xmlns:apachesoap=""http://xml.apache.org/xml-soap"" xmlns:wsdl=""http://schemas.xmlsoap.org/wsdl/"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" xmlns:wsdlsoap=""http://schemas.xmlsoap.org/wsdl/soap/"" xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" ><SOAP-ENV:Body><tns1:checkVat xmlns:tns1=""urn:ec.europa.eu:taxud:vies:services:checkVat:types""><tns1:countryCode>" & countryCode & "</tns1:countryCode><tns1:vatNumber>" & vatNumber & "</tns1:vatNumber></tns1:checkVat></SOAP-ENV:Body></SOAP-ENV:Envelope>"
        
        
        'There's a server error or an error with the input
        If InStr(.responsetext, "faultstring") > 0 Then
            IsVatNumberValid = Split(Split(.responsetext, "faultstring>")(1), "<")(0)
            Exit Function
        End If
        
        'There are no validation errors, read whether it is valid
        If InStr(.responsetext, "valid") > 0 Then
            IsVatNumberValid = Split(Split(.responsetext, "<valid>")(1), "<")(0)
            Exit Function
        End If
        
    End With
    
'If we've got this far there's something else wrong or an error has been raised
handler:
    IsVatNumberValid = CVErr(18)
    
End Function
```


----------



## Rasmussen (Jun 11, 2019)

Kyle123 said:


> OK, here's a better approach, don't use webscraping when the site offers a webservice. This code will return true when valid, false when invalid, if the input is invalid, the error description from the server is returned. If there is an unhandled error, a generic Excel error is listed.
> 
> For your DK example above, a TIMEOUT error is returned (as from the website).
> 
> ...



Works great, thank you.

Is it possible to change the True and False result text to whatever text I want?


----------



## Kyle123 (Jun 11, 2019)

Yes, like this:

```
Public Function IsVatNumberValid(ByVal countryCode As String, ByVal vatNumber As String) As Variant

    Static req As Object
    If req Is Nothing Then Set req = CreateObject("MSXML2.XMLHTTP")
    
    On Error GoTo handler
    
    With req
        .Open "POST", "http://ec.europa.eu/taxation_customs/vies/services/checkVatService", False
        .send "<?xml version=""1.0"" encoding=""UTF-8"" standalone=""no""?><SOAP-ENV:Envelope xmlns:SOAP-ENV=""http://schemas.xmlsoap.org/soap/envelope/"" xmlns:tns1=""urn:ec.europa.eu:taxud:vies:services:checkVat:types"" xmlns:soapenc=""http://schemas.xmlsoap.org/soap/encoding/"" xmlns:impl=""urn:ec.europa.eu:taxud:vies:services:checkVat"" xmlns:apachesoap=""http://xml.apache.org/xml-soap"" xmlns:wsdl=""http://schemas.xmlsoap.org/wsdl/"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" xmlns:wsdlsoap=""http://schemas.xmlsoap.org/wsdl/soap/"" xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" ><SOAP-ENV:Body><tns1:checkVat xmlns:tns1=""urn:ec.europa.eu:taxud:vies:services:checkVat:types""><tns1:countryCode>" & countryCode & "</tns1:countryCode><tns1:vatNumber>" & vatNumber & "</tns1:vatNumber></tns1:checkVat></SOAP-ENV:Body></SOAP-ENV:Envelope>"
        
        
        'There's a server error or an error with the input
        If InStr(.responsetext, "faultstring") > 0 Then
            IsVatNumberValid = Split(Split(.responsetext, "faultstring>")(1), "<")(0)
            Exit Function
        End If
        
        'There are no validation errors, read whether it is valid
        If InStr(.responsetext, "valid") > 0 Then
            If Split(Split(.responsetext, "<valid>")(1), "<")(0) = "true" Then
                IsVatNumberValid = "Yes, it's a valid code"
            Else
                IsVatNumberValid = "No, it's not a valid code"
            End If
            Exit Function
        End If
        
    End With
    
'If we've got this far there's something else wrong or an error has been raised
handler:
    IsVatNumberValid = CVErr(18)
    
End Function
```


----------



## Rasmussen (Jun 11, 2019)

Kyle123 said:


> Yes, like this:
> 
> ```
> Public Function IsVatNumberValid(ByVal countryCode As String, ByVal vatNumber As String) As Variant
> ...



This works perfectly, thank you very much.

Do you know if it's possible to add a print and save to PDF with Public function?


----------

