VBA IE approve download in dialog window

Question

Attempting to download data using a link to a file. IE opens and navigates to the file but a popup window asks me to open the file. I need to click this open button. Requesting help with navigating the pop up. Here is my code thus far:

Sub GetData()

Const cURL = "http://www.bankofengland.co.uk/statistics/Documents/yieldcurve/ukinf05.xlsx"

Dim IE As InternetExplorer

    Dim doc As HTMLDocument

    Dim HTMLelement As IHTMLElement

    Set IE = New InternetExplorer

    IE.Visible = False
    IE.Navigate cURL


End Sub

Use IE's developer tools to locate the `open` button's element id or name and set an object reference to it, which you can then click on. A very quick search of this site (or Google) will give you hundreds of examples — Dave, Sep 26 '16 at 13:26
If you have a URL that points directly to the file, using the API is much, much easier than automating IE. See [this answer](http://stackoverflow.com/a/26186347/4088852). — Comintern, Sep 26 '16 at 14:11
@Comintern Unfortunately I have tried codes like these and get the same "Unable to download the file, or the source URL doesn't exist" message. Despite this being a valid link. — nmgnp, Sep 27 '16 at 18:58
@Dave The code I am using only opens the download window and even when IE.Visible=True only the download window can be seen. Thus I do not have ability to open developer tools. — nmgnp, Sep 27 '16 at 19:04

score 0 · Answer 1 · answered Sep 27 '16 at 19:33

As @Comintern had suggested, and with a little checking on this blog entry by SiddarthRout

Option Explicit

Private Declare Function URLDownloadToFile Lib "urlmon" _
    Alias "URLDownloadToFileA" (ByVal pCaller As Long, _
    ByVal szURL As String, ByVal szFileName As String, _
    ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long

Dim Ret As Long

Sub Sample()
    Dim strURL As String
    Dim strPath As String

    '~~> URL of the Path
    strURL = "http://www.bankofengland.co.uk/statistics/Documents/yieldcurve/ukinf05.xlsx"
    '~~> Destination for the file
    strPath = "C:\temp\ukinf05.xlsx"

    Ret = URLDownloadToFile(0, strURL, strPath, 0, 0)

    If Ret = 0 Then
        MsgBox "File successfully downloaded"
    Else
        MsgBox "Unable to download the file"
    End If
End Sub

The end result I got was the file, correctly in my C:\temp\ folder.

VBA IE approve download in dialog window

1 Answers1