Download a txt file from Website using excel

[Kenneth Hobs]

Regarding the API method that Dr.K posted, use like you would any function. Put all of that code into a Module. Then, in the same Module or another, you can do something similar to how I tested mine.
[Code ]' =downloadfromurl("http://www.federalreserve.gov/releases/h6/hist/h6hist1.txt", "C:\temp\h6hist1.txt")
Sub Test_DownloadFromURL()
Dim url As String
Dim toFileName As String
url = "http://www.federalreserve.gov/releases/h6/hist/h6hist1.txt"
toFileName = ActiveWorkbook.Path & Application.PathSeparator & URLFilenamePart(url)
DownloadFromURL url, toFileName
Shell "notepad " & toFileName, vbNormalFocus
End Sub[/Code]

Noticed that I added a comment before the Sub to show how to use it as a UDF, User Defined Function. This is like any =function built into Excel. When you add it as a UDF like I did in the comment, it returns a value of TRUE if it downloaded the file. If it failed, it will show FALSE. Edit the cell again with F2 if you want to refresh the download. Once you press Enter key, there will be a pause while it downloads.

Or, play the test Sub like you would any macro.


If you don't know how to add references in VBE, you are losing out on early binding features that allow Intellisense to work with that object. To set a reference to winhttp.dll, set the Reference in VBE's menu, Tools > References... > Microsoft WinHTTP Services, version 5.1 > OK.

We can use late binding for the winhttp.dll reference. If you know what you are doing, late binding is fine.

Here is the same method using Late Binding. Notice that I changed it to a Function so that we can use it as a UDF if we like. The function will return "OK" if the file's source data was found and "Not Found" if it was not.

Sub Test_SourceTextLB()
    Dim url As String
    Dim toFileName As String
    url = "http://www.federalreserve.gov/releases/h6/hist/h6hist.txt"
    toFileName = ActiveWorkbook.Path & Application.PathSeparator & URLFilenamePart(url)
    If Dir(toFileName) <> "" Then Kill toFileName
    SourceTextToFileLB url, toFileName
    Shell "notepad " & toFileName, vbNormalFocus
End Sub

' =SourceTextToFileLB("http://www.federalreserve.gov/releases/h6/hist/h6hist1.txt", "C:\temp\h6hist1.txt")

Function SourceTextToFileLB(url As String, toFile As String)
    Dim Request As Object
    Dim ff As Integer
    Set Request = CreateObject("WinHttp.WinHttpRequest.5.1")
    Request.Open "GET", url, False
    Request.Send
    ff = FreeFile
    Open toFile For Output As #ff
    Print #ff, Request.ResponseText
    Close #ff
    SourceTextToFileLB = Request.StatusText
End Function

Mdmackillop's QueryTable method might be best if you wanted to import it to Excel directly.