Bulk Download of Excel Files

  • Thread starter Thread starter Ken
  • Start date Start date
'add references to
microsoft scripting runtime
microsoft internet controls
ms ado
ms msxml
ms HTML object library

watch out for wrapping.
Tim.

______________________________________________________________
Option Explicit

Sub tester()
GetFiles
"http://www.irs.gov/taxpros/providers/article/0,,id=109942,00.html", ".xls"
End Sub

Sub GetFiles(sURL As String, sExtension As String)

Dim oDoc As New MSHTML.HTMLDocument
Dim iCount As Integer
Dim IE As New InternetExplorer
Dim i As Integer
IE.navigate sURL

Do While IE.ReadyState <> READYSTATE_COMPLETE
Loop

Set oDoc = IE.Document
iCount = 0
For i = 1 To oDoc.links.Length
If oDoc.links(i - 1).href Like "*" & sExtension Then
'MsgBox oDoc.links(i - 1).href & "(" & oDoc.links(i - 1).innerHTML
& ")"
saveFile oDoc.links(i - 1).href, _
ThisWorkbook.Path & "\" & oDoc.links(i - 1).innerHTML &
".xls"
End If
iCount = iCount + 1
If iCount > 3 Then Exit For 'comment out for production
Next i

Set oDoc = Nothing
Set IE = Nothing
Application.StatusBar = False
End Sub

Sub saveFile(sURL As String, sPath As String)
Dim oXHTTP As New MSXML2.XMLHTTP
Dim oStream As New ADODB.Stream
Dim oFSO As New Scripting.FileSystemObject

oXHTTP.Open "GET", sURL, False
oXHTTP.send

oStream.Type = adTypeBinary
oStream.Open
oStream.Write oXHTTP.responseBody
oStream.SaveToFile sPath, adSaveCreateOverWrite
oStream.Close

Set oXHTTP = Nothing
Set oStream = Nothing
Set oFSO = Nothing

End Sub
 
Thanks, but as a VBA beginner I need a little more guidance. I put
everything from "Option Explicit" through the second "End Sub" in a
code window and ran it. I get an error "Compile Error, Argument Not
Optional" and the first two lines under "Option Explicit" are
highlighted. Also, I don't understand the beginning part from "'add
references" to through "ms HTML object library." Thanks.

Ken
 
Ken,

Try

GetFiles _
"http://www.irs.gov/taxpros/providers/article/0,,id=109942,00.htm
l", ".xls"

The code should all be on a single line of code, or broken in to
multiple lines with the " _" characters.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




Ken said:
Thanks, but as a VBA beginner I need a little more guidance. I put
everything from "Option Explicit" through the second "End Sub" in a
code window and ran it. I get an error "Compile Error, Argument Not
Optional" and the first two lines under "Option Explicit" are
highlighted. Also, I don't understand the beginning part from "'add
references" to through "ms HTML object library." Thanks.

Ken



"Tim Williams" <saxifrax at pacbell dot net> wrote in message
Click to expand...
"http://www.irs.gov/taxpros/providers/article/0,,id=109942,00.htm
l", ".xls"
 
Ken,

To add references, go to Tools-->References in the VB editor. Find
each of the listed item and put a check next to it.

I'll send you a copy of my test workbook to the address
(e-mail address removed).
If that's not your address then drop me a line at: saxifrax at pacbell
dot net

Tim.
 
Back
Top