Automating download of csv files from web

[robs3131]

Hi,

I wanted to get direction on how to automate the dowload of CSV files from a
website. Below are the details on where the CSV files are located within the
website:

- The website requires a login and password
- There is one web page within the site that has 50 or so links which when
clicked on, result in data being displayed in a tabular format. On this
output page, there is also a "Download File" link which allows you to
download the data in CSV format.
- I have created a .xls file that I would like to create a macro within so
that it can do the following:
1 - Automate the clicking on each of the 50 links which contain the
tabular data
2 - Automate the clicking of the "Download Data" link which appears on
the output page of each of the 50 aforementioned links (note that when this
link is clicked, it gives you three download options, one of which is to
download the file in CSV format, which is the one I would like to have
selected)
3 - When the download button is clicked, automate the clicking of the
"Open" button when the popup comes up asking if you'd like to Open or Save
the file.

All subsequent steps I can handle -- it's just these first three steps where
I'm getting stuck -- and in researching this, unfortunately, I haven't found
any clear direction as of yet.

FYI - I've tried using Web Query to pull the data directly from the table
that shows on the web page when each of the 50 links is clicked, however, the
web query was not able to use the URL I entered I'm guessing either because
of security or because of it's length. Anyway, the 50 or so links on the
page are updated weekly with some new links showing up each week. It seems
that automating the dowload of the CSV files would be a more reliable
solution than using web queries (if not, please don't hesitate to let me know
your opinion!).

Finally, once I develop this spreadsheet, there will be a number of
different users using it -- I'm wondering if the fact that not everyone will
be using Internet Explorer will create a problem in coming up with a solution
here...if so, you can assume I will just create a solution for IE.


Thanks so much in advance for any help you can provide!

 

[Dave Miller]

This question is a very website specific question:

-Is the password a "Pop-up" input box?
if so, your URL should read "HTTP://
username[email protected]"

or embedded in the HTML?
if so, you would need to locate either the name of these text boxes
or the ID to be used in the code:

for example:

Sub SignIn()
Dim ie As Object
Dim sLinks() As String
Dim i As Integer

Set ie = CreateObject("InternetExplorer.Application")

With ie
.Visible = True
.navigate "HTTP://www.d-miller.com"
Do Until .readystate = 4
DoEvents
Loop
With .document
With .Forms(0)
.Item("UserName") = "YourUserName"
.Item("Password") = "YourPassword"
.Submit
End With
Do While ie.Busy
DoEvents
Loop
With .Links
For i = 0 To .Length - 1
ReDim Preserve sLinks(i) As String
sLinks(i) = .Item(i).href
Next
End With
End With

For i = 0 To UBound(sLinks)
.navigate sLinks(i)
Do Until .readystate = 4
DoEvents
Loop
With .document.all
For j = 0 To .Length
With .Item(j)
If .nodeName Like "TABLE" Then
With .Rows
For l = 0 To .Length - 1
With .Item(l).Cells
For c = 0 To .Length - 1
With .Item(c)
ActiveSheet.Cells(l +
1, c + 1) = .innerText
End With
Next
End With
Next
End With
End If
End With
Next j
End With
Next
End With
End Sub

 

[Dave Miller]

This question is a very website specific question with many factors:

-Is the password a "Pop-up" input box?
if so, your URL should read "HTTP://
username[email protected]"

or embedded in the HTML?
if so, you would need to locate either the name of these text boxes
or the ID to be used in the code:

for example:

Sub SignIn()
Dim ie As Object
Dim sLinks() As String
Dim i As Integer

Set ie = CreateObject("InternetExplorer.Application")

With ie
.Visible = True
.navigate "HTTP://www.YourSiteNameHere.com"
Do Until .readystate = 4
DoEvents
Loop
With .document
With .Forms(0)
.Item("UserName") = "YourUserName"
.Item("Password") = "YourPassword"
.Submit
End With
Do While ie.Busy
DoEvents
Loop
With .Links
For i = 0 To .Length - 1
ReDim Preserve sLinks(i) As String
sLinks(i) = .Item(i).href
Next
End With
End With

For i = 0 To UBound(sLinks)
.navigate sLinks(i)
Do Until .readystate = 4
DoEvents
Loop
With .document.all
For j = 0 To .Length
With .Item(j)
If .nodeName Like "TABLE" Then
With .Rows
For l = 0 To .Length - 1
With .Item(l).Cells
For c = 0 To .Length - 1
With .Item(c)
ActiveSheet.Cells(l +
1, _
c +
1) = _
.innerText
End With
Next
End With
Next
End With
End If
End With
Next j
End With
Next
End With
End Sub

 

[robs3131]

Thanks so much Dave! I'm going to spend some time with this and will enter
another post if there is something I can't figure out. Thanks for your input!