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>
<tbody>
</tbody>
The code I used for the above example is as follows:
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
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>
<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>
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: