Access -> Excel Automation Slow

[Mark Day]

Hi All,
I am using Access 2000 to generate over 40 Excel charts and pivot tables
using early binding to the Excel 9.0 object library. I am finding that if I
show the Excel object while processing the charts, the whole process
completes in around 2 minutes. However if I hide the Excel object the same
process takes around 27 minutes to complete. I have played around with
screen updating and this makes no real difference to the time. Does anybody
have any suggestions how I could cut down the processing time with the Excel
object hidden?

Any help appreciated,
Mark Day.

[TC]

This seems strange. You'd think it would be *slower* when made visible.

Put some timers throughout the automation code, to see if the slowdown is
any in particular place.

dim x as single
x = -timer
(do some stuff here)
x = x + timer
debug.print "took "; x; " seconds"

HTH,
TC
"Mark Day" <ma******@solortec.co.uk> wrote in message
news:bm**********@hercules.btinternet.com...

Hi All,
I am using Access 2000 to generate over 40 Excel charts and pivot tables
using early binding to the Excel 9.0 object library. I am finding that if I show the Excel object while processing the charts, the whole process
completes in around 2 minutes. However if I hide the Excel object the same
process takes around 27 minutes to complete. I have played around with
screen updating and this makes no real difference to the time. Does anybody have any suggestions how I could cut down the processing time with the Excel object hidden?

Any help appreciated,
Mark Day.

[Mark Day]

Hi,
I already have timers in place and there does not appear to be any place in
code that causes this slowdown. If I show the Excel object and then minimise
the window via code, this has the same effect as hiding the Excel object as
far as processing time is concerned. However if the Excel object has the
focus, then the processing time dramaticaly reduces.
Any other ideas??

TIA
Mark Day.

"TC" <a@b.c.d> wrote in message news:1065760695.602269@teuthos...

This seems strange. You'd think it would be *slower* when made visible.

Put some timers throughout the automation code, to see if the slowdown is
any in particular place.

dim x as single
x = -timer
(do some stuff here)
x = x + timer
debug.print "took "; x; " seconds"

HTH,
TC
"Mark Day" <ma******@solortec.co.uk> wrote in message
news:bm**********@hercules.btinternet.com...

Hi All,
I am using Access 2000 to generate over 40 Excel charts and pivot tables
using early binding to the Excel 9.0 object library. I am finding that if

I

show the Excel object while processing the charts, the whole process
completes in around 2 minutes. However if I hide the Excel object the

same process takes around 27 minutes to complete. I have played around with
screen updating and this makes no real difference to the time. Does

anybody

have any suggestions how I could cut down the processing time with the

Excel

object hidden?

Any help appreciated,
Mark Day.

[Alphonse Giambrone]

Sounds like an OS issue with how it handles background tasks.

--

Alphonse Giambrone
Email: a-giam at customdatasolutions dot us
"Mark Day" <ma******@solortec.co.uk> wrote in message
news:bm**********@hercules.btinternet.com...

Hi All,
I am using Access 2000 to generate over 40 Excel charts and pivot tables
using early binding to the Excel 9.0 object library. I am finding that if I show the Excel object while processing the charts, the whole process
completes in around 2 minutes. However if I hide the Excel object the same
process takes around 27 minutes to complete. I have played around with
screen updating and this makes no real difference to the time. Does anybody have any suggestions how I could cut down the processing time with the Excel object hidden?

Any help appreciated,
Mark Day.

[Alphonse Giambrone]

Sounds like an OS issue with how it handles background tasks.
BTW, I would love to see the code for generating the charts in Excel.

--

Alphonse Giambrone
Email: a-giam at customdatasolutions dot us
"Mark Day" <ma******@solortec.co.uk> wrote in message
news:bm**********@hercules.btinternet.com...

