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?

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)?

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.

Have you tried with MSXML.Serverxmlhttp?

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?

Like this, assuming NTLM or Negotiate:

Please Login or Register  to view this content.

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?

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.

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

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

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.

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)

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

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.

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?

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.

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

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?