excelvbawinhttprequest

WinHTTPRequest Returning Empty Response Text and Body

I'm having trouble getting response text and a response body returned when I run the code below. The "HTTP/1.1 200 OK" message comes back along with response headers, but no response body. I've confirmed this result using Fiddler2 and also looking a netsh trace log.

Other URLs (http://real-chart.finance.yahoo.com/table.csv?s=CELG&d=6&e=26&f=2014&g=d&a=2&b=26&c=1990&ignore=.csv) for example, do return response text as well as a response body.

Why is there a problem with this URL and how can I get it to return a response body?

Sub testlogin()

    fileUrl = "http://financials.morningstar.com/ajax/ReportProcess4CSV.html?t=XNYS:HFC&region=USA&culture=en-US&productCode=COM&reportType=is&period=&dataType=A&order=desc&columnYear=5&rounding=3&view=raw"

    Set WHTTP = CreateObject("WinHTTP.WinHTTPrequest.5.1")

    WHTTP.Open "GET", fileUrl, False

    WHTTP.Send

    MsgBox WHTTP.Status
    MsgBox WHTTP.ResponseText
    MsgBox WHTTP.ResponseBody
    MsgBox WHTTP.GetAllResponseHeaders

    Set WHTTP = Nothing

End Sub
Solution

  • Have you studied those response headers that are returned by the GET calls to both URLs?

    Morningstar is like this:

    Cache-Control: max-age=0
Connection: keep-alive
Date: Sat, 26 Jul 2014 22:07:33 GMT
Pragma: no-cache
Content-Length: 0
===>> Content-Type: text/html;charset=UTF-8 <<===
===>> Content-Encoding: gzip <<===
Server: Apache
Set-Cookie: JSESSIONID=6FAF41A612ABB32B0C670AB07BF0D8A5; HttpOnly
Vary: User-Agent
Vary: Accept-Encoding
com.coradiant.appvis: vid=ad&sid=CONTROLLER_1&tid=da615c36-2a18-4129-bcd7-1cbb139ab52b
Content-Disposition: attachment;filename=""HFC Income Statement.csv""
ExpiresDefault: access plus 2 hours

    Yahoo Finance is like this:

    Cache-Control: private
Connection: close
Date: Sat, 26 Jul 2014 22:10:00 GMT
Transfer-Encoding: chunked
===>> Content-Type: text/csv <<===
P3P: policyref=""http://info.yahoo.com/w3c/p3p.xml"", CP=""CAO DSP COR CUR ADM DEV TAI PSA PSD IVAi IVDi CONi TELo OTPi OUR DELi SAMi OTRi UNRi PUBi IND PHY ONL UNI PUR FIN COM NAV INT DEM CNT STA POL HEA PRE LOC GOV""
Set-Cookie: B=d3svnbl9t89po&b=3&s=4i; expires=Tue, 26-Jul-2016 22:10:00 GMT; path=/; domain=.yahoo.com
Vary: Accept-Encoding

    I've sort-of highlighted the Content-Type and Content-Encoding headers (where available).

    Basically, the content returned is different for the two calls. Clearly, Excel can interpret the second case where the content type is "text/csv" but the first one is a strange gzipped html page that I guess Excel can't understand.

    I can't possibly give you a solution to this issue, but the content of the headers could certainly explain the difference in behaviour you're seeing.