Excel VBA SendKeys 不会导致 IE 9 保存下载

Excel VBA SendKeys not causing IE 9 to save download

提问by derigible

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

我正在编写一个宏来从我公司的内部网站下载一个 csv 文件。

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.

出于多种原因，我不能使用任何 xmlhttp 对象。宏将下载文件。问题是 Internet Explorer 9 提示用户使用打开、保存和取消按钮。

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

在 IE 中，Alt+Shift+S 将保存下载，但我无法从 Excel VBA 中获取 Sendkeys "%+s" 方法。

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

采纳答案by Siddharth Rout

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.

另一种方法是使用 webbrowser 控件，然后将 URL 传递给它。但是这种方法的主要问题是你不能在同一个 Excel 实例中使用 webbrowser。一旦文件下载窗口弹出，您的整个 VBA 宏就会停止，直到您不将其处理掉为止。

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 blogarticle.

这是一个替代方案。这是我在 VB6 中创建的一个小 exe，它将绕过 IE 信息安全栏弹出文件下载窗口。一旦“文件下载”窗口弹出，您就可以使用我的博客文章中所示的 API 与其进行交互。

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

让我们举个例子来看看我们是如何与这个 vb6 exe 文件交互的。

Create a module in Excel and paste this code.

在 Excel 中创建一个模块并粘贴此代码。

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.

重要说明：由于您没有给我任何 URL，我采用的是静态 URL。请用您的链接替换它。现在，根据您指定的链接，您可能会看到这两个下载窗口之一。根据您看到的下载窗口，您必须根据下图找到窗口句柄。有关我提供的博客链接的更多详细信息。

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.

下载附加的文件并将其保存在 say 中C:\。如果您将它保存在任何其他位置，请在下面的 Shell 语句中修改它。

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.

文件：该文件可以在这里下载。

回答by Lifewithsun

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

你可以试试这个，因为它在 IE 11 上对我有用：

1. Copy file C:\Windows\System32\UIAutomationCore.dllfile to users Documents i.e C:\Users\admin\Documentsthen add reference UIAutomationClientto 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   
   

1. 将文件C:\Windows\System32\UIAutomationCore.dll文件复制到用户文档，即C:\Users\admin\Documents然后添加UIAutomationClient对您的宏文件的引用。

2. 将以下代码粘贴到您的模块中：

       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.

在你的最后尝试。

回答by Tony L.

Your Application.Sendkeysjust needs a tweak. Below is the code I am using so it is tested on IE11. This is for Alt+Swith no Shiftwhich is the keyboard shortcut in IE11. Let me know if this doesn't work and you need help adding the Shiftback in.

你Application.Sendkeys只需要一个调整。下面是我正在使用的代码，因此它在 IE11 上进行了测试。这是用于Alt+S没有的Shift，这是 IE11 中的键盘快捷键。如果这不起作用，请告诉我，您需要帮助重新添加Shift。

Application.SendKeys "%{S}", True

Application.SendKeys "%{S}", True

回答by Rodolphe

I think I came up with a simpler solution: when the download bar appears in IE9, just by-pass it by displaying the "real" Download Pop Up window. The shortcut is "CTRL+J". All you have to do next is click on "Save" or "Open". There might be pretty ways to do it, but I simply send a key sequence to move the focus on desired option and then press enter.

我想我想出了一个更简单的解决方案：当下载栏出现在 IE9 中时，只需通过显示“真实的”下载弹出窗口来绕过它。快捷键是“CTRL+J”。您接下来要做的就是单击“保存”或“打开”。可能有很多方法可以做到这一点，但我只是发送一个键序列来将焦点移到所需的选项上，然后按 Enter。

Here is the code:

这是代码：

' Wait for download bar to appear
Application.Wait (Now + TimeValue("0:00:04"))

' Sending CTRL+J to open download pop-up
SendKeys "^j"

' Wait for download popup to appear
Application.Wait (Now + TimeValue("0:00:02"))

' Sending keys sequence to click on "Save" button
SendKeys "{RIGHT}{RIGHT}{RIGHT}~"