I have a spreadsheet which I want to upload onto my company's intranet for others to view. Everytime the spreadsheet opens, it runs a background script which updates the information. The problem is - the script runs for about 10 minutes because it looks at 2 huge tables.
Is there a way for the spreadsheet to be refreshed once a day (example 2am) without someone having to open it?
-
This depends how you share the document. On a file server? In Sharepoint? We solve this with SQL databases, where we can decide how often synchronisation takes place.– daraosApr 23, 2014 at 9:29
-
Not an answer, but: It looks like you are really hitting Excel's limitations. Maybe you need to write (have written) a "real" program, instead of doing everything inside Excel? The code which Excel runs (VB probably?) could be extracted into a separate program.– sleskeApr 23, 2014 at 9:37
Add a comment
|
2 Answers
Simple answer: no. A VBA macro runs on the Microsoft Excel engine, meaning, Excel must be oen for the macro to run.
However, VBScript is the same language used as VBA with exception that VBA can tie directly into a microsoft excel file and access cells using commands such as sheet(1).cells(1,3) where VBScript can't. With a lot of work, you could create a vbscript that reads and creates .csv files which in term might give you a substitution idea.
If that really is not an option (and honestly, I don't blame you), you could disable the script to load every time someone opens it and instead make it a manual run job to at least give the when control to you. If there's a pc running at night that runs Microsoft Excel (could be a server even), you could create an Auto-hotkey script or perhaps a different VBA script that will run this macro and use a scheduled task to start excel and open the workbook. Note that a scheduled task itself cannot run the macro, so you have to facilitate for a way to run the macro automatically. For example, you have workbook2.xls which has a macro to automatically open workbook1.xls and then run its calculate macro, and at the end save workbook1.xls and then close both files.
I think this could be possible using Application.OnTime.
So, when the Excel sheet is opened by the user add the following to your existing code
Public Sub ExecuteBigMacro()
timeForAlert = Now + TimeValue("24:00:00")
Application.OnTime timeForAlert, "ExecuteBigMacro"
'the rest of your code which takes ages!
End Sub
This does mean initially opening it at 2am (and every time after you restart the application)!! Although depending on the situation, you could even have it update every 4 hours and just accept during this time, there is going to be a delay for the user!!
