Problem with webquery in VBA
 

Hello,

I've a little problem with creating webqueries and handling the
resulting data with a VBA macro. The problem is that the statements
after the webquery are not effectively handled.

To investigate the issue I created 2 macro's. The first macro
refreshes the webquery. The second macro handles the data retrieved by
the webquery. If I start macro1 it refreshes the query data as
intended. When macro1 is finished I manually start macro2 which
handles the retrieved data exactly as intended. Then I created a third
macro. The only thing this third macro does is start macro1 and then
start macro2. The result is that effectively only macro1 runs. If I
watch the screen closely however it looks like first macro1 obtains
the data from the web, then macro2 runs on the old data and then, on
ending macro3, the data obtained with macro1 is saved in the
worksheet.

In short, I've the impression that Excel refreshes the webquery
correctly, but only saves the obtained data when all macro's are
finished.

Is there any-one who knows a solution for this issue?

grtnx
Wilco

Problem with webquery in VBA
 

It might be that the querytable is configured to refresh in the background -
control is passed back to the next line of code before the query finishes
refreshing.

When running the Refresh method, try something like:
qtb.Refresh BackgroundQuery:=False

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Wilco" wrote in message
om...
Hello,

I've a little problem with creating webqueries and handling the
resulting data with a VBA macro. The problem is that the statements
after the webquery are not effectively handled.

To investigate the issue I created 2 macro's. The first macro
refreshes the webquery. The second macro handles the data retrieved by
the webquery. If I start macro1 it refreshes the query data as
intended. When macro1 is finished I manually start macro2 which
handles the retrieved data exactly as intended. Then I created a third
macro. The only thing this third macro does is start macro1 and then
start macro2. The result is that effectively only macro1 runs. If I
watch the screen closely however it looks like first macro1 obtains
the data from the web, then macro2 runs on the old data and then, on
ending macro3, the data obtained with macro1 is saved in the
worksheet.

In short, I've the impression that Excel refreshes the webquery
correctly, but only saves the obtained data when all macro's are
finished.

Is there any-one who knows a solution for this issue?

grtnx
Wilco