Auto Calculate Spreadsheet at a certain time

[SteveK123]

Hi All,

I there a way to set up a spreadsheet so that it opens at a certain time (say 6:30am),calculates all of the data and then saves and closes once the calculation is complete.

I have a morning meeting spreadsheet which takes roughly 30minutes to calculate and I need it to be ready for first thing in the morning.

It needs to be closed however when the meeting takes place as it needs to be read-write status when whoever is running the meeting that day opens it.

I hope this makes sense.

 

[martindwilson]

maybe solving why it takes 30 mins to calculate would be easier!

 

[tinyskills13]

Along the lines of what Martin said, do you have screen updating turned off while your code runs? Might help speed things up a little.

 

[xenou]

In my not so humble opinion no workbook of this magnitude belongs in a daily meeting.

However, it is should be easy to schedule a task to open a workbook. The only problem would be how to save/close it.

I'm puzzled as to why in the world you'd be using such a large workbook in this way - part of a meeting, needing to be shared, different users requiring write access... Everything smells bad here. Can you elaborate on what you are doing? There must be a better way to manage your data and still provide adequate reporting.

 

[SteveK123]

Without going into too much detail about the spreadsheet, it calculates a large amount of process data to give averages, standard deviation, trends and other information to indicate the way the plant has been running for the last 1 day, 3 days and 30 days.

The process data is being pulled directly from the plant operating systems and there is no way of speeding this.

Is it possible to do a macro which will run the calculate at a certain time, I am able to close the spreadsheet in the morning ready for the meeting.

Thanks

 

[SteveK123]

I also forgot to mention the main bottle-neck that slow all of this down. Our works network is rediculously slow and take forever to do anything even simple calculations.

 

[VoG]

You could use the OnTime method.

Your macro would essentially be a one-liner

Sub MyMacro()
Calculate
Application.OnTime TimeValue("06:30:00"), "MyMacro"  
End Sub

 

[xenou]

I would assume you can also run a scheduled task to open the workbook each morning (though, why isn't it possible to just leave it open?).