MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Making VBA in Excel pause for Web Query response


Posted by John Ringleb on December 15, 1999 1:51 PM

I would appreciate it if you could help point me in the right direction. I am attempting to write a spreadsheet to help me track stocks. I want to use the Web Query using the "Detailed Stock Quote by PC Quote, Inc.igy" found under Menu, Data -- Get External Data -- Run Saved Query -- Detailed Stock Quote by PC Quote, Inc.igy. This Query allows you to enter an stock symbol and it returns a table with information on the stock. I am trying to take this data and use it in my spread sheet. I am attempting to write a VBA program that will accomplish this task for a list on stock symbols.

The problem I am having is trying to figure out how to make the program pause, while the Web Query is running. I have tried several dfferent methods from Loop to having Worksheet_Events run when changes occur or spreadsheet is calculated. In all cases the program runs ahead of the query and does not wait for the Query to run.

I would appreciate any assistance you could provide.

jringleb@prodigy.net


Posted by Ivan Moala on December 16, 1999 2:52 AM

Hi John
Have a look @ "BackgroundQuery" setting this to False will
return control to the procedure after your data has been fetched to the worksheet.
True will allow you routines to run asyncronously.
Have a look @ online help re:Refresh(BackgroundQuery)

Ivan

Posted by Scott L. on January 28, 2000 1:29 PM

:Hi, This is what I use.

:Application.OnTime Now + TimeValue("00:02:00"),"NextSub"
:(This delays the code for 2 minutes)

:Hope this helps.
:Scott