VBA - automate download of dynamicly generated csv from website

[XLSNoobz]

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/...T9y4b7otzsx-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>

[TABLE="width: 80%"]
<tbody>[TR]
[TD]
<big>Redirect (authentication_redirect_to_virtual_host)</big>
[/TD]
[/TR]
[TR]
[TD]
You are being redirected to the authentication virtual host.
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[/TR]
[TR]
[TD]For assistance, contact your network support team.
[/TD]
[/TR]
</tbody>[/TABLE]

​

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

Public PageSource As String
Public httpRequest As Object

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

 

[John_w]

You could extract the sessionId string by parsing < SCRIPT> tags with HTMLDocument and HTMLScriptElement from Microsoft HTML Object Library.

Or automate the IE download windows with Windows API functions - see http://www.mrexcel.com/forum/excel-...ile-download-dialog-box-without-sendkeys.html for example code.

 

[Legacy 270740]

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