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