Excel VBA SendKeys不会导致IE 9保存下载 [英] Excel VBA SendKeys not causing IE 9 to save download

问题描述

我正在编写一个自动化从我公司内部网站下载csv文件的过程的宏。由于许多原因，我不能使用任何xmlhttp对象，我已经把宏到它将下载文件的点。问题是当它点击链接下载文件时，Internet Explorer会提示用户打开，保存和取消按钮。而在IE中，使用alt + shift + s会保存下载，但是我似乎无法从excel vba中获取Sendkeys％+ s方法。任何帮助将不胜感激。以下是相关代码：

 函数followLinkByText（thetext As String）As Boolean 
'click the first link that has指定的文本
 Dim alink As Variant 
 
'通过html文档中的每个锚点循环，直到找到指定的文本
'然后单击链接
对于每个alink In ie .document.Links 
如果alink.innerHTML = thetext Then 
 alink.Click 
'waitForLoad 
 Application.Wait Now + TimeValue（00:00:01）
 Application.SendKeys％+ s，True 
 
 followLinkByText = True 
退出函数
结束如果
下一个
 
结束函数
  

解决方案

像我在评论中提到的，信息安全栏很难与文件下载窗口进行交互。

另一种方法是使用web浏览器控件，然后传递网址但是，这种方法的主要问题是您不能将Web浏览器放在同一Excel实例中。一旦文件下载窗口弹出，您的整个VBA宏将会停顿，直到你不处理它。

这是另一种选择。这是一个我在VB6中创建的小exe，它会弹出文件下载窗口，绕过IE信息安全栏。一旦弹出文件下载窗口，您可以使用API​​进行交互，如我的。

I am writing a macro that automates the process of downloading a csv file from my company's internal website. For many reasons, I can't use any xmlhttp objects and I have gotten the macro to the point where it will download the file. The problem is that when it clicks the link to download the file, Internet Explorer prompts the user with Open, Save, and Cancel buttons. While in IE, using alt+shift+s will save the download, but I can't seem to get the Sendkeys "%+s" method from excel vba to work. Any help would be greatly appreciated. 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 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.

这篇关于Excel VBA SendKeys不会导致IE 9保存下载的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！