Performace of the excel by com handle

[shireeshn]

I have created an excle with com handles boz customer is asking to show some of the heading as BOLD and show some line level fields in table outlined.

It is woking fine with com handles(wht customer want) but performace is very poor due to updateing of induvidual cells.

can any one help me in this.

 

[AndrewSmith]

Automating Excel is generally a slow process when compared with a flat extract to text (or csv file). Each COM operation has an inherent overhead that's difficult to avoid! The only suggestions I can give:

1. I've seen a variety of anti virus packages get involved with Excel automation, slowing the process down. Disable it to test for interferance.

2. Try to combine many individual Excel com calls into one single call. If you're applying BOLD to a series of cells, rather than do one cell at a time you can apply it to a RANGE of cells, thus cutting down on the individual calls.

 

[shireeshn]

hi AndrewSmith,

Ur suggestions improved little bit :blush1: but not upto the mark, can u or any one suggest some more suggestions to make work more faster.

 

[enoon]

[SIZE=+1]Tip #4 : Turn off screen updating, recalculation[/SIZE]
If you're working with multiple sheets, screen refresh can slow you down and distract your user. Put Application.ScreenUpdating = False at the top of your routine and it will freeze screen updating until all your code has stopped executing.
Similarly, you can suspend auto-recalculation with Application.Calculation = xlManual, and return it to auto at the end with Application.Calculation = xlAutomatic


Source: http://www.avdf.com/\apr98\art_ot003.html

Good luck, this did the trick for me, I hade a similar issue a few years back.