Refresh Excel document without opening it

Question

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?

Solution

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.

Comments

[daraos]

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.

[sleske]

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.