scrape with excel vba changing input data before scraping

Question

In am trying to change the data on the website for an input field and have that information refreshed on the page. I have updated the input field, but I am not sure how to refresh the page so that the inner table uses the new data from the input field

Below is my code:

Dim IE As InternetExplorer
Dim htmldoc As HTMLDocument
Dim ieURL As String
Dim sPicker As String

ieURL = "https://www.investing.com/commodities/crude-oil-historical-data"

sPicker = "10/01/2017 - 12/31/2017"

'Open InternetExplorer
Set IE = New InternetExplorer
IE.Visible = True

IE.Navigate ieURL

Set htmldoc = IE.document 'Document webpage

' wait until the page loads before doing anything
Do Until (IE.readyState = 4 And Not IE.Busy)
    DoEvents ' DoEvents releases the macro and lets excel do other thing while it waits
Loop

Dim drp As HTMLFormElement
Set drp = htmldoc.getElementById("widgetFieldDateRange")
drp.innerText = sPicker     'Set the new timeframe for scraping

Dim inpt As HTMLInputElement
Set inpt = htmldoc.getElementById("picker")
inpt.Value = sPicker     'Set the new timeframe for scraping

' wait until the page loads before doing anything
Do Until (IE.readyState = 4 And Not IE.Busy)
    DoEvents ' DoEvents releases the macro and lets excel do other thing while it waits
Loop

Thanks for your help

Did you try the below script @EddiRae? What's your feedback? — SIM
– SIM, Commented Jan 9, 2018 at 16:06

SIM · Accepted Answer · 2018-01-09 21:30:42Z

1

Try the below script. It should solve the issue.

Sub Web_Data()

    Dim IE As New InternetExplorer, html As New HTMLDocument
    Dim post As Object, elem As Object, t_data As Object
    Dim trow As Object, tcel As Object

    With IE
        .Visible = True
        .navigate "https://www.investing.com/commodities/crude-oil-historical-data"
        While .readyState < 4: DoEvents: Wend
        Set html = .document
    End With

    Application.Wait Now + TimeValue("00:00:05")

    html.getElementById("widgetFieldDateRange").Click
    Application.Wait Now + TimeValue("00:00:03")

    Set post = html.getElementById("startDate")
    post.innerText = ""
    post.Focus
    Application.SendKeys "10/01/2017"
    Application.Wait Now + TimeValue("00:00:03")

    Set elem = html.getElementById("endDate")
    elem.innerText = ""
    elem.Focus
    Application.SendKeys "12/31/2017"
    Application.Wait Now + TimeValue("00:00:03")

    html.getElementById("applyBtn").Click
    Application.Wait Now + TimeValue("00:00:03")


    Set t_data = html.getElementById("curr_table")

    For Each trow In t_data.Rows
        For Each tcel In trow.Cells
            y = y + 1: Cells(x + 1, y) = tcel.innerText
        Next tcel
        y = 0
        x = x + 1
    Next trow

End Sub

Reference to add to the library:

1. Microsoft Internet Controls
2. Microsoft HTML Object Library

edited Jan 9, 2018 at 21:30

answered Jan 8, 2018 at 22:43

SIM

22.5k6 gold badges45 silver badges116 bronze badges

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

3 Comments

EddiRae Over a year ago

Shahin, I did notice that once this code is completed, it goes back to the main page (investing.com) before I can grab the table that was just updated with the new dates.

EddiRae Over a year ago

Also, is there anyway to do the SendKeys with the IE not visible? It seems to update the code with the dates and not the website fields when the page is hidden.

SIM Over a year ago

I've updated my above script for parsing the data from that newly populated table. You are wrong @EddiRae. I've checked myself and i did find the correct data in my spreadsheet according to the date you have requested in your post. Execute the above script and see for yourself. As for your second question is concerned: I'm not sure how to achieve the same result using IE invisible.

Collectives™ on Stack Overflow

scrape with excel vba changing input data before scraping

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related