Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Excel VBA SendKeys not causing IE 9 to save download

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
like image 477
derigible Avatar asked Nov 03 '22 19:11

derigible


2 Answers

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.

enter image description here

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

enter image description here

FILE: The file can be downloaded here.

like image 67
Siddharth Rout Avatar answered Nov 09 '22 06:11

Siddharth Rout


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.

like image 43
Lifewithsun Avatar answered Nov 09 '22 08:11

Lifewithsun