Hi everyone,
I've got the following problem:
I have an excel file (about 350MB), which as a total number of 72 sheets. Of those 72 sheets, about 30 of them are "input data" or to be more precise results from a structural analysis software. Each fo these sheets has about 12000 rows and 10 columns, which are "raw data". The next 30 sheets are combination sheets, where the "raw data" gets combined according to some relatively simple formulae. The last 12 sheets or so are just graphs visualising this information, so nothing special.
However, the excel is MASSIVE!!! It takes about 5 minutes to open, 10 minutes to save, loads about 5GB on to system memory and takes ages to process just one calculation because of all this information. Worst of all, the strucural model changes about once or twice a week, meaning the results have to re-imported into the "input-data" sheets, processed by the "combination sheets" and interpreted after that.
So, to speed things up, I would like to disable calculations completely on some sheets and leave it running on others (simulating a sort of "paste values only" situation), where excel only takes the last known value and does not recalcualte it at all.
After opening VBA and clicking on one of the sheets, I was able to find a property called "EnableCalculation" which was set to "True". I'm not sure if this is the right way, but it is poissible to create a button or a switch, which will let me change this from "True" to "False" and vice versa, as I need?
Sorry, I know I'm still a nood at VBA, but I'm trying to learn
)
Thank you in advance for your help!
I've got the following problem:
I have an excel file (about 350MB), which as a total number of 72 sheets. Of those 72 sheets, about 30 of them are "input data" or to be more precise results from a structural analysis software. Each fo these sheets has about 12000 rows and 10 columns, which are "raw data". The next 30 sheets are combination sheets, where the "raw data" gets combined according to some relatively simple formulae. The last 12 sheets or so are just graphs visualising this information, so nothing special.
However, the excel is MASSIVE!!! It takes about 5 minutes to open, 10 minutes to save, loads about 5GB on to system memory and takes ages to process just one calculation because of all this information. Worst of all, the strucural model changes about once or twice a week, meaning the results have to re-imported into the "input-data" sheets, processed by the "combination sheets" and interpreted after that.
So, to speed things up, I would like to disable calculations completely on some sheets and leave it running on others (simulating a sort of "paste values only" situation), where excel only takes the last known value and does not recalcualte it at all.
After opening VBA and clicking on one of the sheets, I was able to find a property called "EnableCalculation" which was set to "True". I'm not sure if this is the right way, but it is poissible to create a button or a switch, which will let me change this from "True" to "False" and vice versa, as I need?
Sorry, I know I'm still a nood at VBA, but I'm trying to learn
)Thank you in advance for your help!
