Using VB to automate IE "save target as"

Question

I'm trying to use an excel VB macro to download excel files from a membership password-protected site. I am using the "InternetExplorer" object to open a browser window, log-in and browse to the correct page, then scanning for the links I want in the page. Using the Workbooks.Open(URLstring) doesn't work because Excel isn't logged. Instead of the actual file, it opens the html page asking for the log-in.

My preference would be to use the VB macro to automate the right-click "save target as" event in internet explorer on the correct link, but I don't know exactly how to do this.

Answer 1

There isn't really a way to do that with the Internet Explorer API. If it's just a throwaway script you can probably justify using SendKeys to 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 routine:

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