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