Excel VBA SendKeys not causing IE 9 to save download

Question

I am writing a macro to download a csv file from my company's internal website.

For many reasons I can't use any xmlhttp objects. The macro will download the file. The problem is Internet Explorer 9 prompts the user with Open, Save, and Cancel buttons.

While in IE, Alt+Shift+S will save the download, but I can't get the Sendkeys "%+s" method from Excel VBA to work.

Here is the relevant code:

Function followLinkByText(thetext As String) As Boolean
   'clicks the first link that has the specified text
    Dim alink As Variant

    'Loops through every anchor in HTML document until specified text is found
    ' then clicks the link
    For Each alink In ie.document.Links
       If alink.innerHTML = thetext Then
            alink.Click
            'waitForLoad
            Application.Wait Now + TimeValue("00:00:01")
            Application.SendKeys "%+s", True

            followLinkByText = True
            Exit Function
        End If
     Next

End Function

Solution 1

Like I mentioned in my comments, The Info Security bar makes it difficult to interact with the File Download Window.

An alternative is to use the webbrowser control and then passing the URL to it. But the main problem with this method is that you cannot have the webbrowser in the same Excel Instance. Once the File Download window pops up your entire VBA Macro will come to a standstill till the time you do not dispose it off.

Here is an alternative. Here is a small exe that I created in VB6 which will pop up the File Download window bypassing the IE Info Security Bar. And once the File Download window pops up, you can interact with it using the APIs as shown in my blog article.

Let's take an example to see on how we interact with this vb6 exe file.

Create a module in Excel and paste this code.

IMPORTANT NOTE: Since you didn't give me any URL, I am taking a Static URL. Please replace it with your link. Now depending upon the link that you specify, you might see the one of these two download windows. Based on the download window that you see you will have to find the window handles based on the pic shown below. More details on the blog link that I gave.

Download the file attached and save it in say C:\. If you save it in any other location then amend that in the Shell statement below.

Sub Sample()
    Dim sUrl As String

    sUrl = "http://spreadsheetpage.com/downloads/xl/king-james-bible.xlsm"

    Shell "C:\FDL.exe " & sUrl, vbNormalFocus
End Sub

SNAPSHOT

FILE: The file can be downloaded here.

Solution 2

You may try this as it is worked for me on IE 11:

1. Copy file C:\Windows\System32\UIAutomationCore.dll file to users Documents i.e C:\Users\admin\Documents then add reference UIAutomationClient to your macro file.

2. Paste below code in your module:

       Option Explicit
       Dim ie As InternetExplorer
       Dim h As LongPtr
       Private Declare PtrSafe Function FindWindowEx Lib "user32" Alias "FindWindowExA" (ByVal hWnd1 As LongPtr, ByVal hWnd2 As LongPtr, ByVal lpsz1 As String, ByVal lpsz2 As String) As LongPtr
   
   Sub Download()
       Dim o As IUIAutomation
       Dim e As IUIAutomationElement
       Set o = New CUIAutomation
       h = ie.Hwnd
       h = FindWindowEx(h, 0, "Frame Notification Bar", vbNullString)
       If h = 0 Then Exit Sub
   
       Set e = o.ElementFromHandle(ByVal h)
       Dim iCnd As IUIAutomationCondition
       Set iCnd = o.CreatePropertyCondition(UIA_NamePropertyId, "Save")
   
       Dim Button As IUIAutomationElement
       Set Button = e.FindFirst(TreeScope_Subtree, iCnd)
       Dim InvokePattern As IUIAutomationInvokePattern
       Set InvokePattern = Button.GetCurrentPattern(UIA_InvokePatternId)
       InvokePattern.Invoke
   End Sub   
   

Try at your end.