Download a txt file from Website using excel

[Nick_London]

Hi,

Is it possible to download the following txt file from within excel?

http://www.federalreserve.gov/releas...st/h6hist1.txt

It's possible to download it manually by left clicking on the ASCII (in this case under Tabel 1) on the following screen and selecting on save as but I would like to download it automatically from excel using a macro.

Does anyone have any idea what the URL/direct link would be so that when you paste in the above link, it automatically allows you to download the file instead of going to the screen and clicking save as on the ASCII?

http://www.federalreserve.gov/releases/h6/hist/

Thanks,

Nick

[Dr.K]

Yeah, there is a Windows API function for that. I made a wrapper function that clears the cache first, and then DLs the file.

Private Declare 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
 
Private Declare Function DeleteUrlCacheEntry Lib "wininet" _
Alias "DeleteUrlCacheEntryA" (ByVal lpszUrlName As String) As Boolean
 
Function DownloadFromURL(FullURL As String, Destination As String) As Boolean
    Dim varTemp As Variant
    DownloadFromURL = False
    If FullURL = Empty Then Exit Function
    If Not Left(FullURL, 7) = "http://" Then Exit Function
    If Destination = Empty Then Exit Function
   On Error Resume Next
    Kill Destination
    On Error GoTo 0
    varTemp = DeleteUrlCacheEntry(FullURL)
    varTemp = URLDownloadToFile(0, FullURL, Destination, 0, 0)
    If varTemp = 0 Then DownloadFromURL = True
End Function

EDIT:

Oh yeah, I forgot, this is "binary" download, and most webservers use Unix style text files, so any text file or CSV that you download will most likely have Unix-style EndOfLine characters, instead of Windows Style.

If you need it, I have a function to convert the EOLs from Unix to Windows.

[Kenneth Hobs]

Here is alternate method for strictly text files.

Sub Test_SourceText()
    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)
    SourceTextToFile url, toFileName
    Shell "notepad " & toFileName, vbNormalFocus
End Sub

Sub SourceTextToFile(url As String, toFile As String)
    ' requires reference to winhttp.dll in Microsoft WinHTTP Services, version 5.1
    Dim Request As New WinHttpRequest
    Dim ff As Integer
    Request.Open "GET", url, False
    Request.Send
    ff = FreeFile
    Open toFile For Output As #ff
    Print #ff, Request.ResponseText
    Close #ff
End Sub

Function URLFilenamePart(sFullname As String) As String
    URLFilenamePart = Mid(sFullname, InStrRev(sFullname, "/") + 1)
End Function

[Nick_London]

Dr K/Kenneth,

Thanks for the code. To be honest I have no idea how I use your code Dr K as it is all functions and I didn't see a reference for the location of the file ( http://www.federalreserve.gov/releas...st/h6hist1.txt). Where do I include the referance to to actual txt/csv file?

Kenneth,

I have no idea how to reference the winhttp.dll in Microsoft WinHTTP Services, version 5.1. Is this something I can do within excel?

To be honest, I'd rather have a procedure that doesn't require configuring other parts of the PC system so that I download the file from any PC without having to worry about changing references or installing new functionality in case that's what I need to do.

Thanks,

Nick

[mdmackillop]

Unsophisticated, but try

 
Option Explicit

Sub gethtmltable()
    Dim objWeb As QueryTable
    With Sheets("Sheet1")
        Set objWeb = .QueryTables.Add( _
        Connection:="URL;http://www.federalreserve.gov/releases/h6/hist/h6hist1.txt", Destination:=.Range("A1"))
        With objWeb
            .WebSelectionType = xlSpecifiedTables
            .WebTables = "1" ' Identify your HTML Table here
            .Refresh BackgroundQuery:=False
            .SaveData = True
        End With
    End With
    Set objWeb = Nothing
End Sub

[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.