Hi ,
    I am working on an excel application with around 40 sheets and a lot of VB Controls and interaction with Access database. The Excel file takes a huge amount of time to open , save or Close. Can any one tell me how to reduce this time???

vikashtiwari,

Had you said that the execution of macros took a very long time, then I would have been able to suggest modifications to your code (such as turning off ScreenUpdating, setting the calculations to manual, using arrays instead of collections, etc.); but the problem with opening, saving and closing a file generally relates to the SIZE of the file.

One question I have though is -- When does the file connect to the database? If it connects when it's opened, then that might be cause for delay (it could be changed to connecting only when certain processes are run).

One of the things that often contributes to the size of Excel file (with the associated opening, closing, and out-of-memory problems) are storing complex formulas in a lot of cells. The way to alleviate this problem is to have any macros which create formulas to turn the formulas into values after the result is computed (by the formula).

To make a long story short —— you should probably experiment with different approaches and see which works best. Do you really need 40 worksheets in 1 workbook?

For more info recommend http://www.vba-programmer.com

CarlR

Turning calculation to manual rather than automatic can sometimes also help (so it doesn't recalculate upon opening, saving and closing).

Hi Carl,
   Thanx a lot for the suggestion. Actually i need 40 sheets in the workbook as it has a lot of functionality. The data is being stored in access databse from those sheets and i am setting the formulae for calculation on activation of each sheet. But yes my file size has bloated to 4 MB and each sheet has atleast 4-5 buttons.
Can you suggest anyway to reduce this overhead of OLE Buttons.
Vikash

vikashtiwari,

You've said that each of your 40 sheets has 4-5 buttons.

Suggest that you remove these buttons from the worksheets -- simply having them there creates significant overhead -- and put them on one or more toolbars.

CarlR

I am also facing the same problem. I tries multi threading (VB.Net) but no use. Did you find any solution ?
Please share your though with me.

Hello everyone.

The problem of SLOW open, save, and close of Excel is actually quite common. The solution is to delete all of your *.emf files. These are temporary files created by excel each time you open, add, change, etc... an Excel file. Over time, not only does your pc accumulate a lot of these files, but they can also become corrupt. If you use Excel often (which most of us do), you'll want to check for and delete these *.emf files.

Navigation =
     Start-->Search-->files & folders-->Local Disk
Enter *.emf in the file name box and then press FIND/SEARCH.


Rick

Vikash.
Is it the only 40 sheets file closing that slow? What about the small files. Do they close fast enough? Check the regular size files.
If they also saving slow check if you have any mapped drives in your root.
Very often mapped drives linked to inactive computers cause this problem. Even 1 cell sheet could take a long time to save.

Regards,
Mike

Vikash.
Is it the only 40 sheets file closing that slow? What about the small files. Do they close fast enough? Check the regular size files.
If they also saving slow check if you have any mapped drives in your root.
Very often mapped drives linked to inactive computers cause this problem. Even 1 cell sheet could take a long time to save.

Regards,
Mike