Fast then slow performance
 

This is suitably vague enough that I don't hold out too much hope for a quick
fix, but perhaps...

I have a procedure that pulls in data from Access, and does a few
calculations and a bit of formatting. Sometimes, when my computer is in a
good mood, the time to run the macro is about 8-10 seconds. Other times it
can take up to two minutes.

Calculation is always set to manual, and it doesn't make a difference if I
have several applications open or nothing but Excel. I don't understand it.
It's easy to pass on to our customer-base if the macros take 8 seconds to
run, but not if they take two minutes.

Oh, when it does take a long time, the bit that takes ages is the pulling
from Access.

If you have any ideas, I would be most grateful.

regards
Daniel

Fast then slow performance
 

Hi Daniel,

See the MSKB Article # 319998
'BUG: Memory leak occurs when you query an open Excel worksheet by using
ActiveX Data Objects (ADO)'

http://support.microsoft.com/default...9998&Product=x


See also:

http://tinyurl.com/dpqqd



---
Regards,
Norman


"Daniel Bonallack" wrote in
message ...
This is suitably vague enough that I don't hold out too much hope for a
quick
fix, but perhaps...

I have a procedure that pulls in data from Access, and does a few
calculations and a bit of formatting. Sometimes, when my computer is in a
good mood, the time to run the macro is about 8-10 seconds. Other times
it
can take up to two minutes.

Calculation is always set to manual, and it doesn't make a difference if I
have several applications open or nothing but Excel. I don't understand
it.
It's easy to pass on to our customer-base if the macros take 8 seconds to
run, but not if they take two minutes.

Oh, when it does take a long time, the bit that takes ages is the pulling
from Access.

If you have any ideas, I would be most grateful.

regards
Daniel

Fast then slow performance
 

Hi Daniel,

You probably have already though of it, but turn of screen refreshing, if
you have not done so already. I had a simple macro which I would run on a
Monday with a date parameter for each day in the previous week, first day
would take 10-15 seconds; subsequent days took 3-4 minutes until I turned of
the screen updating.
--
Tony Green


"Daniel Bonallack" wrote:

This is suitably vague enough that I don't hold out too much hope for a quick
fix, but perhaps...

I have a procedure that pulls in data from Access, and does a few
calculations and a bit of formatting. Sometimes, when my computer is in a
good mood, the time to run the macro is about 8-10 seconds. Other times it
can take up to two minutes.

Calculation is always set to manual, and it doesn't make a difference if I
have several applications open or nothing but Excel. I don't understand it.
It's easy to pass on to our customer-base if the macros take 8 seconds to
run, but not if they take two minutes.

Oh, when it does take a long time, the bit that takes ages is the pulling
from Access.

If you have any ideas, I would be most grateful.

regards
Daniel

Fast then slow performance
 

Thanks Norman and Tony for your help. Yes, screen-updating was off, so that
wasn't the issue. The article was very interesting - thanks for that.
Daniel


"Norman Jones" wrote:

Hi Daniel,

See the MSKB Article # 319998
'BUG: Memory leak occurs when you query an open Excel worksheet by using
ActiveX Data Objects (ADO)'

http://support.microsoft.com/default...9998&Product=x


See also:

http://tinyurl.com/dpqqd



---
Regards,
Norman


"Daniel Bonallack" wrote in
message ...
This is suitably vague enough that I don't hold out too much hope for a
quick
fix, but perhaps...

I have a procedure that pulls in data from Access, and does a few
calculations and a bit of formatting. Sometimes, when my computer is in a
good mood, the time to run the macro is about 8-10 seconds. Other times
it
can take up to two minutes.

Calculation is always set to manual, and it doesn't make a difference if I
have several applications open or nothing but Excel. I don't understand
it.
It's easy to pass on to our customer-base if the macros take 8 seconds to
run, but not if they take two minutes.

Oh, when it does take a long time, the bit that takes ages is the pulling
from Access.

If you have any ideas, I would be most grateful.

regards
Daniel