I have a VB.Net 2005/Excel 2007 Automation app that reads in excel input, templates, and gets data from SQL server to build excel output files. Currently it builds around a 100 files. Originally, it took 7 minutes a file (lots of copy and paste to facilitate formatting) - or 11.5 hours to build all files. This was on a single CPU workstation. I moved app to another PC with dual core and 3GB ram and process dropped to less 1 minute or 1.5 hours. As I began to fine tune the formatting and add features, something changed and I'm now at 7-9 minutes a file which is now over 13 hours. It is like I told it to skip the 2nd CPU or something.
I have a lot of cut-n-pasts like the one listed below. Again, the error always trips on the paste, and if I put a break on my Catch I can then set next statement to the xlFormattingWS.Activate() and hit GO, and it runs along for a short while (error on same output file) or even a very very long while (error 10-30 output files later).
' Set Font, Size, background from template formatting values
xlFormattingWS.Range(xlFormattingWS.Cells(3, 2), xlFormattingWS.Cells(3, 2)).Select()
xlFormattingWS.Range(xlFormattingWS.Cells(3, 2), xlFormattingWS.Cells(3, 2)).Copy()
xlOutPutWS.Range(xlRateSheetWS.Cells(iNextAvailRow, iNextAvailCol), xlOutPutWS.Cells(iNextAvailRow, iNextAvailCol)).Select()
' Set tier title value
xlOutPutWS.Cells(iNextAvailRow, iNextAvailCol) = sMyText.TrimEnd