Cannot perform auto search on a specific 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. The code works well on other websites. How should I change them to adapt to this site?

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

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

Please share with us the exact error message you are receiving. Also what other web site(s) does this macro work well with? So that we may compare/contrast their html. — donPablo, Sep 09 '18 at 05:16

QHarr · Accepted Answer · 2018-09-09T07:22:54.200

0

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

edited Sep 09 '18 at 07:22

answered Sep 09 '18 at 06:29

QHarr

83,427
12
54
101

Thanks Qharr!! It works perfectly fine! I think the reason I had the error is because I messed up with "l" and "1" haha! I have another question concerning the next step: I would always like to click the first link that appears after clicking the search button. I assume this code should work: 'Set TargetFile = i.e.document.getElementById("ctl00_gvMain_ctl02_hlTitle") TargetFile.Click' But after that I have no idea how to save or open the file. My purpose is to open the file and extract certain data. Thank you so much in advance! – LLC Sep 09 '18 at 13:28
Assume what code should work? Post a question with what you are trying and leave the link here and I will have a look. Make sure you state what should happen and was is happening. – QHarr Sep 09 '18 at 13:32
Set TargetFile = i.e.document.getElementById("ctl00_gvMain_ctl02_hlTitle") TargetFile.Click – LLC Sep 09 '18 at 13:34
Post a question as it is easier to debug there that in the comments here. – QHarr Sep 09 '18 at 13:34

Cannot perform auto search on a specific website

1 Answers1

Linked