Solved: Scraping / downloading xls files from Web

[raykon]

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]

Hi,

Analysis thi codes below.

[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

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

[/VBA]

[vzachin]

dunno if this will help you; this will open the file. then use the macro recorder to save the file

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


hth
zach

[raykon]

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]

Hi raykon, i am so happy to help you. Take good care of yourself.

[debauch]

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]

no one?