Hi All,
I am using Access 2000 to generate over 40 Excel charts and pivot tables
using early binding to the Excel 9.0 object library. I am finding that if I show the Excel object while processing the charts, the whole process
completes in around 2 minutes. However if I hide the Excel object the same
process takes around 27 minutes to complete. I have played around with
screen updating and this makes no real difference to the time. Does anybody have any suggestions how I could cut down the processing time with the Excel object hidden?

Any help appreciated,
Mark Day.

[Mark Day]

Have tried same code on windows XP pro and win 2000 pro although the
processor speeds, ram etc are different on each machine the % of time
difference between executing the code re hiding & showing the Excel object
remain the same. If is is an OS issue I guess it could be a registry tweak
common to both OS's.
BTW the code to create the various charts is to lengthy to post in this
newsgroup.
One function opens & returns an instance of the Excel object, followed by
various functions common to each chart type to manipulate and save the
chart, again followed by a function to close the instance of Excel after all
charts have been saved.

Any further help appreciated.
Mark Day.
www.solortec.co.uk

"Alphonse Giambrone" <NO**********@example.invalid> wrote in message
news:W2**********************@news4.srv.hcvlny.cv. net...

Sounds like an OS issue with how it handles background tasks.
BTW, I would love to see the code for generating the charts in Excel.

--

Alphonse Giambrone
Email: a-giam at customdatasolutions dot us
"Mark Day" <ma******@solortec.co.uk> wrote in message
news:bm**********@hercules.btinternet.com...

Hi All,
I am using Access 2000 to generate over 40 Excel charts and pivot tables
using early binding to the Excel 9.0 object library. I am finding that if

I

show the Excel object while processing the charts, the whole process
completes in around 2 minutes. However if I hide the Excel object the

same process takes around 27 minutes to complete. I have played around with
screen updating and this makes no real difference to the time. Does

anybody

have any suggestions how I could cut down the processing time with the

Excel

object hidden?

Any help appreciated,
Mark Day.

[Alphonse Giambrone]

Makes sense since XP is based on 2K.
Try System Properties > Advanced > Performance Settings > Advanced >
Processor Scheduling > background services.
If that helps, maybe a Windows 2k newsgroup can tell you how to
programatically get a single process to take more processor time.

--

Alphonse Giambrone
Email: a-giam at customdatasolutions dot us
"Mark Day" <ma******@solortec.co.uk> wrote in message
news:bm**********@titan.btinternet.com...

Have tried same code on windows XP pro and win 2000 pro although the
processor speeds, ram etc are different on each machine the % of time
difference between executing the code re hiding & showing the Excel object
remain the same. If is is an OS issue I guess it could be a registry tweak
common to both OS's.
BTW the code to create the various charts is to lengthy to post in this
newsgroup.
One function opens & returns an instance of the Excel object, followed by
various functions common to each chart type to manipulate and save the
chart, again followed by a function to close the instance of Excel after all charts have been saved.

Any further help appreciated.
Mark Day.
www.solortec.co.uk

"Alphonse Giambrone" <NO**********@example.invalid> wrote in message
news:W2**********************@news4.srv.hcvlny.cv. net...

Sounds like an OS issue with how it handles background tasks.
BTW, I would love to see the code for generating the charts in Excel.

--

Alphonse Giambrone
Email: a-giam at customdatasolutions dot us
"Mark Day" <ma******@solortec.co.uk> wrote in message
news:bm**********@hercules.btinternet.com...

Hi All,
I am using Access 2000 to generate over 40 Excel charts and pivot tables using early binding to the Excel 9.0 object library. I am finding that if

I

show the Excel object while processing the charts, the whole process
completes in around 2 minutes. However if I hide the Excel object the

same process takes around 27 minutes to complete. I have played around with
screen updating and this makes no real difference to the time. Does

anybody

have any suggestions how I could cut down the processing time with the

Excel

object hidden?

Any help appreciated,
Mark Day.

[TC]

"Mark Day" <ma******@solortec.co.uk> wrote in message
news:bm**********@titan.btinternet.com...

