Excel VBA to Save As from IE 11 Download Bar

Question

I have the following code to download a file from the web, but I have to manually do a Save As.

  Dim Filename As String
  Dim ieApp As Object
  Dim URL As String

    URL = Range("All_Quad_URL")
    Filename = "C:\Historic_Weather_Data\Precipitation\" & Range("File_Name").Value

    Set ieApp = CreateObject("InternetExplorer.Application")
    ieApp.Visible = True
    ieApp.Navigate URL

      While ieApp.Busy Or ieApp.ReadyState <> 45
        DoEvents
      Wend

    ieApp.Quit

    Set ieApp = Nothing

I would like to automate Save As. I have tried the following with no luck:

Controlling IE11 "Do you want to Open/Save" dialogue window buttons in VBA

I still get the "View Downloads - Internet Explore" dialog box with the open/save options. I changed the FindWindowEX to h = FindWindowEx(h, 0, "View Downloads - Internet Explorer", vbNullString)

The file name and location for the Save As needs to be

Filename = "C:\Historic_Weather_Data\Precipitation\" & Range("File_Name").Value

I am also getting

Run-time error "-2147467259 (80004005)': Method 'Busy' of object 'IWebBrowser 2' failed"

that debugs to the While ieApp.Busy line.

Any help is appreciated.

Assuming the URL is a direct link to the downloadable file, try it without IE. Use the URLDownloadToFile API function given in the answer: https://stackoverflow.com/a/23299764/1467082 — David Zemens, May 09 '19 at 17:08

W-hit · Answer 1 · 2019-05-09T16:24:20.883

Unfortunately IE doesn't let you set the path to save a download. I've searched this for hours until I found the answer. It will save to the last location you saved a download.

However, I have some good news. place the below code after you click the download button, and it should work for you. You'll also need to add the appropriate library references.

EDIT: if you want the full code for save as, you can find it Here

'wait for save as window to appear
Dim o As IUIAutomation
Dim h As LongPtr
Set o = New CUIAutomation
h = 0
Do Until h > 0
    'h = ie.hWnd
    h = FindWindow("#32770", "Internet Explorer")
Loop

'find and click save as button
Dim e As IUIAutomationElement
Dim iCnd As IUIAutomationCondition
Dim Button As IUIAutomationElement
Set e = o.ElementFromHandle(ByVal h)
Set Button = Nothing
Do Until Not Button Is Nothing
    Set iCnd = o.CreatePropertyCondition(UIA_NamePropertyId, "Save as")
    Set Button = e.FindFirst(TreeScope_Subtree, iCnd)
Loop

Dim InvokePattern As IUIAutomationInvokePattern
Set InvokePattern = Button.GetCurrentPattern(UIA_InvokePatternId)
InvokePattern.Invoke

Excel VBA to Save As from IE 11 Download Bar

1 Answers1

Linked