PDA

View Full Version : Solved: Scraping / downloading xls files from Web



raykon
12-01-2006, 01:52 PM
Does anyone have a few lines of Excel VBA coding to download xls files from the Internet?

For example, there are several spreadsheets listed on this Census Bureau Web page at census.gov/population/projections/

Two spreadsheets there are named 01PyrmdAL1.xls and 01PyrmdAL2.xls

So using that as an example, how would I "scrape" or download these spreadsheets to my C: drive?

Thanks again for your help.

Erdin? E. Ka
12-01-2006, 03:32 PM
Hi, :hi:

Analysis thi codes below.


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 DownloadAFileFromWeb()

Dim TheNameOfTheDownloadedFile As String
Dim DirectoryName As String
Dim FullAddressOfTheLink As String
Dim SurNameOfTheDownloadedFile As String

TheNameOfTheDownloadedFile = "Example"
FullAddressOfTheLink = http://www.domainname.com/files/sample.xls
DirectoryName = "C:\"
SurNameOfTheDownloadedFile = ".xls"

If URLDownloadToFile(0, FullAddressOfTheLink, DirectoryName _
& TheNameOfTheDownloadedFile & SurNameOfTheDownloadedFile, 0, 0) = 0 Then
MsgBox "Downloaded to: " & DirectoryName & TheNameOfTheDownloadedFile _
& SurNameOfTheDownloadedFile
End If

End Sub

vzachin
12-01-2006, 03:32 PM
dunno if this will help you; this will open the file. then use the macro recorder to save the file


Sub GetReport4()
Workbooks.Open Filename:= _
"http://www.census.gov/population/projections/01PyrmdAL1.xls"
End Sub



hth
zach

raykon
12-02-2006, 08:55 PM
Hey, thanks vzachin, and thanks yet again Erdin?. I was able to use both of your suggestions to my benefit. Thank you!

Erdin? E. Ka
12-02-2006, 11:55 PM
Hi raykon, i am so happy to help you. Take good care of yourself. :hi:

debauch
03-22-2007, 11:45 AM
Hello -
Similar scenario :
Would it be possible to 'scrape' a .txt file from a FTP site that requires a login and password?

I think it is similar enough to use the existing thread.

debauch
04-08-2007, 04:58 PM
no one?