Excel automation Performance problem!! Need help please!   

I apologize if this is not quite the correct group for this message.  Also, I 
sort of posted as follow up to my original questio a couple of days ago but 
have since determined that this is problem I really need an answer to. ( I 
will be cross posting just to have a chance for a more reponses)

I am automating Excel using C# in VS2005.  I am NOT using VSTO.  I need 
support Excel 2000 through Excel 2007 so I am using 'late binding' via 
reflection and developing with an Excel 2000 reference.

I need to perform a replace of about 100 different values, but I can be 
guranteed that these values will alway be in the same place and I also have 
been told that using named ranges is not likely to be allowed.  So this 
replace has to occur over the used area of sheet.  Doing this with Excel 
hidden and without user access, is extremely slow (if I use a specific named 
range, it has acceptable performance).  I also know that I can pull a Range 
of values in an object array in C#, replace them in the array and then 
repopulate the array, but this is again requires knowing the range.

It was decided to try and use an embedded macro that launch to do the 
replacement so that the Replace is not occurring via the interop and COM.  
Manually, opening Excel and executing the macro has performance that is more 
than acceptable.

So I tried this automated with Excel hidden, and performance was extremely 
poor about the same as with the replace via interop automation.  A minute 
plus to complete what took a second or two when launched manually.  I then 
made Excel visible and had the same performace problem.  Finally, I ran the 
application again with Excel visible and then I manully clicked Excel, giving 
it focus.  The macro immediately sped up and finished in a second or two.

So my questions are:

- Has anyone seen this behavior for?
- Does anyone know why this is the case?
- Most, importantly is there a way around this?

The requirement is to run Excel hidden or at the very least  minimized while 
giving performance similar to the manual lauch of the embedded macro.

date: Fri, 31 Aug 2007 06:56:00 -0700   author:   John Mercure

RE: Excel automation Performance problem!! Need help please!   

Some added information here that may help others.

It appears that the performance problem I was seeing is tied to Excel 2000.  
This is either because I was testing from VS2005 or some issue with Excel.  
Testing against Excel 2003 and 2007 through an installed copy of my 
application showed no performance issue.

I would still like to determine why Excel 2000 is giving me problems.

- John Mercure



"John Mercure" wrote:

> I apologize if this is not quite the correct group for this message.  Also, I 
> sort of posted as follow up to my original questio a couple of days ago but 
> have since determined that this is problem I really need an answer to. ( I 
> will be cross posting just to have a chance for a more reponses)
> 
> I am automating Excel using C# in VS2005.  I am NOT using VSTO.  I need 
> support Excel 2000 through Excel 2007 so I am using 'late binding' via 
> reflection and developing with an Excel 2000 reference.
> 
> I need to perform a replace of about 100 different values, but I can be 
> guranteed that these values will alway be in the same place and I also have 
> been told that using named ranges is not likely to be allowed.  So this 
> replace has to occur over the used area of sheet.  Doing this with Excel 
> hidden and without user access, is extremely slow (if I use a specific named 
> range, it has acceptable performance).  I also know that I can pull a Range 
> of values in an object array in C#, replace them in the array and then 
> repopulate the array, but this is again requires knowing the range.
> 
> It was decided to try and use an embedded macro that launch to do the 
> replacement so that the Replace is not occurring via the interop and COM.  
> Manually, opening Excel and executing the macro has performance that is more 
> than acceptable.
> 
> So I tried this automated with Excel hidden, and performance was extremely 
> poor about the same as with the replace via interop automation.  A minute 
> plus to complete what took a second or two when launched manually.  I then 
> made Excel visible and had the same performace problem.  Finally, I ran the 
> application again with Excel visible and then I manully clicked Excel, giving 
> it focus.  The macro immediately sped up and finished in a second or two.
> 
> So my questions are:
> 
> - Has anyone seen this behavior for?
> - Does anyone know why this is the case?
> - Most, importantly is there a way around this?
> 
> The requirement is to run Excel hidden or at the very least  minimized while 
> giving performance similar to the manual lauch of the embedded macro.
> 
>

date: Tue, 4 Sep 2007 02:52:04 -0700   author:   John Mercure