Bulk Download of Excel Files
 

Is there a way to do a bulk download of Excel files on a web page to a
single folder on my hard drive? The files I want to download are at:
http://www.irs.gov/taxpros/providers...109942,00.html

Ken

Bulk Download of Excel Files
 

'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


"Ken" wrote in message
om...
Is there a way to do a bulk download of Excel files on a web page to a
single folder on my hard drive? The files I want to download are at:
http://www.irs.gov/taxpros/providers...109942,00.html

Ken

Bulk Download of Excel Files
 

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 ...
'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


"Ken" wrote in message
om...
Is there a way to do a bulk download of Excel files on a web page to a
single folder on my hard drive? The files I want to download are at:
http://www.irs.gov/taxpros/providers...109942,00.html

Ken

Bulk Download of Excel Files
 

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" wrote in message
om...
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
...
'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.htm
l", ".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


"Ken" wrote in message
om...
Is there a way to do a bulk download of Excel files on a
web page to a
single folder on my hard drive? The files I want to
download are at:

http://www.irs.gov/taxpros/providers...109942,00.html

Ken

Bulk Download of Excel Files
 

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
.
If that's not your address then drop me a line at: saxifrax at pacbell
dot net

Tim.




"Ken" wrote in message
om...
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
...
'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


"Ken" wrote in message
om...
Is there a way to do a bulk download of Excel files on a web
page to a
single folder on my hard drive? The files I want to download
are at:

http://www.irs.gov/taxpros/providers...109942,00.html

Ken