Page 1 of 3 123 LastLast
Results 1 to 10 of 24

Thread: VBA automate IE actions

  1. #1
    New Member
    Join Date
    Jun 2019
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA automate IE actions

    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.

    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 Fluff; Jun 10th, 2019 at 01:25 PM.

  2. #2
    MrExcel MVP ZVI's Avatar
    Join Date
    Apr 2008
    Location
    Sevastopol
    Posts
    3,626
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    3 Thread(s)

    Default Re: VBA automate IE actions

    Hi and welcome to the forum!
    Try inserting this code just before your For Each Text... part
    Code:
        ' --> Wait for the document to load
        While objIE.Document Is Nothing
          DoEvents
        Wend
        ' <-- End of waiting
    Vladimir Zakharov

  3. #3
    New Member
    Join Date
    Jun 2019
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA automate IE actions

    Quote Originally Posted by ZVI View Post
    Hi and welcome to the forum!
    Try inserting this code just before your For Each Text... part
    Code:
        ' --> Wait for the document to load
        While objIE.Document Is Nothing
          DoEvents
        Wend
        ' <-- End of waiting
    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.

  4. #4
    MrExcel MVP ZVI's Avatar
    Join Date
    Apr 2008
    Location
    Sevastopol
    Posts
    3,626
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    3 Thread(s)

    Default Re: VBA automate IE actions

    Quote Originally Posted by Rasmussen View Post
    ...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
    Last edited by ZVI; Jun 10th, 2019 at 04:17 PM.
    Vladimir Zakharov

  5. #5
    New Member
    Join Date
    Jun 2019
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA automate IE actions

    Quote Originally Posted by ZVI View Post
    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=?

  6. #6
    MrExcel MVP ZVI's Avatar
    Join Date
    Apr 2008
    Location
    Sevastopol
    Posts
    3,626
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    3 Thread(s)

    Default Re: VBA automate IE actions

    Quote Originally Posted by Rasmussen View Post
    Thanks for the input. Where in the loop would you place result=?
    See the new code line in Red:
    Code:
        ' --> 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
    Vladimir Zakharov

  7. #7
    MrExcel MVP ZVI's Avatar
    Join Date
    Apr 2008
    Location
    Sevastopol
    Posts
    3,626
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    3 Thread(s)

    Default Re: VBA automate IE actions

    If code doesn't work, to test on myside, what are valid values of A2 and B2 in Sheet1?
    Last edited by ZVI; Jun 10th, 2019 at 05:57 PM.
    Vladimir Zakharov

  8. #8
    New Member
    Join Date
    Jun 2019
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA automate IE actions

    Quote Originally Posted by ZVI View Post
    If code doesn't work, to test on myside, what are valid values of A2 and B2 in Sheet1?
    Thank you, it works perfectly !

  9. #9
    Board Regular Kyle123's Avatar
    Join Date
    Jan 2012
    Location
    Leeds, UK
    Posts
    2,641
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    2 Thread(s)

    Default Re: VBA automate IE actions

    This would be much more efficent:
    Code:
    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:
    Code:
    =IsVatNumberValid(A1,B1)
    It returns True for a valid vat number, or False for invalid, it's also much faster than automating internet explorer
    Last edited by Kyle123; Jun 11th, 2019 at 05:04 AM.

  10. #10
    New Member
    Join Date
    Jun 2019
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA automate IE actions

    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?

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •