From EXCEL VBA Click Button on Web

Question

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.

Please share the relevant html using the snippet tool via edit — QHarr
– QHarr, Commented Dec 27, 2018 at 3:37

Lord Elrond · Accepted Answer · 2018-12-26 23:49:41Z

1

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.

answered Dec 26, 2018 at 23:49

Lord Elrond

16.4k8 gold badges54 silver badges92 bronze badges

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

5 Comments

user10829321 Over a year ago

There is also the point of treating a collection (e.g. getElementsByClassName (plural)) as a single element. getElementsByClassName("blah-blah")(0).click might be a better approach.

Alfred Over a year ago

It works fine with getElementsByClassName("blah-blah")(0).click!

Alfred Over a year ago

I can now Access the web and click on the desired "max" button. If I am logged in to the web previous to running the code then the new IE window logs me in automatically which is good. Then the code clicks the "max" button and triggers a download Action. BUT there is a last prompt warning that reads: "Do you want to open or save "filename.csv" from barchart.com? I tried SendKeys commands to select SAVE on that prompt so that it saves automattically. Unfortunately Sendkeys does not help much as Tabs Keep changing on the web. The prompts also Comes after changing IE Settings... Any ideas??

Lord Elrond Over a year ago

@Alfred try changing the settings in IE to automatically download files when clicked on so that the prompt doesn't show up in the first place.

Alfred Over a year ago

Jane: IE11 browser does not allow it. There is no way to click to allow automatic download of files without prompting...one week trying and Blogs just to save you time.Using Windows 10

QHarr · Accepted Answer · 2019-01-04 17:05:51Z

1

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

edited Jan 4, 2019 at 17:05

answered Dec 31, 2018 at 23:23

QHarr

84.5k14 gold badges58 silver badges105 bronze badges

11 Comments

Alfred Over a year ago

Hi, I tried also Selenium but after installing Selenium Type Library and SeleniumWrapper Type Library still does not work. Can you send me the link for the ChromeDriver you are using so I can test your code? thx!

Alfred Over a year ago

Please note that the correct URL is: Const URL = "barchart.com/futures/quotes/ZWF|530P/price-history/historical", and that as soon as you access it it will change to a slightly different URL address that is useless.

Alfred Over a year ago

In your code above you use the "transformed" URL that does not work:barchart.com/futures/quotes/ZWF9%7C530P/price-history/…

Alfred Over a year ago

You can create for free any login and password. Once you are logged in in a browser, launching again from scratch that browser remembers your login and it does not prompt you to relogin

QHarr Over a year ago

I tested and worked for me. I can try something updated later today.

|

Collectives™ on Stack Overflow

From EXCEL VBA Click Button on Web

2 Answers 2

5 Comments

11 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

11 Comments

Your Answer

Sign up or log in

Post as a guest

Related