Hello,

I have a macro that connects to an orocale database with ODBC and
passes the database a query and the result is that data is pasted into
excel. I then take that pasted data and do all sorts of stuff with
it.

The problem is that the data takes a few (perhaps 2) seconds to appear
in the excel spreadsheet as the connection to the database is verified
and the data retrieved and then the spreadsheet 'refreshed' (?). This
is a problem because the macro runs faster than the database
connection so by the time the macro has executed the data retrieve
code and is on to the data maniluplation code there is no data to work
with and it errors out.

I have got around this by having two macros - the first pulls down the
data and then the second, when i run it manually with a button a few
seconds later, does the stuff with the data but I would liek to
combine this into one macro.

How can I make excel wait until the data has been downloaded? I have
tried to use the wait() command but that seems to put all of excel on
hold (including the data dump sequence - even if i insert the wait()
command after the data should have been successfully dumped into the
sheet).

I guess that i could use a do until loop like this (in psuedo code)

Do until (timer circuit breaker = true) or isempty(activecell.value) =
false
if the loop has run for 10 seconds then there is a problem there
timer circuit breaker = true
loop

Doesnt seem like a very elegant soln.

Help much appreciated.

thank you

The query has a backgroundquery property. Set it to false and the code will
wait for the query to finish. It is a property of the querytable object.

--
Regards,
Tom Ogilvy


"kaiser" wrote:

Hello,

I have a macro that connects to an orocale database with ODBC and
passes the database a query and the result is that data is pasted into
excel. I then take that pasted data and do all sorts of stuff with
it.

The problem is that the data takes a few (perhaps 2) seconds to appear
in the excel spreadsheet as the connection to the database is verified
and the data retrieved and then the spreadsheet 'refreshed' (?). This
is a problem because the macro runs faster than the database
connection so by the time the macro has executed the data retrieve
code and is on to the data maniluplation code there is no data to work
with and it errors out.

I have got around this by having two macros - the first pulls down the
data and then the second, when i run it manually with a button a few
seconds later, does the stuff with the data but I would liek to
combine this into one macro.

How can I make excel wait until the data has been downloaded? I have
tried to use the wait() command but that seems to put all of excel on
hold (including the data dump sequence - even if i insert the wait()
command after the data should have been successfully dumped into the
sheet).

I guess that i could use a do until loop like this (in psuedo code)

Do until (timer circuit breaker = true) or isempty(activecell.value) =
false
if the loop has run for 10 seconds then there is a problem there
timer circuit breaker = true
loop

Doesnt seem like a very elegant soln.

Help much appreciated.

thank you

On Jun 12, 8:09 pm, Tom Ogilvy
wrote:
The query has a backgroundquery property. Set it to false and the code will
wait for the query to finish. It is a property of the querytable object.

--
Regards,
Tom Ogilvy



"kaiser" wrote:
Hello,

I have a macro that connects to an orocale database with ODBC and
passes the database a query and the result is that data is pasted into
excel. I then take that pasted data and do all sorts of stuff with
it.

The problem is that the data takes a few (perhaps 2) seconds to appear
in the excel spreadsheet as the connection to the database is verified
and the data retrieved and then the spreadsheet 'refreshed' (?). This
is a problem because the macro runs faster than the database
connection so by the time the macro has executed the data retrieve
code and is on to the data maniluplation code there is no data to work
with and it errors out.

I have got around this by having two macros - the first pulls down the
data and then the second, when i run it manually with a button a few
seconds later, does the stuff with the data but I would liek to
combine this into one macro.

How can I make excel wait until the data has been downloaded? I have
tried to use the wait() command but that seems to put all of excel on
hold (including the data dump sequence - even if i insert the wait()
command after the data should have been successfully dumped into the
sheet).

I guess that i could use a do until loop like this (in psuedo code)

Do until (timer circuit breaker = true) or isempty(activecell.value) =
false
if the loop has run for 10 seconds then there is a problem there
timer circuit breaker = true
loop

Doesnt seem like a very elegant soln.

Help much appreciated.

thank you- Hide quoted text -

- Show quoted text -

Great! Thanks for the help