0

Excel Vba IE_automate: how to trigger changes on dropdown list

Please visit the webpage www.twse.com.tw/zh/page/trading/fund/MI_QFIIS.html www.twse.com.tw/zh/page/trading/fund/MI_QFIIS.html

First dropdown list = year

Second dropdown list = month

Third dropdown list = day

If I manually change the value of second dropdown list, the month from 02 to 01, the value of third dropdown list will automatically change to 01. (first day of month)

however, if I use the follow codes. the value of third dropdonw list wont change. I tried t.fireevent("onchange") but not working

Sub Getvaule() Set ie = CreateObject("internetexplorer.application") ie.Visible = True ie.navigate ("http://www.twse.com.tw/zh/page/trading/fund/MI_QFIIS.html") While ie.readystate <> 4 Or ie.busy Wend For Each t In ie.document.getElementsByTagName("select") If Trim(t.Name) = "mm" Then t.Value = "1" While ie.readystate <> 4 Or ie.busy Wend End If Next End Sub

Edited. sendkeys_example for reference

Sub sendkeys_example() Dim ie, x Set x = CreateObject("wscript.shell") Set ie = CreateObject("InternetExplorer.Application") ie.Navigate "http://www.twse.com.tw/zh/page/trading/fund/MI_QFIIS.html" ie.Visible = 1 ie.Toolbar = 0 While ie.readystate <> 4 Or ie.Busy Wend x.SendKeys "{tab 4}", True x.SendKeys "{down}" End Sub
Improve this question
2
  • You can use SendKeys to mimic what you are doing manually. In your example 3 Tabs bring you to the years combo, Enter opens the combo etc. This way you will have no problem Commented Feb 17, 2019 at 10:25
  • Thanks Siyon DP for giving me a direction. Commented Feb 18, 2019 at 18:20

1 Answer 1

Reset to default
1

I would avoid using a browser at all and use xmlhttp request. You can pass the date as query string parameter then parse the json response with a jsonparser such as jsonconverter.bas. After adding jsonconverter.bas to your project you need to go to VBE > Tools > References > Add a reference to Microsoft Scripting Runtime.

Just change the date=20180104 to the required date and concatenate into the request url.

Public Sub GetInfo() Dim url As String, json As Object, headers(), fields As Object Dim data As Object, item As Object, results(), r As Long, c As Long url = "http://www.twse.com.tw/fund/MI_QFIIS?response=json&date=20180103&selectType=01&_=1550397583207" With CreateObject("MSXML2.XMLHTTP") .Open "GET", url, False .send Set json = JsonConverter.ParseJson(.responseText) End With Set fields = json("fields") Set data = json("data") ReDim headers(1 To fields.Count) ReDim results(1 To data.Count, 1 To fields.Count) For i = 1 To fields.Count headers(i) = fields(i) Next For Each item In data r = r + 1: c = 1 For Each DataField In item results(r, c) = DataField c = c + 1 Next Next With ThisWorkbook.Worksheets("Sheet1") .Cells(1, 1).Resize(1, UBound(headers)) = headers .Cells(2, 1).Resize(UBound(results, 1), UBound(results, 2)) = results End With End Sub

Results:

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

if you are adding it as a variable yes. Dim as string and concantenate in. Dim dateField As String: dateField = "20180104" : url = "twse.com.tw/fund/MI_QFIIS?response=json&date=" & dateField & "&selectType=01&_=1550397583207"
sorry to bother you again. In situation like the following link histock.tw/stock/future.aspx what shall I do to get the data?? @QHarr
looks like you can use xmlhttp and target the table tag. Have a go and if stuck post a question with your code and where stuck. If you put the link here I will have a look. codingislove.com/http-requests-excel-vba

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.