CODEWITHSUNDEEP
excelvbainternet-explorer
user7151495
user7151495

Reputation: 31

VBA: Search for text in IE, open link, find line copy text, insert in Excel

I'm a complete newbie. Can you guys help me with the following? 1. I have a spreadsheet with productcodes. 2. I have an active Internet Explorer session (logged in) on an index page filled with links that among others contain the said productcodes. 3. For each code, I need it to find the code, open the link. Then find a line containing the string "udsalg" and copy the complete line i.e. "Udsalg 10994" 4. Insert the string next to the productcode. 5. Enter IE and "go back" to index page. 5. Rinse and repeat until all productcodes have been searched.

I have no skill regarding this and hoping for help. I understand basic programming (php etc). I hope somebody can help or point in the right direction. Best regards.

Upvotes: 1

Views: 1157

Answers (1)

Deepak-MSFT
Deepak-MSFT

Reputation: 11335

You can try to refer code example below will give you most of the answers for your above questions. Note that this is not the exact code.

'start a new subroutine called SearchBot
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

You can try to refer the code and try to understand it. Then you can try to modify it based on your requirement. If you have any further questions than you can try to post your sample code. We will try to check it and try to provide you suggestions for it.

Helpful References to learn about IE VBA Automation.

(1) Excel + VBA + IE = web automation

(2) IE (Internet Explorer) Automation using Excel VBA

(3) Automate Internet Explorer (IE) Using VBA

Upvotes: 1

Related Questions