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?
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.