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

[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

 

[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.

 

[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.

 

[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

 

[Ellis]

Thanks, Don, I think I have this one whipped. I appreciate your interest