Download File from web using VBA

rfeuchard

New Member
Joined
Nov 12, 2008
Messages
2
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

New Member
Joined
Aug 10, 2007
Messages
13
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.
 

rfeuchard

New Member
Joined
Nov 12, 2008
Messages
2
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

Rules Violation
Joined
Oct 26, 2002
Messages
1,075
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

New Member
Joined
Mar 14, 2009
Messages
2
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? :confused:

Thanks in advance for any help!
 

ellyza

New Member
Joined
Mar 14, 2009
Messages
2
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! :oops:
 

Madhart

New Member
Joined
Oct 22, 2008
Messages
1
Here are two working examples of VBA to Download Files that work for me...

Code:
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
 

Forum statistics

Threads
1,085,752
Messages
5,385,680
Members
401,967
Latest member
Sullivag2

Some videos you may like

This Week's Hot Topics

Top