VB.Net Excel 2007 Automation Performance HUGE Hit

[doug]

I originally wrote an app that I'm still testing on a XP PC with 2GB Ram and
1 CPU. Took 7 minutes to create 1 file. I'm creating over 100 files so this
is not a good sign. I move app to a XP PC with 3GB and 2 CPUs. App now
created sames files in just under 1 minute each. Another week or so goes by,
I'm just adding a few features and fixing formatting and data sourcing
issues, and suddently last 3 days, same 2 CPU PC, files take 7-9 minutes
each. I cannot account for this.

Any suggestions?

Additionally, I'm getting totally random 0x800A03EC errors, mostly on Excel
WS PAST methods. I do Source Activate, Select, Copy, Target Select, Paste in
a lot of places. It wil randomly (seemingly) abend on the Paste. If I run
in debug mode and ut a break on all Catch blocks once it gets tripped, I
determine hich line had the issue (always on a Paste), I look for the Soure
Activate statement that prceded the Target Paste and set that instruction to
be next statement, and press GO. Runs fine. Might break again different
place, same file, or break 10-30 files later. Always restartable.

 

[Jim Cone]

I probably can't or won't help as I avoid xl2007 issues.
However, in order to get somebody to help, you should provide more info...
From what application are you automating Excel?
Are you using Windows and what version?
What programming language are you using?
Posting the offending code will generate the most help. (assuming VBA).

Comments...
When automating Excel, it is best not to use "Select". Instead use object references.
When using object references, Set them to nothing when exiting the code.
(assuming your programming code allows that)

And what does "abend" mean? Yours is the second post today that uses that term.
FWIW, MS has asked users to send them workbooks that perform poorly in xl2007.
(of course, that may be all of them)
--
Jim Cone
Portland, Oregon USA



"doug" <[email protected]>
wrote in message
I originally wrote an app that I'm still testing on a XP PC with 2GB Ram and
1 CPU. Took 7 minutes to create 1 file. I'm creating over 100 files so this
is not a good sign. I move app to a XP PC with 3GB and 2 CPUs. App now
created sames files in just under 1 minute each. Another week or so goes by,
I'm just adding a few features and fixing formatting and data sourcing
issues, and suddently last 3 days, same 2 CPU PC, files take 7-9 minutes
each. I cannot account for this.

Any suggestions?

Additionally, I'm getting totally random 0x800A03EC errors, mostly on Excel
WS PAST methods. I do Source Activate, Select, Copy, Target Select, Paste in
a lot of places. It wil randomly (seemingly) abend on the Paste. If I run
in debug mode and ut a break on all Catch blocks once it gets tripped, I
determine hich line had the issue (always on a Paste), I look for the Soure
Activate statement that prceded the Target Paste and set that instruction to
be next statement, and press GO. Runs fine. Might break again different
place, same file, or break 10-30 files later. Always restartable.

 

[Charles Williams]

Don't know why its crashing, but it certainly sounds extremely slow.

Excel 2007 has a large overhead on each transfer of data between Excel and
VBA so presumably the same holds true for .Net, but even larger because of
the slow .Net interop layer.

You need to avoid activating and selecting, but also try to transfer/work
with the data in as large a block as possible rather than cell-by-cell.

And if you need speed avoid using the .Net Interop Layer.

Also try switching off Events, ScreenupDating and set Calculation to manual.


Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com