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]

Re: Access -> Excel Automation Slow

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" <mark.day@solor tec.co.uk> wrote in message
news:bm4cpk$pd3 $1@hercules.bti nternet.com...[color=blue]
> 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[/color]
I[color=blue]
> 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[/color]
anybody[color=blue]
> have any suggestions how I could cut down the processing time with the[/color]
Excel[color=blue]
> object hidden?
>
> Any help appreciated,
> Mark Day.
>
>[/color]

[Mark Day]

Re: Access -&gt; Excel Automation Slow

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 ...[color=blue]
> 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" <mark.day@solor tec.co.uk> wrote in message
> news:bm4cpk$pd3 $1@hercules.bti nternet.com...[color=green]
> > 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[/color][/color]
if[color=blue]
> I[color=green]
> > show the Excel object while processing the charts, the whole process
> > completes in around 2 minutes. However if I hide the Excel object the[/color][/color]
same[color=blue][color=green]
> > process takes around 27 minutes to complete. I have played around with
> > screen updating and this makes no real difference to the time. Does[/color]
> anybody[color=green]
> > have any suggestions how I could cut down the processing time with the[/color]
> Excel[color=green]
> > object hidden?
> >
> > Any help appreciated,
> > Mark Day.
> >
> >[/color]
>
>[/color]

[Alphonse Giambrone]

Re: Access -&gt; Excel Automation Slow

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

--

Alphonse Giambrone
Email: a-giam at customdatasolut ions dot us


"Mark Day" <mark.day@solor tec.co.uk> wrote in message
news:bm4cpk$pd3 $1@hercules.bti nternet.com...[color=blue]
> 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[/color]
I[color=blue]
> 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[/color]
anybody[color=blue]
> have any suggestions how I could cut down the processing time with the[/color]
Excel[color=blue]
> object hidden?
>
> Any help appreciated,
> Mark Day.
>
>[/color]

[Alphonse Giambrone]

Re: Access -&gt; Excel Automation Slow

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 customdatasolut ions dot us


"Mark Day" <mark.day@solor tec.co.uk> wrote in message
news:bm4cpk$pd3 $1@hercules.bti nternet.com...[color=blue]
> 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[/color]
I[color=blue]
> 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[/color]
anybody[color=blue]
> have any suggestions how I could cut down the processing time with the[/color]
Excel[color=blue]
> object hidden?
>
> Any help appreciated,
> Mark Day.
>
>[/color]

[Mark Day]

Re: Access -&gt; Excel Automation Slow

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.

https://www.solortec.co.uk

"Alphonse Giambrone" <NOSPAMa-giam@example.in valid> wrote in message
news:W2yhb.4044 3$Ri4.16113002@ news4.srv.hcvln y.cv.net...[color=blue]
> 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 customdatasolut ions dot us
>
>
> "Mark Day" <mark.day@solor tec.co.uk> wrote in message
> news:bm4cpk$pd3 $1@hercules.bti nternet.com...[color=green]
> > 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[/color][/color]
if[color=blue]
> I[color=green]
> > show the Excel object while processing the charts, the whole process
> > completes in around 2 minutes. However if I hide the Excel object the[/color][/color]
same[color=blue][color=green]
> > process takes around 27 minutes to complete. I have played around with
> > screen updating and this makes no real difference to the time. Does[/color]
> anybody[color=green]
> > have any suggestions how I could cut down the processing time with the[/color]
> Excel[color=green]
> > object hidden?
> >
> > Any help appreciated,
> > Mark Day.
> >
> >[/color]
>
>
>[/color]

[Alphonse Giambrone]

Re: Access -&gt; Excel Automation Slow

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 customdatasolut ions dot us


"Mark Day" <mark.day@solor tec.co.uk> wrote in message
news:bm6mr0$1uh $1@titan.btinte rnet.com...[color=blue]
> 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[/color]
all[color=blue]
> charts have been saved.
>
> Any further help appreciated.
> Mark Day.
> www.solortec.co.uk
>
>
>
> "Alphonse Giambrone" <NOSPAMa-giam@example.in valid> wrote in message
> news:W2yhb.4044 3$Ri4.16113002@ news4.srv.hcvln y.cv.net...[color=green]
> > 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 customdatasolut ions dot us
> >
> >
> > "Mark Day" <mark.day@solor tec.co.uk> wrote in message
> > news:bm4cpk$pd3 $1@hercules.bti nternet.com...[color=darkred]
> > > Hi All,
> > > I am using Access 2000 to generate over 40 Excel charts and pivot[/color][/color][/color]
tables[color=blue][color=green][color=darkred]
> > > using early binding to the Excel 9.0 object library. I am finding that[/color][/color]
> if[color=green]
> > I[color=darkred]
> > > show the Excel object while processing the charts, the whole process
> > > completes in around 2 minutes. However if I hide the Excel object the[/color][/color]
> same[color=green][color=darkred]
> > > process takes around 27 minutes to complete. I have played around with
> > > screen updating and this makes no real difference to the time. Does[/color]
> > anybody[color=darkred]
> > > have any suggestions how I could cut down the processing time with the[/color]
> > Excel[color=darkred]
> > > object hidden?
> > >
> > > Any help appreciated,
> > > Mark Day.
> > >
> > >[/color]
> >
> >
> >[/color]
>
>[/color]

[TC]

Re: Access -&gt; Excel Automation Slow


"Mark Day" <mark.day@solor tec.co.uk> wrote in message
news:bm5q1o$674 $1@titan.btinte rnet.com...[color=blue]
> Hi,
> I already have timers in place and there does not appear to be any place[/color]
in[color=blue]
> code that causes this slowdown.[/color]

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


[color=blue]
> If I show the Excel object and then minimise
> the window via code, this has the same effect as hiding the Excel object[/color]
as[color=blue]
> 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 ...[color=green]
> > 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[/color][/color]
is[color=blue][color=green]
> > 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" <mark.day@solor tec.co.uk> wrote in message
> > news:bm4cpk$pd3 $1@hercules.bti nternet.com...[color=darkred]
> > > Hi All,
> > > I am using Access 2000 to generate over 40 Excel charts and pivot[/color][/color][/color]
tables[color=blue][color=green][color=darkred]
> > > using early binding to the Excel 9.0 object library. I am finding that[/color][/color]
> if[color=green]
> > I[color=darkred]
> > > show the Excel object while processing the charts, the whole process
> > > completes in around 2 minutes. However if I hide the Excel object the[/color][/color]
> same[color=green][color=darkred]
> > > process takes around 27 minutes to complete. I have played around with
> > > screen updating and this makes no real difference to the time. Does[/color]
> > anybody[color=darkred]
> > > have any suggestions how I could cut down the processing time with the[/color]
> > Excel[color=darkred]
> > > object hidden?
> > >
> > > Any help appreciated,
> > > Mark Day.
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

[Mark Day]

Re: Access -&gt; Excel Automation Slow

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

https://www.solortec.co.uk

"Mark Day" <mark.day@solor tec.co.uk> wrote in message
news:bm6mr0$1uh $1@titan.btinte rnet.com...[color=blue]
> 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[/color]
all[color=blue]
> charts have been saved.
>
> Any further help appreciated.
> Mark Day.
> www.solortec.co.uk
>
>
>
> "Alphonse Giambrone" <NOSPAMa-giam@example.in valid> wrote in message
> news:W2yhb.4044 3$Ri4.16113002@ news4.srv.hcvln y.cv.net...[color=green]
> > 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 customdatasolut ions dot us
> >
> >
> > "Mark Day" <mark.day@solor tec.co.uk> wrote in message
> > news:bm4cpk$pd3 $1@hercules.bti nternet.com...[color=darkred]
> > > Hi All,
> > > I am using Access 2000 to generate over 40 Excel charts and pivot[/color][/color][/color]
tables[color=blue][color=green][color=darkred]
> > > using early binding to the Excel 9.0 object library. I am finding that[/color][/color]
> if[color=green]
> > I[color=darkred]
> > > show the Excel object while processing the charts, the whole process
> > > completes in around 2 minutes. However if I hide the Excel object the[/color][/color]
> same[color=green][color=darkred]
> > > process takes around 27 minutes to complete. I have played around with
> > > screen updating and this makes no real difference to the time. Does[/color]
> > anybody[color=darkred]
> > > have any suggestions how I could cut down the processing time with the[/color]
> > Excel[color=darkred]
> > > object hidden?
> > >
> > > Any help appreciated,
> > > Mark Day.
> > >
> > >[/color]
> >
> >
> >[/color]
>
>[/color]

