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.

[vba]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
[/vba]


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.
[VBA]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[/VBA]

[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
[VBA]
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

[/VBA]

[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.
[vba]'=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[/vba]
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.
[vba]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
[/vba]
Mdmackillop's QueryTable method might be best if you wanted to import it to Excel directly.