none
How to show Microsoft Internet Controls under VBA? RRS feed

  • Question

  • Referring to following link, I would like to know on how to show Microsoft Internet Controls under VBA.

    Does anyone have any suggestions?
    Thanks in advance for any suggestions


    Thanks in advance for any suggestions

    Friday, December 13, 2019 1:00 PM

All replies

  • Hi Sir, 

    From your description, I suppose you want to use Excel VBA to open the IE browser and make some automation actions, right?

    If that is the case, please refer to the following steps:

    1. Open an excel file, Click the Developer tab (if not showing, Click File › Options › Customize Ribbon › add Developer to Main tabs › OK), and click Visual Basic.

    2. As you screenshot said, click Tools › References, and add the reference libraries Microsoft HTML Object Library and Microsoft Internet Controls…

    3. In the Visual Basic Editor (VBE), click View › Project Explorer. Then, right-click VBAProject, and Insert › a new Module…

    4. Now copy & paste the code below into Module1…

    Sub 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 <a> (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 "https://duckduckgo.com"
     
        '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("search_form_input_homepage").Value = _
          Sheets("Sheet1").Range("A2").Value & " in " & Sheets("Sheet1").Range("C1").Value
     
        'click the 'go' button
        objIE.document.getElementById("search_button_homepage").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 <a> element in the collection of objects with class of 'result__a'...
        For Each aEle In objIE.document.getElementsByClassName("result__a")
     
            '...get the href link and print it to the sheet in col C, row y
            result = aEle
            Sheets("Sheet1").Range("C" & y).Value = result
     
            '...get the text within the element and print it to the sheet in col D
            Sheets("Sheet1").Range("D" & y).Value = aEle.innerText
            Debug.Print aEle.innerText
     
            'is it a yellowpages link?
            If InStr(result, "yellowpages.com") > 0 Or InStr(result, "yp.com") > 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
     
        'add up the yellowpages listings
        Sheets("Sheet1").Range("B1").Value = _
          Application.WorksheetFunction.Sum(Sheets("Sheet1").Range("B2:B100"))
     
        'close the browser
        objIE.Quit
     
    'exit our SearchBot subroutine
    End Sub


    5. Press F5 or click the Run Sub button to execute the script. Then it will open IE browser and get the elements from web page.

    More detail information, please check the following links:

    Excel + VBA + IE = web automation

    Automate Internet Explorer (IE) Using VBA 



    • Edited by Zhi Lv Friday, December 13, 2019 4:09 PM
    • Proposed as answer by Zhi Lv Wednesday, December 25, 2019 7:50 AM
    Friday, December 13, 2019 4:07 PM