Download File from web using VBA

[rfeuchard]

Hi,

I'm quite new to vba and have been working on automating IE. Part of my task involves using explorer to download a file from a given url and save it to disk. I've figured out how to launch explorer and start the download, but then a dialogue box appears asking whether to save the file and where to save it. how do I automatically get past this and save the file to e.g "quotes.xls"?

 

[eric8005]

Can't answer your question, but I can suggest an alternative. I find iOpus iMacros good for browser automation. The free version cannot be controlled by VBA if that is really important to you, but I believe one of the pay versions can (take a look at the feature lists).

I use the free version to automatically download files.

 

[Haluk]

This may help to you;

http://www.mrexcel.com/forum/showthread.php?t=353006

 

[rfeuchard]

Thanks for the help, Haluk, but this code did not work for me..

I did this:

Sub Test()
Dim FileNum As Long
Dim FileData() As Byte
Dim MyFile As String
Dim WHTTP As Object

On Error Resume Next
Set WHTTP = CreateObject("WinHTTP.WinHTTPrequest.5")
If Err.Number <> 0 Then
Set WHTTP = CreateObject("WinHTTP.WinHTTPrequest.5.1")
End If
On Error GoTo 0

MyFile = "website xxxx.com"

WHTTP.Open "GET", MyFile, False
WHTTP.Send here is where I stop. This is the error message:
"Run-time error '-2147012867 (80072efd)':
A connection with the server could not be established"
FileData = WHTTP.ResponseBody
Set WHTTP = Nothing

If Dir("c:\test", vbDirectory) = Empty Then MkDir "c:\test"

FileNum = FreeFile
Open "the file I downloaded " For Binary Access Write As #FileNum
Put #FileNum, 1, FileData
Close #FileNum

MsgBox "Open the folder [ c:\test] for the downloaded file..."
End Sub

Does anyone have any idea of what mgiht be the problem?? I would be glad for any help.

Thanks!

 

[Haluk]

According to the error message seen from here, either you have given an invalid path for the variable MyFile as a file name located somewhere housing the file on a server or your internet connection is slow so that a time-out error has occured....

 

[ellyza]

Hi,

I have the exact same problem as rfeuchard, a run-time error upon sending the http request.
Did you find any fix for that?
I've checked the file address, and I have tried with different addresses, the error message persists. My connection is pretty fast and I don't use a proxy, so I don't think it's the cause.
I was wondering whether it could be some security problem, or me forgetting to activate some related service?

Thanks in advance for any help!

 

[ellyza]

Hi again,

I solved my problem, it was my bad.
I'm posting it just in case someone else wonders - my firewall was blocking outgoing connections!

 

[bapcki1]

how do you delete a post on this forum?

 

[DeepikaShinde]

Hi again,

I solved my problem, it was my bad.
I'm posting it just in case someone else wonders - my firewall was blocking outgoing connections!

Hi Please share the code.Even I am trying to do same task.

 

[Madhart]

Here are two working examples of VBA to Download Files that work for me...

Option Explicit
Declare PtrSafe Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" (ByVal pCaller As Long, ByVal szURL As String, ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long

Sub DownloadFileAPI()
Dim strURL As String
Dim LocalFilePath As String
Dim DownloadStatus As Long

    strURL = "http://data.iana.org/TLD/tlds-alpha-by-domain.txt"
    LocalFilePath = "C:\Test\TEST2_tlds-alpha-by-domain.txt"
    DownloadStatus = URLDownloadToFile(0, strURL, LocalFilePath, 0, 0)
    If DownloadStatus = 0 Then
        MsgBox "File Downloaded. Check in this path: " & LocalFilePath
    Else
        MsgBox "Download File Process Failed"
    End If
End Sub


Sub DownloadFile()
Dim WinHttpReq As Object
Dim oStream As Object
Dim myURL As String
Dim LocalFilePath As String

myURL = "http://data.iana.org/TLD/tlds-alpha-by-domain.txt"
LocalFilePath = "C:\Test\TEST_tlds-alpha-by-domain.txt"

Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
WinHttpReq.Open "GET", myURL, False, "", ""  '("username", "password")
WinHttpReq.send

If WinHttpReq.Status = 200 Then
    Set oStream = CreateObject("ADODB.Stream")
    oStream.Open
    oStream.Type = 1
    oStream.Write WinHttpReq.responseBody
    oStream.SaveToFile LocalFilePath, 2 ' 1 = no overwrite, 2 = overwrite
    oStream.Close
End If
End Sub