+ Reply to Thread
Results 1 to 19 of 19

WinHTTP JSON response error: 'An internal response header size limit was exceeded'

  1. #1
    Forum Expert JasperD's Avatar
    Join Date
    05-07-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    1,393

    WinHTTP JSON response error: 'An internal response header size limit was exceeded'

    Hi guys,

    I'm pulling data from an internal site; it's a straightforward "GET" request in the following format:

    Please Login or Register  to view this content.
    When running this, I get " Run-time error '2147012714(80072f96)': An internal response header size limit was exceeded "
    SetTimeOuts doesn't help.
    Changing it between early or late binding doesn't help.
    The response comes in in chunks, but is small (10KB or so). It's a straightforward JSON string.

    Using URLDownloadtoFileA does work, but ultimately I'd want to do this a-synchronous, so prefer to get WinHTTP working.
    (especially as URLDownloadtoFileA locks excel for about 5~10 seconds for each request, which is annoying).

    Any bright ideas anyone?

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,726

    Re: WinHTTP JSON response error: 'An internal response header size limit was exceeded'

    There's nothing VBA can do. The issue is handled on application/server side.
    Check with your web admin, what limit is imposed on header size, especially since this is "response" header issue and not "request" header issue.

    If using Tomcat, by default, header size is limited to 8192 bytes. Admin should configure maxHttpHeaderSize attribute as needed.
    There are other points where limit can be imposed.

    Have a read of link for more detail.
    https://maxchadwick.xyz/blog/http-re...er-size-limits
    “Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.”
    ― Robert A. Heinlein

  3. #3
    Forum Expert JasperD's Avatar
    Join Date
    05-07-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    1,393

    Re: WinHTTP JSON response error: 'An internal response header size limit was exceeded'

    Hi CK,

    thank you very much for your reply. That's bad news - it will be hard to get this changed as the people maintaining this tool probably have very little interest in me scraping it. How come that URLDownloadToFileA works? Isn't that also simply inet control based (so similar to WinHTTP)?

  4. #4
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,726

    Re: WinHTTP JSON response error: 'An internal response header size limit was exceeded'

    Without knowing how the web site is set up. Hard to say. It could be that server processes GET request using cookie and bloats header element.
    Or it could be result of some other process (such as URL redirect etc).

    I'm not too familiar with how URLDownloadToFileA (Wine API) works. But, I suspect this doesn't use header at all in it's process.

  5. #5
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    2016 Win10
    Posts
    7,176

    Re: WinHTTP JSON response error: 'An internal response header size limit was exceeded'

    Have you tried with MSXML.Serverxmlhttp?

  6. #6
    Forum Expert JasperD's Avatar
    Join Date
    05-07-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    1,393

    Re: WinHTTP JSON response error: 'An internal response header size limit was exceeded'

    How do I authenticate with MSXML2.ServerXMLHTTP60 ?
    I was thinking about it, but that doesn't have "setautologonpolicy" - and isn't ServerXMLHTTP simply XML + WinHTTP?

    And how to do a-sync with MSXML2 ? It doesn't have .waitforresponse either, I believe?

  7. #7
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    2016 Win10
    Posts
    7,176

    Re: WinHTTP JSON response error: 'An internal response header size limit was exceeded'

    Like this, assuming NTLM or Negotiate:
    Please Login or Register  to view this content.

  8. #8
    Forum Expert JasperD's Avatar
    Join Date
    05-07-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    1,393

    Re: WinHTTP JSON response error: 'An internal response header size limit was exceeded'

    Thanks Kyle - so biggest downside I see is having to hard-code the password.
    And is it possible to do this a-sync in a class module?

    I'd assume by setting a class module like this (let's call it 'Kyle123')
    Please Login or Register  to view this content.
    With this in a normal sub:
    Please Login or Register  to view this content.
    but... that doesn't seem to work

    Also - how do I bypass the security warning when opening a httpS site?

  9. #9
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    2016 Win10
    Posts
    7,176

    Re: WinHTTP JSON response error: 'An internal response header size limit was exceeded'

    Hi Jasper,

    Almost, you're missing a (rather obscure) step. You need to make the OnReadyState Change method the default value of the class. To do this, you need to export the class into a text editor and then put Attribute
    Please Login or Register  to view this content.
    directly below Sub ....

    Then import it back into Excel.

  10. #10
    Forum Expert JasperD's Avatar
    Join Date
    05-07-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    1,393

    Re: WinHTTP JSON response error: 'An internal response header size limit was exceeded'

    Thank you very much Kyle, that did the trick.
    I owe you another beer, it seems (about a truck full of them by now).

    I love that it actually works with late binding as well, just setting the object and using 'createobject'.
    Now I (hopefully) have you here anyway, I have two further questions, somewhat related to this topic.

    a) how to prevent the pesky windows (?) security warning when accessing sites via xmlhttp, but, much more importantly
    b) when using scriptcontrol to parse json in an x64 environment (via MSHTA.exe), the json structure won't be visible in the locals window. Is there a way to do this anyway (with this setup)? Or another easy and fast way to create a treeview of the json object in all its nested complexity? If so, I'll open another thread

  11. #11
    Forum Expert JasperD's Avatar
    Join Date
    05-07-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    1,393

    Re: WinHTTP JSON response error: 'An internal response header size limit was exceeded'

    Really though - how do I get rid of this:
    secwarning.jpg

  12. #12
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,726

    Re: WinHTTP JSON response error: 'An internal response header size limit was exceeded'

    That's what's called, “Cross-domain data access” dialog. Happens when a page makes a call that violates same-origin-policy.

    Can you test if you get the same message when you use... "MSXML2.ServerXMLHTTP" instead of "MSXML2.XMLHTTP"?

    If you do.
    Either change IE's security setting or add network location to list of trusted location.

  13. #13
    Forum Expert JasperD's Avatar
    Join Date
    05-07-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    1,393

    Re: WinHTTP JSON response error: 'An internal response header size limit was exceeded'

    ServerXMLHTTP works, but that requires me to login via login & pass, while XMLHTTP uses SSO.
    I can't hard-code a password in as it's a shared tool.

    I thought I remembered there's a way to ignore this / circumvent this, but I could be mistaken.
    I tried using URLOpenStreamA, but I have absolutely no idea how to get the callback for that one.... :S
    https://technet.microsoft.com/en-us/...75129(v=vs.60)

  14. #14
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    2016 Win10
    Posts
    7,176

    Re: WinHTTP JSON response error: 'An internal response header size limit was exceeded'

    Can't you just prompt for username and password?

    For json parsing, I don't use the scripting object anymore, I use https://github.com/VBA-tools/VBA-JSON, it's cross platform and works on 64-bit

  15. #15
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,726

    Re: WinHTTP JSON response error: 'An internal response header size limit was exceeded'

    Have a read of link. Specifically "Security: Cross-Domain and Zone Policy".
    https://msdn.microsoft.com/en-us/lib...).aspx#xdomain

    By adding network location to trusted (or perhaps intranet zone) you should be able to avoid it.
    Or depending on server side set up, you can avoid this issue by sending header info along with your request (exact header needed will depend on specific set up).

    I'd recommend tracing request sent to the server using browser and developer tool. This will tell you a lot about how you should construct your requests.

    Edit: As for JSON parsing. I use PowerQuery. But since you don't have it (Excel 2007), either RegExp code or what Klye mentioned works wonderfully.

  16. #16
    Forum Expert JasperD's Avatar
    Join Date
    05-07-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    1,393

    Re: WinHTTP JSON response error: 'An internal response header size limit was exceeded'

    Thank you both gents, very helpful - much appreciated.

    I will try and prompt for username / password - but the class module must be set up differently in that case (as ServerXMLHTTP =/= XMLHTTP).
    It's annoying though - XMLHTTP gets the data fine, but throws the popup; ServerXMLHTTP needs hard coded (or prompted) authentication, WinHTTP gives me 'response header size too big', and URLDownloadtoFile can't be done a-synchronously, I think.

    I'll check out the requestheaders on to send along with XMLHTTP to see if that's a solution; but I don't have my hopes up.

    RE: JSON, I am actually on Excel 2013 (I need to update that) - but is powerquery a built-in solution? I'll have to look that up.
    I've avoided VBA-JSON so far as it's so bloated. Hundreds of lines of code of what scriptcontrol does in 1 or 2 lines. Scriptcontrol works properly on X64, but I can't see the treeview in the locals window - which is kind of a blocker from time to time.
    How is the speed of going through hundreds of thousands of rows on VBA-JSON?

  17. #17
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,726

    Re: WinHTTP JSON response error: 'An internal response header size limit was exceeded'

    VBA-JSON is pretty fast. I don’t like to use script control, for parsing, as it can run malicious code embedded in response (though shouldn’t be an issue for internal site).

    As for PQ for 2013, I believe you needed ProPlus sku, though it may have changed with recent update.

  18. #18
    Forum Expert JasperD's Avatar
    Join Date
    05-07-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    1,393

    Re: WinHTTP JSON response error: 'An internal response header size limit was exceeded'

    Ah yes, for internal sites, I am happy with 'eval'. For anything external, I use JSON.parse; I pulled the code here through jscompress and embedd it as jscript addcode into my vba code. It gets compressed to 4 lines so quite happy with that.
    My main 'beef' with VBA-JSON is, as it is so much code and I haven't stepped through it yet to see what each part does, I don't quite trust it yet. But not being able to see a treeview in x64 might make me switch

  19. #19
    Forum Expert JasperD's Avatar
    Join Date
    05-07-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    1,393

    Re: WinHTTP JSON response error: 'An internal response header size limit was exceeded'

    Bumping this up - I don't know why I said before that MSXML2.ServerXMLHTTP worked, because now it's throwing me the same error as WinHTTP
    (which is not all that surprising, as serverXMLHTTP is essentially winHTTP in an XML wrapper as far as I know).

    So that leaves me with - XMLHTTP; giving me the lovely error as depicted a few posts back, or URLDownloadtofileA, which won't work a-synchronously (so that's going to take forever...)

    Anything I haven't thought of?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Waiting for WinHTTP response Excel goes to 'not responding' - anything I can do?
    By JasperD in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-28-2020, 06:57 PM
  2. winhttp doesn't deflate gzip response
    By JasperD in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-16-2017, 05:28 AM
  3. Use Excel VBA to Get and parse an API JSON response
    By proverbguy in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-11-2017, 02:56 PM
  4. Allocating data from a multiple response field to individual response filelds
    By Riddles in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-14-2016, 02:15 AM
  5. Error 91- Response button No not working
    By Wags1240 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-04-2016, 07:39 PM
  6. [SOLVED] Change Multipage & UserForm size based on response
    By DHFE in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-26-2015, 12:07 PM
  7. VBA Response to Error Box?
    By blokeyhighlander in forum PowerPoint Formatting & General
    Replies: 3
    Last Post: 09-17-2013, 05:53 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1