Web query: Want to pause macro while Web query completes refreshin

E

Ellis

I have built a workbook that uses Web queries to populate pages with
financial data. The data is then used to populate other worksheets which
analyze the fundamentals of a company as a prospective investment.

The Web queries refresh automatically when I change the ticker symbol in the
cell in one worksheet which triggers the refresh process with the new ticker.
This works beautifully when I do one ticker symbol at a time.

However, I have automated the process and a macro has it read down a list of
ticker symbols and retrieve the data. Unfortunately, the refresh process,
triggered as each ticker symbol is read, is too slow; and the macro continues
and posts the data which has not been updated.

I've tried changing the Option in External Data Range Properties by
unchecking the box labeled "Enable Background Refresh." But that does not do
it. I've tried adding code to the macro that makes use of the AfterRefresh
Event to intercept it and hold up the macro's processing until the refresh is
complete. No luck.

I'd be grateful for any help you can give me.

Ellis Traub
 
M

MSweetG222

Try "Sleep"

Place at top of your Module (declare public or private depending on your
needs)...

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)


Place in your code after query code line...

Call Sleep (3000) '(<<< try various #s to get correct # for your needs)


Hope this helps.
 
D

Don Guillett

Post your url(s), code and sample data. You may be able to get in ONE fetch.
You may want to goto the yahoo xltraders group where I (donaldb36) and
others have free files.
 
E

Ellis

Thanks. I'll try it; but I'm not sure the query continues to run while the
macro is.

I did get another suggestion which I tried and it works just fine. It was:

With Sheets("QtrData").QueryTables(1)
.BackgroundQuery = False
.Refresh
End With
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top