CODEWITHSUNDEEP
excelvbaweb-scraping
Alfred
Alfred

Reputation: 69

From EXCEL VBA Click Button on Web

I am using VBA in Excel and I am trying to access a webpage and then click on a link to trigger a CSV download on my PC.

With VBA I am able so far to open IE and access the required Webpage. But after inspecting the HTML code of the page (F12) and using getElementsByClassName and other methods I cannot product a clickable object that will trigger the download from my code.

Sub Automate_IE_Enter_Data() 'This will load a webpage in IE Dim i As Long Dim URL As String Dim IE As Object Dim objElement As Object Dim objCollection As Object Dim HWNDSrc As Long

'Create InternetExplorer Object
Set IE = CreateObject("InternetExplorer.Application")

'Set IE.Visible = True to make IE visible, or False for IE to run in the background
IE.Visible = True

'Define URL
'URL = "https://www.automateexcel.com/excel/vba"
URL = "https://www.barchart.com/futures/quotes/ZWF9|530P/price-history/historical"

'Navigate to URL
IE.Navigate URL

' Statusbar let's user know website is loading
Application.StatusBar = URL & " is loading. Please wait..."

' Wait while IE loading...
'IE ReadyState = 4 signifies the webpage has loaded (the first loop is set to avoid inadvertantly skipping over the second loop)
'Do While IE.ReadyState = 4: DoEvents: Loop
Do Until IE.ReadyState = 4: DoEvents: Loop

'Webpage Loaded
Application.StatusBar = URL & " Loaded"

'Get Window ID for IE so we can set it as activate window
HWNDSrc = IE.hWnd
'Set IE as Active Window
SetForegroundWindow HWNDSrc

Set IEAppColl = IE.Document.getElementsByClassName("bc-glyph-download")(0)

IEAppColl.Click

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

SetForegroundWindow HWNDSrc

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

End Sub

Expected: 1) Open on IE the following URL: https://www.barchart.com/futures/quotes/ZWF9|530P/price-history/historical 2) Click the "max" link on the Daily Prices section to download a CSV file

1) is OK 2) produces the error: Object does not support this property or method.

I am not click as to how to replicat the clicking of the "max" link in my VBA Code. Not sure I am using ist correct name.

Upvotes: 1

Views: 2503

Answers (2)

QHarr
QHarr

Reputation: 84465

With IE - you can simply use the following to initiate the download provided login not requested.

ie.document.querySelector("[data-ref=historical]").Click

Longer login version:

Well that threw up horror after horror with both IE and Selenium including login details not accepted (for IE) as detected as bot, page not found errors thrown after login, stale element references, element not clickable...... The following is the horrific war I waged successfully to get the download. In the morning I will see if I can find a better way.

Option Explicit
Public Sub Download()
    Dim d As WebDriver
    Set d = New ChromeDriver
    Const URL = "https://www.barchart.com/futures/quotes/ZWF9%7C530P/price-history/historical"
    Const JS_WAIT_CLICKABLE = _
    "var target = this, endtime = Date.now() + arguments[0];" & _
    "(function check_clickable() {" & _
    "  var r = target.getBoundingClientRect(), x = r.left+r.width/2, y = r.top+r.height/2;" & _
    "  for (var e = document.elementFromPoint(x , y); e; e = e.parentElement)" & _
    "    if (e === target){ callback(target); return; }" & _
    "  if (Date.now() > endtime) { callback(target); return; }" & _
    "  setTimeout(check_clickable, 60);" & _
    "})();"                                      'by @florentbr

    With d
        .Start "Chrome"
        .get URL, timeout:=100000
        .FindElementByCss("[data-ref=historical]").Click

        With .FindElementsByCss("input.form-input")
            .item(1).SendKeys "name"
            .item(2).SendKeys "password"
        End With
        .FindElementByCss("[value=GO]").Click
        .GoBack
        .GoBack
        Application.Wait Now + TimeSerial(0, 0, 5)
        With .FindElementByCss("[data-ref=historical]")
            .ExecuteAsyncScript(JS_WAIT_CLICKABLE, 10000) _
        .Click
        End With
        .Quit
    End With
End Sub

Upvotes: 1

Lord Elrond
Lord Elrond

Reputation: 16052

That site won't let you download the csv unless you are logged in. the class you specified is linked to a jQuery that checks your login status and throws an error if you aren't. To download it through excel you would first need to go to the website and create an account, then write code to log you in, then download the csv, and it should work. I don't know the exact syntax for this but hopefully this will point you in the right direction.

Also I would recommend trying Selenium. Its a popular webcrawling software so it should be easy to find help with this.

Upvotes: 1

Related Questions