Using VBA to download and save a file which behind a login (but do not want the code to perform login)

[wmc]

Hi there.

I have a number of files that need to be downloaded periodically and require me to log into a secure website in order to do so. I would like to automate the download, but not the login. I want to log in manually, and then kick off the macro.

I have searched the this forum and the web in general. I found a number of solutions that would appear to work, but none of them seems to do the trick. The URL to initiate the download is saved in cell J8. The filename I would like to use is saved in cell B8. How can I use Excel to automatically download and save the file, overwriting the existing file if one exists?

Here is the code that seems to be most promising:

Sub DownloadFile()

Dim website As String
Dim filename As String
website = Range("J8").Value
filename = Range("B8").Value

Dim WinHttpReq As Object
Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
WinHttpReq.Open "GET", website, False
WinHttpReq.send

OpenX = WinHttpReq.responseBody
If WinHttpReq.Status = 200 Then
    Set oStream = CreateObject("ADODB.Stream")
    oStream.Open
    oStream.Type = 1
    oStream.write WinHttpReq.responseBody
    oStream.SaveToFile ("\\network folder\my target folder\" & filename & ".xls")
    oStream.Close
End If

End Sub

I get Run-time error '-2147467259 (80004005)':
Method 'open' of object 'IXMLHTTPRequest' failed on this line:

WinHttpReq.Open "GET", OpenX, False

Any ideas?

Thanks in advance for any assistance!