Fetch CSV that is downloaded via an url

[tetsii]

Hey all,

How do I get the underlying downloadable file downloaded, instead of the response html, when using winhttpreq?

I have a URL (seen in the code below) that I can download a CSV file from. However, the url itself returns html when opened in code. When simply opening the link manually, I get a normal "Save" popup from IE. I have tried some really sketchy solutions with using an IE object and then adding lines to press save etc, but it does not seem even remotely close to a proper solution. Currently I would use the code below:

Sub TestDL()
Dim myURL As String
Dim oStream As Variant
myURL = "[URL]https://transparency.entsoe.eu/generation/r2/actualGenerationPerGenerationUnit/export?type=CSV&dataItems%5BACTUAL_GENERATION_OUTPUT_PER_UNIT%5D=38W-MT--BEJG10-5&intervalStart=2015-11-30T00%3A00%3A00.000%2B01%3A00&intervalEnd=2015-12-10T00%3A00%3A00.000%2B01%3A00&timeZoneId=CET&areaType=unknown[/URL]"
Dim WinHttpReq As Object
Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
WinHttpReq.Open "GET", myURL, False
WinHttpReq.sEnd
myURL = WinHttpReq.responseBody
If WinHttpReq.Status = 200 Then
    Set oStream = CreateObject("ADODB.Stream")
    oStream.Open
    oStream.Type = 1
    oStream.Write WinHttpReq.responseBody
    oStream.SaveToFile "C:\temp\file.csv", 2 ' 1 = no overwrite, 2 = overwrite
    oStream.Close
End If
End Sub

However, as mentioned, the saved file.csv is just filled with html instead of the file that is requested. If someone is willing to help, creating an account to the site is free and very quick.

Thanks!

Best regards,
Tetsii

 

[Fishboy]

Hey all,

How do I get the underlying downloadable file downloaded, instead of the response html, when using winhttpreq?

I have a URL (seen in the code below) that I can download a CSV file from. However, the url itself returns html when opened in code. When simply opening the link manually, I get a normal "Save" popup from IE. I have tried some really sketchy solutions with using an IE object and then adding lines to press save etc, but it does not seem even remotely close to a proper solution. Currently I would use the code below:

Sub TestDL()
Dim myURL As String
Dim oStream As Variant
myURL = "[URL]https://transparency.entsoe.eu/generation/r2/actualGenerationPerGenerationUnit/export?type=CSV&dataItems%5BACTUAL_GENERATION_OUTPUT_PER_UNIT%5D=38W-MT--BEJG10-5&intervalStart=2015-11-30T00%3A00%3A00.000%2B01%3A00&intervalEnd=2015-12-10T00%3A00%3A00.000%2B01%3A00&timeZoneId=CET&areaType=unknown[/URL]"
Dim WinHttpReq As Object
Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
WinHttpReq.Open "GET", myURL, False
WinHttpReq.sEnd
myURL = WinHttpReq.responseBody
If WinHttpReq.Status = 200 Then
    Set oStream = CreateObject("ADODB.Stream")
    oStream.Open
    oStream.Type = 1
    oStream.Write WinHttpReq.responseBody
    oStream.SaveToFile "C:\temp\file.csv", 2 ' 1 = no overwrite, 2 = overwrite
    oStream.Close
End If
End Sub

However, as mentioned, the saved file.csv is just filled with html instead of the file that is requested. If someone is willing to help, creating an account to the site is free and very quick.

Thanks!

Best regards,
Tetsii

Hi Tetsii,

I used to use a very similar macro to download a CSV file from the internet and when I dug it out and compared it to yours there really wasn't much different (apart from a check I carry out at the start that the save location exists otherwise it is created).

i have tweaked my code to reflect the details from your code, (file name, save location etc). Try this out and see it it works any better:

Sub Download_CSV()
Application.ScreenUpdating = False
    Dim fsoFSO
    Set fsoFSO = CreateObject("Scripting.FileSystemObject")
    If fsoFSO.FolderExists("C:\Temp") Then
    Else
        fsoFSO.CreateFolder ("C:\Temp")
    End If
    Dim myURL As String
    myURL = "https://transparency.entsoe.eu/gene...:00.000+01:00&timeZoneId=CET&areaType=unknown"
    
    Dim WinHttpReq As Object
    Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
    WinHttpReq.Open "GET", myURL, False
    WinHttpReq.Send
    
    myURL = WinHttpReq.ResponseBody
    If WinHttpReq.Status = 200 Then
        Set oStream = CreateObject("ADODB.Stream")
        oStream.Open
        oStream.Type = 1
        oStream.Write WinHttpReq.ResponseBody
        oStream.SaveToFile ("C:\Temp\File.csv"), 2
        oStream.Close
    End If
End Sub

 

[John_w]

the saved file.csv is just filled with html instead of the file that is requested

You probably need to emulate the site's log in process in a similar way using GET/POST requests to establish a connection session for the file download request.