CODEWITHSUNDEEP
excelvbaweb-scraping
Vishal
Vishal

Reputation: 119

When the search button is clicked using vba the text entered in search box is not seen by web page

I have written vba code for entering manufacturer part number in search box of below website and clicking on search icon. It is able enter manufacturer part number in search box and click on search icon, but when "search icon is clicked the text entered in the text box is not picked up". It searches empty data.

'HTML Part for search icon

<a href="javascript:void(0);" style="float: right; width: 20%; height: 55px; border-top-right-radius: .25rem!important; border-bottom-right-radius: .25rem!important; border: 0; padding: 0!important; background: #dadada; margin: 0!important; display: flex; align-items: center;justify-content: center;text-decoration: none;" ng-click="SearchButtonClick(1)"><em class="fa fa-search" aria-hidden="true" style="color: gray;"></em></a>

It being almost a month I have tried various different way which was also mentioned on stack overflow, like using "createEvent("keyboardevent")" but nothing worked.

' VBA code 
Sub AptivScrapping()

    Dim IE As SHDocVw.InternetExplorer
    Set IE = New InternetExplorer
    IE.Visible = True

    IE.navigate "https://ecat.aptiv.com"

    Do While IE.readyState < READYSTATE_COMPLETE
    Loop

    Dim idoc As MSHTML.HTMLDocument
    Set idoc = IE.document

    idoc.getElementById("searchUserInput").Value = "33188785"

    Dim doc_ele As MSHTML.IHTMLElement
    Dim doc_eles As MSHTML.IHTMLElementCollection

    Set doc_eles = idoc.getElementsByTagName("a")

    For Each doc_ele In doc_eles
        If doc_ele.getAttribute("ng-click") = "SearchButtonClick(1)" Then
            doc_ele.Click
            Exit Sub
        Else
        End If
    Next doc_ele

End Sub

Upvotes: 3

Views: 289

Answers (3)

Vishal
Vishal

Reputation: 119

I got the solution for above problem from Mrxel.com below is the link for that post. https://www.mrexcel.com/forum/excel-questions/1105434-vba-ie-automation-issue-angularjs-input-text-post5317832.html#post5317832

In this case I need to enter the search string character by character and sendKeys and input events inside the loop. Below is the working vba code.

    Sub AptivScrapping()

    Dim IE As SHDocVw.InternetExplorer
    Set IE = New InternetExplorer
    IE.Visible = True

    IE.navigate "https://ecat.aptiv.com"

    Do While IE.readyState < READYSTATE_COMPLETE
    Loop

    Dim idoc As MSHTML.HTMLDocument
    Set idoc = IE.document

    IE.document.getElementById("searchUserInput").Focus = True
    IE.document.getElementById("searchUserInput").Select

    sFieldInput = "33188785"
    For s = 1 To Len(sFieldInput)
    Application.SendKeys Mid(sFieldInput, s, 1)
    While IE.readyState < 4 Or IE.Busy
    Application.Wait DateAdd("s", LoopSeconds, Now)
    Wend
    Next s

    IE.document.getElementById("searchUserInput").Focus = False

    Dim doc_ele As MSHTML.IHTMLElement
    Dim doc_eles As MSHTML.IHTMLElementCollection

    Set doc_eles = idoc.getElementsByTagName("a")

    For Each doc_ele In doc_eles
        If doc_ele.getAttribute("ng-click") = "SearchButtonClick(1)" Then
            doc_ele.Click
            Exit Sub
        Else
        End If
    Next doc_ele

End Sub

Upvotes: 0

QHarr
QHarr

Reputation: 84465

The page does an xhr request to retrieve the search results. You can find it in the network tab after clicking submit. This means you can avoid, in this case, the expense of a browser and issue an xhr request. The response is json so you do need a json parser to handle the results.

I would use jsonconverter.bas to parse the json. After installing the code from that link in a standard module called JsonConverter, go to VBE > Tools > References > Add a reference to Microsoft Scripting Runtime

I dimension an array to hold the results. I determine rows from the number of items in the json collection returned and the number of columns from the size of the first item dictionary. I loop the json object, and inner loop the dictionary keys of each dictionary in collection, and populate the array. I write the array out in one go at end which is less i/o expensive.

Option Explicit

Public Sub GetInfo()
    Dim json As Object, ws As Worksheet, headers()
    Dim item As Object, key As Variant, results(), r As Long, c As Long

    Set ws = ThisWorkbook.Worksheets("Sheet1")
    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", "https://ecat.aptiv.com/json/eCatalogSearch/SearchProducts?filter=All&options=&pageSize=10&search=33188785", False
        .send
        Set json = JsonConverter.ParseJson(.responseText)("Products")
    End With
    headers = json.item(1).keys
    ReDim results(1 To json.Count, 1 To UBound(headers) + 1)
    For Each item In json
        r = r + 1: c = 1
        For Each key In item.keys
            results(r, c) = item(key)
            c = c + 1
        Next
    Next
    With ws
        .Cells(1, 1).Resize(1, UBound(headers) + 1) = headers
        .Cells(2, 1).Resize(UBound(results, 1), UBound(results, 2)) = results
    End With

Upvotes: 2

Mikku
Mikku

Reputation: 6654

You can do this instead:

txt = "33188785"

IE.navigate "https://ecat.aptiv.com/feature?search=" & txt

This will take you straight to the Search Result.

Code:

Sub AptivScrapping()

    Dim IE As SHDocVw.InternetExplorer
    Dim txt As String

    Set IE = New InternetExplorer

    txt = "33188785"

    IE.Visible = True
    IE.navigate "https://ecat.aptiv.com/feature?search=" & txt

    Do While IE.Busy
        Application.Wait DateAdd("s", 1, Now)
    Loop


End Sub
  • This will be faster as You will only have to load one page.

Why that's happening, i am not sure, but seems like the TextBox that is used to input text is not being Activated when adding text automatically to it. It is being activated when we click inside it.

Upvotes: 1

Related Questions