Lots of words today eh? LOL 3rd way, maybe even 4th way after this post.
First, how to post code. See my signature block. It shows you how. Using that method of posting code is always best because it maintains the code formatting and makes for a quick and simple copy and paste into the editor.
Yesterday, I also read about issues of VBA code continuing before file download is complete. There was code somewhere to overcome that in vba, but unfortunately I didn't copy it over. I'll see if I can find it and incorporate it into the code I'm about to post for you.
Another thought for the shell and ftp method would be to use Chip Pearson's Shell and Wait. I've never used it, but it might be something to check out as an option.
Shell And Wait
I still haven't been able to get the Inet1...blah blah blah stuff to work that requires the additional Microsoft Internet Controls, but I think the code I found today might be better than that anyway. It appears that most of that stuff needs to go through a userform, which to me seems kind of pointless when you don't need a userform, but the download of the files. The Microsoft Internet Controls are added by....
In the VB editor click Tools > References then scroll down to Microsoft Internet Controls, check the box and click ok. Although I read somewhere that it could be missing or reference the wrong DLL.
This was one site:
Need help can't find Microsoft internet control in VBA list of references
Mine was referencing ieframe.dll before. I'm not familiar enough with these to know which one it should actually be.
Although, through a bunch of searching and playing today, I came up with something that I thought was quite useful. This only works with the http side of things. I tried 1000x times over to get it to work with ftp, but ftp urls produce undesired results. Although, it looks like for the end goal, it might not matter if you download via ftp or http.
Check this code out, although it might have the same problem of continuing before the download is complete. I'll look to see if I can find that code again to ensure it waits long enough. Also, this code, as I just found out, does crash if the file already exists locally, so code would have to be added to deal with a file that the same name exists locally.
Code:
Sub TestFileExistsandDownload()
'This code will test a web address to see if a file exists
'If the file exists, it will download the file.
' It's a mixture of code retreived from 2 sites:
' http://www.utteraccess.com/wiki/index.php?title=HttpRequest_Class
' http://social.msdn.microsoft.com/Forums/en-US/exceldev/thread/62a95cb5-be76-4328-a9b9-ede9f981ba83/
Dim XmlHttpReq As Object
Dim WebURL As String
Dim WebFile As String
Dim SaveTo As String
Dim ObjStream As Object
WebURL = "http://www.sec.gov/Archives/edgar/data/1084869/000110465913008750/"
WebFile = "Financial_Report.xls"
SaveTo = "C:\rj\excel\"
Set XmlHttpReq = CreateObject("Microsoft.XMLHTTP")
XmlHttpReq.Open "GET", WebURL & WebFile, False
XmlHttpReq.Send
If XmlHttpReq.Status = 200 Then
'download if file exists
Set ObjStream = CreateObject("ADODB.Stream")
ObjStream.Open
ObjStream.Type = 1
ObjStream.Write XmlHttpReq.ResponseBody
ObjStream.SaveToFile (SaveTo & WebFile)
ObjStream.Close
Else
MsgBox "Status: " & XmlHttpReq.Status & Chr(10) & _
"Status Text: " & XmlHttpReq.StatusText
End If
Set XmlHttpReq = Nothing
End Sub
PS: Forget my job, your job is more fun.
I'd be interested in the finished product, just because it interests me in how I may otherwise use the same techniques.
