3

I am using Excel VBA's WebQuery, using something similar to this example: (This example is actually copy-pasted from here: http://support.microsoft.com/kb/213730 and I am using a different URL with different POST arguments)

Sub URL_Post_Query()

With ActiveSheet.QueryTables.Add(Connection:= _
    "URL;http://webservices.pcquote.com/cgi-bin/excel.exe", _
    Destination:=Range("a1"))

    .PostText = _
        "QUOTE0=[""QUOTE0"",""Enter up to 20 symbols separated " & _
              "by spaces.""]"

   .BackgroundQuery = True
    .TablesOnlyFromHTML = True
    .Refresh BackgroundQuery:=False
    .SaveData = True
End With
End Sub

I need to be able to make particularly large queries (for example, let's say I'm getting stock price information for 20,000 securities), and when I do so, Excel "locks up" and doesn't display any progress while it is working, although the query does successfully complete.

Using this syntax, is there any way to get access to the stream of data, as it is coming in? This would help in 2 ways: I could process data in smaller chunks as it is received, and I could create a progress meter to display to the user.

Thanks.

Josh
  • 4,412
  • 7
  • 38
  • 41

3 Answers3

1

If you want full access to the results of the web call, you could open the URL as another workbook instead of using a Web Query.

See my answer on this question: How can I post-process the data from an Excel web query when the query is complete?

The tradeoff of that approach is you have to manage processing the data you get back yourself - Excel won't put it in a given destination for you. But it sounds like that's maybe ok with you?

It's a synchronous call, but you do get a progress bar, so the user experience is better.

We ended up going this route after we tried something pretty similar to what you seem to have been doing.

Community
  • 1
  • 1
mcw
  • 3,500
  • 1
  • 31
  • 33
  • Not a bad hack.. Where were you 4 years ago when I was working on this project? :) (We ended up going in an entirely different direction) – Josh Oct 06 '15 at 16:41
  • Ha! Sorry :) Turns out I was running in the same direction, just, oh, 4 years or so behind you. – mcw Oct 06 '15 at 19:02
  • Dunno if you saw, but someone actually came back with [the real answer](http://stackoverflow.com/a/32975304/3782) to this question. Go figure. :) – mcw Oct 07 '15 at 14:27
1

If you want more control, you could switch from the web query to something like using an instance of xmlhttp: this can be used asynchronously (not super-simple, but quite do-able):

http://www.dailydoseofexcel.com/archives/2006/10/09/async-xmlhttp-calls/

Note there's no guarantee that when you send a large block of symbols the webservice returns the response for each symbol individually: it's common for servers to buffer content until the entire response is complete.

You might be better off chunking your quotes into multiple requests, each with a smaller number of symbols.

Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • I knew about this option but I was hoping that the WebQuery option would be simpler. Not-so-much. For my purposes, though, I can't break up the request into smaller pieces (I don't control the requests), and was willing to parse it bit-by-bit as it came in. Looks like the XMLHTTP object doesn't allow you to look at the responseText field while it is being populated though. So I will just have to have a "fake" progress bar instead of a precise one, and I will have to blame the users if their responses take up too much memory and crash the script. – Josh Aug 12 '11 at 18:12
0

If you are working with a large XML document then you might find that processing the results via SAX rather than DOM makes things less prone to freezing up. You might find this answer to a related question to be of use

Community
  • 1
  • 1
barrowc
  • 10,444
  • 1
  • 40
  • 53