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

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" <[email protected]> wrote in
message news:[email protected]...
> 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
>
>

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" <[email protected]> wrote in
message news:[email protected]...
> 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
>
>

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

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" <[email protected]> wrote in
> message news:[email protected]...
> > 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
> >
> >
>
>
>