Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB.Net Excel 2007 Automation Performance HUGE Hit
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB.Net Excel 2007 Automation Performance HUGE Hit
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" 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB.Net Excel 2007 Automation Performance HUGE Hit
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 "doug" 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Improve the performance of Excel 2007. | Excel Worksheet Functions | |||
How do I improve the 2007 Excel performance. | Excel Discussion (Misc queries) | |||
Automation performance degradation in Excel 2007 | Excel Programming | |||
Excel 2007 export file size huge | Excel Discussion (Misc queries) | |||
Excel Automation - Chart Performance | Excel Programming |