Hi,
I already have timers in place and there does not appear to be any place in code that causes this slowdown.
Are you saying that there does not appear to be any *one specific* place in
the code that causes the slowdown? That is, all parts of the code slow down
equally? I am wondering whether certain Excel methods or property references
slow down more than others. This would give some clues on the cause.

TC
If I show the Excel object and then minimise
the window via code, this has the same effect as hiding the Excel object as far as processing time is concerned. However if the Excel object has the
focus, then the processing time dramaticaly reduces.
Any other ideas??

TIA
Mark Day.

"TC" <a@b.c.d> wrote in message news:1065760695.602269@teuthos...

This seems strange. You'd think it would be *slower* when made visible.

Put some timers throughout the automation code, to see if the slowdown is

any in particular place.

dim x as single
x = -timer
(do some stuff here)
x = x + timer
debug.print "took "; x; " seconds"

HTH,
TC
"Mark Day" <ma******@solortec.co.uk> wrote in message
news:bm**********@hercules.btinternet.com...

Hi All,
I am using Access 2000 to generate over 40 Excel charts and pivot tables using early binding to the Excel 9.0 object library. I am finding that

if

I

show the Excel object while processing the charts, the whole process
completes in around 2 minutes. However if I hide the Excel object the

same process takes around 27 minutes to complete. I have played around with
screen updating and this makes no real difference to the time. Does

anybody

have any suggestions how I could cut down the processing time with the

Excel

object hidden?

Any help appreciated,
Mark Day.

[Mark Day]

Hi Alphonse,
Have tried optimising for background services, unfortunately this made no
difference.
This is becoming a real pain, any other ideas greatly recieved.

TIA
Mark Day
www.solortec.co.uk
"Mark Day" <ma******@solortec.co.uk> wrote in message
news:bm**********@titan.btinternet.com...

Have tried same code on windows XP pro and win 2000 pro although the
processor speeds, ram etc are different on each machine the % of time
difference between executing the code re hiding & showing the Excel object
remain the same. If is is an OS issue I guess it could be a registry tweak
common to both OS's.
BTW the code to create the various charts is to lengthy to post in this
newsgroup.
One function opens & returns an instance of the Excel object, followed by
various functions common to each chart type to manipulate and save the
chart, again followed by a function to close the instance of Excel after all charts have been saved.

Any further help appreciated.
Mark Day.
www.solortec.co.uk

"Alphonse Giambrone" <NO**********@example.invalid> wrote in message
news:W2**********************@news4.srv.hcvlny.cv. net...

Sounds like an OS issue with how it handles background tasks.
BTW, I would love to see the code for generating the charts in Excel.

--

Alphonse Giambrone
Email: a-giam at customdatasolutions dot us
"Mark Day" <ma******@solortec.co.uk> wrote in message
news:bm**********@hercules.btinternet.com...

Hi All,
I am using Access 2000 to generate over 40 Excel charts and pivot tables using early binding to the Excel 9.0 object library. I am finding that if

I

show the Excel object while processing the charts, the whole process
completes in around 2 minutes. However if I hide the Excel object the

same process takes around 27 minutes to complete. I have played around with
screen updating and this makes no real difference to the time. Does

anybody

have any suggestions how I could cut down the processing time with the

Excel

object hidden?

Any help appreciated,
Mark Day.

[Mark Day]

Hi Tc,
I have a series of functions that create several charts. Each function works
with its own data to create identical charts or pivot tables. The only
variation for the charts within its function is the criteria passed to base
the chart upon. Lets say each function creates 6 charts or pivot tables.
There is obviously a time difference between code execution of each
function, but the 6 charts that are created by any one function do not
differ in time. So in answer to your question, no there does not appear to
be any *one specific* place in the code that causes the slowdown.
Any further help appreciated.

Mark Day
www.solortec.co.uk

"Mark Day" <ma******@solortec.co.uk> wrote in message
news:bm**********@titan.btinternet.com...

Hi,
I already have timers in place and there does not appear to be any place in code that causes this slowdown. If I show the Excel object and then minimise the window via code, this has the same effect as hiding the Excel object as far as processing time is concerned. However if the Excel object has the
focus, then the processing time dramaticaly reduces.
Any other ideas??

