CODEWITHSUNDEEP
vba
M Eqbal
M Eqbal

Reputation: 11

Getting Web table data into excel

I wanted to get monthly data from "https://investing.com/indices/us-spx-500-historical-data" into excel through vba.

Here is the source code of Time Frame dropdown menu:-

<select id="data_interval" class="newInput selectBox float_lang_base_1">
        <option value="Daily" selected="">Daily</option>
        <option value="Weekly">Weekly</option>
        <option value="Monthly">Monthly</option>
    </select>enter code here

Here is the Table source Code:

<table class="genTbl closedTbl historicalTbl" id="curr_table" tablesorter="">enter code here

Here is the VBA code I am using,

Sub GetData()
Dim IE As Object
Dim Links
Set IE = CreateObject("InternetExplorer.Application")
IE.navigate ("https://uk.investing.com/indices/us-spx-500-historical-data")
IE.Visible = True
Do
    DoEvents
Loop Until IE.readyState = 4
Set Links = IE.document.getElementById("data_interval")
Links.selectedIndex = 2
End Sub

2 is the Monthly Index number, It can select the Monthly from dropdown menu but the table not update itself from weekly to Monthly, I am also wanting to get this data into excel

Upvotes: 1

Views: 216

Answers (1)

Vityata
Vityata

Reputation: 43575

Try like this, it may work for you, depending on your Internet Explorer.

Option Explicit

Sub GetData()

    Dim IE      As Object
    Dim Links   As Object
    Dim evt     As Object

    Set IE = CreateObject("InternetExplorer.Application")
    IE.navigate ("https://uk.investing.com/indices/us-spx-500-historical-data")
    IE.Visible = True
    Do
        DoEvents        
    Loop Until IE.readyState = 4

    Set Links = IE.document.GetElementById("data_interval")
    Set evt = IE.document.createevent("htmlevents")
    evt.initevent "change", True, False

    Links.Focus
    Links.SelectedIndex = 2
    Links.FireEvent ("onChange")

    Links.dispatchevent evt

End Sub

Tribute to these guys for firing the event correctly.

Upvotes: 1

Related Questions