Speeding up the excel spreadsheet population

pulkit
pulkit used Ask the Experts™
on
I have a excel spreadsheet which is populated from the data thru OLE from mainframe. The spreadsheet contains 40 coulmns atleast 50% of which have fromula's. The number of rows returned are around 6000+. It takes almost 10 minutes to populate the spreadsheet. I need to sped it up. Using tools - option- calculation-manual is not a option as I believe that impact all the excel spreadsheet on the server.

Can any one suggest some thing? the formula's mostly uses the Excel's native funtions which includes vlookup and arithmatic/logical functions.

Thanks.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2006
Commented:
Hello Pulkit,

you could try

on open
application.screenupdating = false
Application.Calculation = xlCalculationManual

on close
application.screenupdating = true
Application.Calculation = xlCalculationAutomatic

NEVER Select anything.  Totally unnecessary.  If you have code that Selects (or Activeates) anything, you're burning up zillions of CPU cycles

HAGD:O)Bruintje

Author

Commented:
As always : Worked!!! Thanks.

Is there any documents avalable on performance tuning for Excel any where?




Top Expert 2006

Commented:
thanks for the grade

http://www.cpearson.com/excel/optimize.htm

among other goodies there's an optimizer here
http://www.erlandsendata.no/english/downloads/tools.htm

:O)Bruintje

Author

Commented:
Well, initially I did not understand the importance of giving grade. I thought it was in line with complexcity. Now I do. You deserved what I gave you this time.

Thanks for the sites. They are good.

Thanks again.

Pulki Shah

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial