How can I force the VBA script in Excel to wait till an Excel add-in function
completes retrieving data asynchronously from an external data source. I
tried Msgbox statement; but the OK button becomes active before the data
retrieval is completed.

Do you have a way of knowing when the data retrieval has been completed?
If so, something like

'Retrieve data

Do
DoEvents
Loop Until AllDataReceivedTest

Venu wrote:
How can I force the VBA script in Excel to wait till an Excel add-in function
completes retrieving data asynchronously from an external data source. I
tried Msgbox statement; but the OK button becomes active before the data
retrieval is completed.

It is not possible to know whether data retrieval has been completed; data
just gets populated in various cells. Excel status bar shows the progress
such as "Calculating Cells : 75%".

"Gareth" wrote:

Do you have a way of knowing when the data retrieval has been completed?
If so, something like

'Retrieve data

Do
DoEvents
Loop Until AllDataReceivedTest

Venu wrote:
How can I force the VBA script in Excel to wait till an Excel add-in function
completes retrieving data asynchronously from an external data source. I
tried Msgbox statement; but the OK button becomes active before the data
retrieval is completed.

Providing Excel doesn't finish calculating until the data transfer has
finished... you could do something like:

'Wait until sheet finishes calculating
do
Doevents
loop until Application.CalculationState < xlDone

That should wait until it's finished calulating. Of course, you need to
ensure it starts calculating in the first place. Although it seems to be
working now you might like to throw in an

Application.calculation = xlCalculationAutomatic

or at least check it is in your code before launching the import

or maybe check the current settting, set it to automatic and then change
back if necessary.

I haven't tried all this but I hope it works.

Gareth

Venu wrote:
It is not possible to know whether data retrieval has been completed; data
just gets populated in various cells. Excel status bar shows the progress
such as "Calculating Cells : 75%".

"Gareth" wrote:


Do you have a way of knowing when the data retrieval has been completed?
If so, something like

'Retrieve data

Do
DoEvents
Loop Until AllDataReceivedTest

Venu wrote:

How can I force the VBA script in Excel to wait till an Excel add-in function
completes retrieving data asynchronously from an external data source. I
tried Msgbox statement; but the OK button becomes active before the data
retrieval is completed.

I tried the logic you had suggested; but the control comes out of the do
loop before the data retrieval is completed; Excel calculation may be
completing before the data transfer is over. Could you please provide any
other option ?

Venu

"Gareth" wrote:

Providing Excel doesn't finish calculating until the data transfer has
finished... you could do something like:

'Wait until sheet finishes calculating
do
Doevents
loop until Application.CalculationState < xlDone

That should wait until it's finished calulating. Of course, you need to
ensure it starts calculating in the first place. Although it seems to be
working now you might like to throw in an

Application.calculation = xlCalculationAutomatic

or at least check it is in your code before launching the import

or maybe check the current settting, set it to automatic and then change
back if necessary.

I haven't tried all this but I hope it works.

Gareth

Venu wrote:
It is not possible to know whether data retrieval has been completed; data
just gets populated in various cells. Excel status bar shows the progress
such as "Calculating Cells : 75%".

"Gareth" wrote:


Do you have a way of knowing when the data retrieval has been completed?
If so, something like

'Retrieve data

Do
DoEvents
Loop Until AllDataReceivedTest

Venu wrote:

How can I force the VBA script in Excel to wait till an Excel add-in function
completes retrieving data asynchronously from an external data source. I
tried Msgbox statement; but the OK button becomes active before the data
retrieval is completed.

Umm.... without knowing more about the retrieval process it's difficult
to say.

- Is this an AddIn you wrote (and therefore have access to the code)?

- Where is data being retrieved from and how?

- What code is running that need to wait? Is it some code that calls
the retrieval process? An event that runs?

G

Venu wrote:
I tried the logic you had suggested; but the control comes out of the do
loop before the data retrieval is completed; Excel calculation may be
completing before the data transfer is over. Could you please provide any
other option ?

Venu

"Gareth" wrote:


Providing Excel doesn't finish calculating until the data transfer has
finished... you could do something like:

'Wait until sheet finishes calculating
do
Doevents
loop until Application.CalculationState < xlDone

That should wait until it's finished calulating. Of course, you need to
ensure it starts calculating in the first place. Although it seems to be
working now you might like to throw in an

Application.calculation = xlCalculationAutomatic

or at least check it is in your code before launching the import

or maybe check the current settting, set it to automatic and then change
back if necessary.

I haven't tried all this but I hope it works.

Gareth

Venu wrote:

It is not possible to know whether data retrieval has been completed; data
just gets populated in various cells. Excel status bar shows the progress
such as "Calculating Cells : 75%".

"Gareth" wrote:



Do you have a way of knowing when the data retrieval has been completed?
If so, something like

'Retrieve data

Do
DoEvents
Loop Until AllDataReceivedTest

Venu wrote:


How can I force the VBA script in Excel to wait till an Excel add-in function
completes retrieving data asynchronously from an external data source. I
tried Msgbox statement; but the OK button becomes active before the data
retrieval is completed.