[Mark Day]

Re: Access -&gt; Excel Automation Slow

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

https://www.solortec.co.uk

"Mark Day" <mark.day@solor tec.co.uk> wrote in message
news:bm5q1o$674 $1@titan.btinte rnet.com...[color=blue]
> Hi,
> I already have timers in place and there does not appear to be any place[/color]
in[color=blue]
> code that causes this slowdown. If I show the Excel object and then[/color]
minimise[color=blue]
> the window via code, this has the same effect as hiding the Excel object[/color]
as[color=blue]
> 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 ...[color=green]
> > 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[/color][/color]
is[color=blue][color=green]
> > 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" <mark.day@solor tec.co.uk> wrote in message
> > news:bm4cpk$pd3 $1@hercules.bti nternet.com...[color=darkred]
> > > Hi All,
> > > I am using Access 2000 to generate over 40 Excel charts and pivot[/color][/color][/color]
tables[color=blue][color=green][color=darkred]
> > > using early binding to the Excel 9.0 object library. I am finding that[/color][/color]
> if[color=green]
> > I[color=darkred]
> > > show the Excel object while processing the charts, the whole process
> > > completes in around 2 minutes. However if I hide the Excel object the[/color][/color]
> same[color=green][color=darkred]
> > > process takes around 27 minutes to complete. I have played around with
> > > screen updating and this makes no real difference to the time. Does[/color]
> > anybody[color=darkred]
> > > have any suggestions how I could cut down the processing time with the[/color]
> > Excel[color=darkred]
> > > object hidden?
> > >
> > > Any help appreciated,
> > > Mark Day.
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

[TC]

Re: Access -&gt; Excel Automation Slow

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" <mark.day@solor tec.co.uk> wrote in message
news:bm845a$ad5 $1@titan.btinte rnet.com...[color=blue]
> Hi Tc,
> I have a series of functions that create several charts. Each function[/color]
works[color=blue]
> 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[/color]
base[color=blue]
> 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" <mark.day@solor tec.co.uk> wrote in message
> news:bm5q1o$674 $1@titan.btinte rnet.com...[color=green]
> > Hi,
> > I already have timers in place and there does not appear to be any place[/color]
> in[color=green]
> > code that causes this slowdown. If I show the Excel object and then[/color]
> minimise[color=green]
> > the window via code, this has the same effect as hiding the Excel object[/color]
> as[color=green]
> > 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 ...[color=darkred]
> > > This seems strange. You'd think it would be *slower* when made[/color][/color][/color]
visible.[color=blue][color=green][color=darkred]
> > >
> > > Put some timers throughout the automation code, to see if the slowdown[/color][/color]
> is[color=green][color=darkred]
> > > 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" <mark.day@solor tec.co.uk> wrote in message
> > > news:bm4cpk$pd3 $1@hercules.bti nternet.com...
> > > > Hi All,
> > > > I am using Access 2000 to generate over 40 Excel charts and pivot[/color][/color]
> tables[color=green][color=darkred]
> > > > using early binding to the Excel 9.0 object library. I am finding[/color][/color][/color]
that[color=blue][color=green]
> > if[color=darkred]
> > > I
> > > > show the Excel object while processing the charts, the whole process
> > > > completes in around 2 minutes. However if I hide the Excel object[/color][/color][/color]
the[color=blue][color=green]
> > same[color=darkred]
> > > > process takes around 27 minutes to complete. I have played around[/color][/color][/color]
with[color=blue][color=green][color=darkred]
> > > > 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[/color][/color][/color]
the[color=blue][color=green][color=darkred]
> > > Excel
> > > > object hidden?
> > > >
> > > > Any help appreciated,
> > > > Mark Day.
> > > >
> > > >
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]