Solved: Downloading XLS files from Web page

[raykon]

Hi guys,

I'm trying to figure out to use Excel VBA to look for and download each of the XLS files at http://www.census.gov/popest/cities/tables/ and I can't seem to get it to work without, say, creating an array that specifically names each of the files that I want.

Is there a way to use Excel VBA to simply download any and all files with an .xls extension, without having to know the exact file names in advance?

Thanks, again!

[vonpookie]

I have never tried looping through the links on a page myself, but maybe this would help?
http://www.dailydoseofexcel.com/archives/2004/09/22/automating-internet-explorer/

[tstom]

Assuming that you have a drive 'C'. Will create a folder named www.census.gov (http://www.census.gov) which will contain all of the files that are downloaded. Download and run the attached...


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
Sub RunThis()
Dim IE As Object, Links As Object, Link As Object, r As Range

Set IE = CreateObject("Internetexplorer.Application")

CreateObject("Scripting.FileSystemObject").createfolder "C:\www.census.gov"

IE.Navigate "http://www.census.gov/popest/cities/tables/"

Do Until IE.ReadyState = 4
DoEvents
Loop

Set r = [a1]

For Each Link In IE.Document.Links
If Right(Link.href, 4) = ".xls" Then
r = Link.href

If URLDownloadToFile(0, Link.href, Replace(Link.href, _
"http://www.census.gov/popest/cities/tables/", _
"C:\www.census.gov\"), 0, 0) = 0 Then

r.Offset(, 1) = "ok"
Else
r.Offset(, 1) = "failed"
End If

Set r = r.Offset(1)
End If
Next

IE.Quit

End Sub

[raykon]

Thank you, tstom,

Your coding worked beautifully and I can't thank you enough for saving me many hours of work.

For anyone who's reading this, tstom's coding can be modifiied easily and quickly to allow you to download pdf, htm, and csv files, etc.

Thanks again, tstom.

- Ray