TIA
Mark Day.

"TC" <a@b.c.d> wrote in message news:1065760695.602269@teuthos...

This seems strange. You'd think it would be *slower* when made visible.

Put some timers throughout the automation code, to see if the slowdown is

any in particular place.

dim x as single
x = -timer
(do some stuff here)
x = x + timer
debug.print "took "; x; " seconds"

HTH,
TC
"Mark Day" <ma******@solortec.co.uk> wrote in message
news:bm**********@hercules.btinternet.com...

Hi All,
I am using Access 2000 to generate over 40 Excel charts and pivot tables using early binding to the Excel 9.0 object library. I am finding that

if

I

show the Excel object while processing the charts, the whole process
completes in around 2 minutes. However if I hide the Excel object the

same process takes around 27 minutes to complete. I have played around with
screen updating and this makes no real difference to the time. Does

anybody

have any suggestions how I could cut down the processing time with the

Excel

object hidden?

Any help appreciated,
Mark Day.

[TC]

Mark, I think we are talking at cross-purposes.

Say that creating a chart takes something like this:

execute method #1
execute method #2
if property#3 = 123 then
execute method #4
else
execute method#5
endif
& so on.

Say this block of code takes 10 seconds in one case, and 10 minutes in
another (ie. 60 times longer).

This might occur because:

(a) each statement took 60 times longer, or

(b) none of the statement took any longer at all, with the single exception
of the reference to property #3, which took 9 minutes and 50
seconds longer!

Obviously if (b) were the case then this would give a strong clue as to what
is happening. So I am basically dsaying to time *each line*, & see if they
are sall equally slower, or whether some paters emerge. This will not be as
tedious as it sounds, if you knock up some quick & dirty code in a public
sub, then just catter calls to that sub liberally throughout the charting
code.

HTH,
TC
"Mark Day" <ma******@solortec.co.uk> wrote in message
news:bm**********@titan.btinternet.com...

Hi Tc,
I have a series of functions that create several charts. Each function works with its own data to create identical charts or pivot tables. The only
variation for the charts within its function is the criteria passed to base the chart upon. Lets say each function creates 6 charts or pivot tables.
There is obviously a time difference between code execution of each
function, but the 6 charts that are created by any one function do not
differ in time. So in answer to your question, no there does not appear to
be any *one specific* place in the code that causes the slowdown.
Any further help appreciated.

Mark Day
www.solortec.co.uk

"Mark Day" <ma******@solortec.co.uk> wrote in message
news:bm**********@titan.btinternet.com...

Hi,
I already have timers in place and there does not appear to be any place

in

code that causes this slowdown. If I show the Excel object and then

minimise

the window via code, this has the same effect as hiding the Excel object

as

far as processing time is concerned. However if the Excel object has the
focus, then the processing time dramaticaly reduces.
Any other ideas??

TIA
Mark Day.

"TC" <a@b.c.d> wrote in message news:1065760695.602269@teuthos...

This seems strange. You'd think it would be *slower* when made visible.
Put some timers throughout the automation code, to see if the slowdown is any in particular place.

dim x as single
x = -timer
(do some stuff here)
x = x + timer
debug.print "took "; x; " seconds"

HTH,
TC
"Mark Day" <ma******@solortec.co.uk> wrote in message
news:bm**********@hercules.btinternet.com...
> Hi All,
> I am using Access 2000 to generate over 40 Excel charts and pivot tables > using early binding to the Excel 9.0 object library. I am finding that

if

I
> show the Excel object while processing the charts, the whole process
> completes in around 2 minutes. However if I hide the Excel object

the same

> process takes around 27 minutes to complete. I have played around

with > screen updating and this makes no real difference to the time. Does
anybody
> have any suggestions how I could cut down the processing time with the Excel
> object hidden?
>
> Any help appreciated,
> Mark Day.
>
>