0

I have an Excel VBA project that uses MSXML2.XMLHTTP to bring web content into Excel. Everything executes fine, except sometimes I get this error while the script is running:

error dialog

Regardless of which button I click (Yes or No), the script continues to run normally and everything is OK (i.e., all content is properly loaded into Excel). However, I would like to prevent this error from popping up in the first place since it is bad user experience to have this pop up while the macro is running.

The VBA project doesn't even have 344 lines, so I'm not sure which line the error is referring to. Does anybody have a clue how I can fix this, or at least prevent this error dialog from showing up?

Improve this question
5
  • Can you show the code which performs the fetch of the web content and places it in Excel? Is it possible that the error is occurring in the web content ? Commented Nov 1, 2011 at 5:03
  • @TimWilliams You may be right that it's originating from the web since it doesn't seem like a VBA error message. Unfortunately I cannot show the code on here because it's pretty long and complex, but the core idea is from this article: tushar-mehta.com/publish_train/xl_vba_cases/… (XMLHttp section). Commented Nov 1, 2011 at 16:43
  • @TimWilliams The XMLHttp scraping code appears solid -- everything works fine when I fetch about 10 pages once. The runtime error only rears its ugly head when I fetch about 500 pages in a loop (maximum 10 opened connections at a time). Commented Nov 1, 2011 at 16:56
  • Hard to add anything else without seeing any code. I'd be surprised if the error's coming from the web content, since I didn't think xmlhttp runs and scripts... Commented Nov 1, 2011 at 16:56
  • You could try going into your IE settings and making sure script debugging is enabled: that might at least give some idea as to where the error is coming from. Commented Nov 1, 2011 at 17:00

2 Answers 2

Reset to default
1

After manually fetching 10 pages at a time, I finally arrived at the problematic page on the 416th page. It turned out that this one page out of my list of 436 pages return a malformed XMLHttpReq.responseText, so this line was triggering the runtime error for this particular page:

HTMLDoc.body.innerHTML = XMLHttpReq.responseText

To fix this I had to save XMLHttpReq.responseText to a variable, modify the source code so that it's valid HTML, then assign it to HTMLDoc.

Thank you all for those who took the time to reply.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

can you use on error resume next right before the line which may cause this error, then on error goto 0 after you are done? This requires that you identify which call to that XMLHTTP may cause trouble, which, i don't know, debug.print everywhere and see which line can be a trouble?

I am prepared for downvote :p

Improve this answer

2 Comments

The problem is I cannot determine where the error is coming from. It doesn't look like a VBA error; it looks more like an IE control error originating from MSXML2.XMLHTTP.
I see that from your comment, and the MSXML2.XMLHTTP either has bug or you are passing value that the function is not expecting. So, if you call this function only once from VBA, sandwitch that function with two of on error statements. This is like catch exception and silently ignore it in other languages. The reason i said sprinkle with debug.print is that you may use this library many places and not sure which line of your code may cause trouble.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.