Fetch CSV that is downloaded via an url

tetsii

New Member
Joined
Mar 10, 2014
Messages
4
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:
Code:
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

Well-known Member
Joined
Feb 13, 2015
Messages
4,261
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:
Code:
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:

Rich (BB code):
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/generation/r2/actualGenerationPerGenerationUnit/export?type=CSV&dataItems[ACTUAL_GENERATION_OUTPUT_PER_UNIT]=38W-MT--BEJG10-5&intervalStart=2015-11-30T00:00:00.000+01:00&intervalEnd=2015-12-10T00:00: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

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,001
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.
 

Forum statistics

Threads
1,082,557
Messages
5,366,304
Members
400,882
Latest member
zaldy_a

Some videos you may like

This Week's Hot Topics

Top