Using VB to automate IE "Save Target As"

Question

I am trying to use an excel VB macro to download excel files from a password protected site. I use the InternetExplorer object to open a browser window, log in and go to the correct page, and then check the links I want on the page. Using Workbooks.Open (URLstring) does not work because Excel is not registering. Instead of the actual file, the html page opens, requesting login.

My advantage would be to use the VB macro to automate the right-click on the Save Target As event in Internet Explorer with the correct link, but I don’t know exactly how to do this.

Answer 1

There really is no way to do this using the Internet Explorer API. If this is just a throw script, you can probably justify using SendKeys for yourself.

Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) ... Sub YourMacro() ... Navigate IE to the correct document, and get it to pop up the "Save As" dialog ... Set sh = CreateObject("WScript.Shell") sh.AppActivate "File Download" sh.SendKeys "S" Sleep 100 sh.SendKeys "C:\Path\filename.ext{ENTER}" End Sub 

WScript.Shell Documentation

Answer 2

If you know the URL of the file you want to download, you can use this procedure:

 Sub HTTPDownloadFile(ByVal URL As String, ByVal LocalFileName As String) Dim http As Object ' Inet Dim Contents() As Byte Set http = New Inet Set http = CreateObject("InetCtls.Inet") With http .protocol = icHTTP .URL = URL Contents() = .OpenURL(.URL, icByteArray) End With Set http = Nothing Open LocalFileName For Binary Access Write As #1 Put #1, , Contents() Close #1 End Sub