Excel VBA: Cannot perform auto search on website

Question

Recently I am learning to use excel macro to search on a website. I've read several forum threads and I came up with the code below. However, error appears when I reach the row

SearchBox(0).Value = SearchString

I tried to remove the (0) but another error appears as well. I am so frustrated right now and would like to seek your professional advice. The code works well on other websites. How should I change them to adapt to this site? Thank you so much!

P.S. I would also like to know the way to click the search button. Thanks!

Sub Searchstockcode()

Dim SearchString As String

SearchString = "700"

Set ie = CreateObject("InternetExplorer.Application")
With ie

ie.Visible = True
End With

ie.Navigate "http://www.hkexnews.hk/listedco/listconews/advancedsearch/search_active_main.aspx"

While ie.ReadyState <> 4

DoEvents

Wend


Dim SearchBox As Object

Set SearchBox = ie.Document.GetElementsByName("ct100$txt_stock_code")

SearchBox(0).Value = SearchString


Dim SearchButton As Object

Set SearchButton = ie.Document.GetElementsByName


End Sub

Regards, LLC

Answer 1

I don't know whether the problem with your name selection is due to the element having two name attributes but that seems possible.

You may use the following.

For the search box I use its id to target the element. This is normally unique on a document and the fastest selector method.

For the search button I use a CSS attribute + value selector of

[src*='/image/search.gif']

This targets the src attribute [] of the element by its value. * means contains. The selector looks for a src attribute containing /image/search.gif in its value.

You can observe the attribute here:

Option Explicit
Sub Searchstockcode()

    Dim SearchString As String, SearchBox As Object, SearchButton As Object, ie As Object

    SearchString = "700"

    Set ie = CreateObject("InternetExplorer.Application")

    ie.Visible = True

    ie.navigate "http://www.hkexnews.hk/listedco/listconews/advancedsearch/search_active_main.aspx"

    While ie.Busy Or ie.readyState < 4: DoEvents: Wend

    Set SearchBox = ie.document.getElementById("ctl00_txt_stock_code")
    SearchBox.Value = SearchString

    Set SearchButton = ie.document.querySelector("[src*='/image/search.gif']")
    SearchButton.Click

    While ie.Busy Or ie.readyState < 4: DoEvents: Wend

    Stop '<==Delete me
    'other code
    ie.Quit
End Sub