VBA - automate download of dynamicly generated csv from website

XLSNoobz

New Member
Joined
Apr 9, 2014
Messages
1
Hello,

Task:
Using vba, automate the download of csv files from the following website: Current and Historical Alberta Weather Station Data Viewer
In order to manually download a csv, once at this URL, select a weather station from the drop down on the right, let's say "Calgary Int'l Cr10", check off a data timeseries to include such as Precipitation (mm), and then click Download at the bottom of the page.

Problem 1:
The exact URL that triggers as CSV download, actually triggers the dynamic creation of the file for download, and javascript is used to POST the resulting CSV back to the user in the form of a download prompt.

Problem 2:
The exact URL that triggers the csv download includes a session ID which is found in the header of the URL I posted above, so I need to retrieve that and incorporate it into the solution.

Example URL to trigger CSV creation & download prompt:
http://agriculture.alberta.ca/acis/weather-data/timeseries?stations=2049&elements=PRA,PRCIP&startdate=20130101&enddate=20130131&interval=daily&format=csv&session=Zkkvk5fT9y4b7otzsx-jtTD&precipunit=inch&comment=true

What I've Tried:
I have tried various samples of code all of which arrive at the same result of responding back with an httpRequest.Status of 307 - temporary redirect and an httpRequest.responsetext of the following:


<title>Redirect</title>

<big>Redirect (authentication_redirect_to_virtual_host)</big>

You are being redirected to the authentication virtual host.

For assistance, contact your network support team.

<tbody>
</tbody>

The code I used for the above example is as follows:

Code:
Public PageSource As String
Public httpRequest As Object
Code:
Public Function URLSource(ByVal URL As String, Optional AllowRedirects As Boolean)


    Dim GetURLStatus As Variant
    
    Const WinHttpRequestOption_UserAgentString = 0
    Const WinHttpRequestOption_EnableRedirects = 6

    On Error Resume Next
    Set httpRequest = CreateObject("WinHttp.WinHttpRequest.5.1")
    If httpRequest Is Nothing Then
        Set httpRequest = CreateObject("WinHttp.WinHttpRequest.5")
    End If
    Err.Clear
    On Error GoTo 0

    httpRequest.Option(WinHttpRequestOption_UserAgentString) = "Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.0)"
    httpRequest.Option(WinHttpRequestOption_EnableRedirects) = AllowRedirects

    'Clear any pervious web page source information
    PageSource = ""

    'Add protocol if missing
    If InStr(1, URL, "://") = 0 Then
        URL = "http://" & URL
    End If

    'Launch the HTTP httpRequest synchronously
    On Error Resume Next
    httpRequest.Open "GET", URL, False
    httpRequest.setRequestHeader "Session", "Zkkvk5fT9y4b7otzsx-jtTD" ' Just recently tried this, but did not help the outcome
    'httpRequest.send
    If Err.Number <> 0 Then
      'Handle connection errors
        GetURLStatus = Err.Description
        Err.Clear
        Exit Function
    End If
    On Error GoTo 0

    'Send the http httpRequest for server status
    On Error Resume Next
    httpRequest.send
    httpRequest.WaitForResponse
    If Err.Number <> 0 Then
      ' Handle server errors
        PageSource = "Error"
        GetURLStatus = Err.Description
        Err.Clear
    Else
      'Show HTTP response info
        GetURLStatus = httpRequest.Status & " - " & httpRequest.StatusText
      'Save the web page text
        URLSource = httpRequest.responsetext
    End If
    On Error GoTo 0
End Function
I would be very appreciative of anyone's suggestions as to how to address the automated download of these dynamically generated CSV files; so I can continue my work, but also nothing better than learning something new :).

Thank you,

Greg
 
Last edited:

rwhawell

New Member
Joined
Jan 22, 2014
Messages
2
Hi,

I'm trying to do a very similar thing - although actually starting from scratch so your post has been really helpful.

I was wondering whether you'd be better off with a website such as: Weather History for London, United Kingdom | Weather Underground

As with this site you won't need to click what you want in the download you'll just need to select the date etc.

I understand you posted this awhile ago, so if you have resolved this issue I'd greatly appreciate you sharing your new found wisedom!

Thanks,
Rebecca
 

Forum statistics

Threads
1,082,281
Messages
5,364,216
Members
400,786
Latest member
ismi88

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top