How to click on this particular button from VBA?

Question

I have a simple VBA code (see below), that goes to a webpage, selects some value, clicks the “Begin download” button, and then saves the file. The problem is I am stuck at the “clicking the download button” part. Can someone help?

Here is the code:

Sub Treasury_Auc_Notes()

    Dim IE As Object

    Set IE = Nothing
    Set IE = CreateObject("InternetExplorer.Application")
    IE.Visible = True
    IE.Navigate "http://www.treasurydirect.gov/RI/OFAuctions?form=ndnld&typesec=notes"

    While IE.Busy
        DoEvents
Wend

    IE.Document.All.Item("begYr").Value = "2012"
    With IE.Document.getElementsByName("cols")
        .Item(0).Checked = True
    End With

    'Click "Begin download" button  (this is where I am stuck)
    'Choose Save Open or Cancel   (I haven’t got to this part yet)

    ActiveWorkbook.SaveAs Filename


End Sub

Answer 1

This one's tricky, and due to restrictive security on my laptop, I'm not able to verify this 100%, but try:

While IE.ReadyState <> 4
    DoEvents
Wend

IE.Document.All.Item("begYr").Value = "2012"
With IE.Document.getElementsByName("cols")
    .Item(0).Checked = True
End With
Dim ele As Object

For Each ele In IE.Document.Forms
    If ele.Action = "/RI/OFAuctions" Then
        ele.Submit
        Exit For
    End If
Next

You may have to use SendKeys (I think Application.SendKeys "o") method to open the file then use VBA to save the ActiveWorkbook to the desired location. I'm not able to test SendKeys for reasons mentioned below.

Or, I'm pretty sure there is a WinAPI functions that can do this more reliably than SendKeys. You'll need to get the hWnd of the Save dialog and do some other stuff to force it to open/save. This is fairly advanced VBA that I probably have a reference to somewhere, but rarely need to use it. If you have trouble with this particular part, I would urge you to ask a separate question "How to get the hWnd of File Save dialog and download file from IE" or something like that.

NOTE: I can't test the SendKeys method. When I use the above code, I am fairly certain the file is being downloaded, but it is going to a temporary folder that is hidden, and difficult to find. In any case, it does appear to be downloading with some manual intervention. I get this warning:

I click to ignore that (I have no idea how to automate this part, I'm just trying to validate that the form .Submit method actually worked), and after some creative searching (temporary internet files get dumped in a strange/hidden folder usually) I verify the file is downloaded, although it is showing as a TXT extension instead of CSV.

If instead of using VBA, I click on the button manually, and I choose to "open" the file opens as CSV and has the same path to that temporary internet location.