Use vba to open IE and download a .csv file?

Question

I have a program that has to open a webpage, copy over some data, save the data, and close the webpage. This is the format of the webpage I am looking for. My goal is to have vba automatically download that .csv file (right side of the page, above the data itself, if you don't see it), and move it to a particular location out of downloads.

(I was using workbooks.open(URL) before, but I changed the URL to a government site to get better, more reliable data. Problem is, this URL doesn't seem to work when I do that, unlike the old website, which would open up the page inside of Excel. That was convenient, and if anyone knows how to make that work again with this site, I'll just do that instead, though I'm still curious to know how to use IE for vba)

Problem is, I know piss all about how to use IE via VBA. The code I have so far is

Dim IE As InternetExplorer
Dim Doc As Object, Elements As Object 'these two things and the next row are just things I heard might be useful through my research on how to do this
Dim WSSh As Object

Set IE = New InternetExplorer
Set WSSh = CreateObject("WScript.Shell") 'not even sure what to do with this but until I'm sure I won't need it I'll just leave it there

With IE
    .Visible = True 'once it's finished I'll take this out, I don't actually need to see this happen
        'just want to see it happen while I write/debug it
    .Navigate DataURLBegin & DataURLMiddle & DataURLEnd 'these three combined form the url above
    Do
        DoEvents
    Loop While .ReadyState <> READYSTATE_COMPLETE
    Stop
    'this is where I've tried a lot of things but I'm not sure where to even start
End With

As you can see I...haven't gotten far. I gather from other people's attempts to do similar things that I have to loop through the elements on the sheet that can be activated, but I looked through IE's children, and I didn't see anything that seemed like elements, or that I could loop through. So I'm not entirely sure where to proceed.

Edit: so I think I've gotten closer, but I'm still having trouble. My code is now:

Option Explicit

Sub ContactWeb(ByVal URL As String, ByVal DownloadPath As String)
'I moved it into its own sub to stop taking up so much space
'and make things easy to read

Dim IE As InternetExplorer 'some of these aren't used, I'll clean it up once this works
Dim Doc As Object, Elmt As Object
Dim HTMLColl As MSHTML.IHTMLElementCollection
Dim HTMLInput As MSHTML.HTMLInputElement

Set IE = New InternetExplorer

With IE
    .Visible = True
    .Navigate URL

    Do  'waits until the webpage is fully loaded, otherwise everything afterwards fails
        DoEvents
    Loop While .readyState <> READYSTATE_COMPLETE

    Set Doc = IE.Document

    Set Elmt = Doc.getElementById("bulkCsvLink")

    Elmt.Click 'I can't tell if this is actually doing anything, though...

    Do
        DoEvents
    Loop While .readyState <> READYSTATE_COMPLETE

    Stop

    Set HTMLColl = Doc.getElementsByTagName("input")
    For Each Elmt In HTMLColl
        If Elmt.innerText = "Download Data" Or Elmt.Value = "Download Data" Then
            Stop
            Elmt.Click 'This I know works because I tested it on the xml version
            Exit For
        End If
    Next Elmt

    Stop
    Do
        DoEvents
    Loop While .readyState <> READYSTATE_COMPLETE
    IE.Quit
End With

End Sub

As near as I can tell, this code is correct, but I don't know how to confirm the little message at the bottom that asks if I want to download (which I could have sworn wasn't appearing before, but maybe that was just me; sorry to anyone who saw the flurry of edits I did). How do I click on confirm? Preferably with the ability to input a download location, though I suppose I can always just copy/paste it to the right place using code as well.

Answer 1

If I understand right, you want to download CSV file and open it in Excel. If you do it manually with Chrome, for example, you can see that URL where it came from is:

http://climate.weather.gc.ca/climate_data/bulk_data_e.html?format=csv&stationID=889&Year=2011&Month=9&Day=19&timeframe=1&submit=Download+Data

You can continue with Workbooks.Open(URL) or use XMLHTTP object or even QueryTables, just format URL string with parameters you need (station ID, date etc.). No IE required - I'm trying to use it only if auth, buttons clicks or JavaScript required to get data.

UPD: Use this macro below, works without errors

Sub OpenCSV()
strURL = "http://climate.weataher.gc.ca/climate_data/bulk_data_e.html?format=csv&stationID=889&Year=2011&Month=9&Day=19&timeframe=1&submit=Download+Data"
Application.Workbooks.Open (strURL)
End Sub