Is Scheduled, Unattended Server-Based Creation of Excel Workbook With VBA Possible?

I'd like to be able to 开发者_运维技巧schedule an Excel macro (VBA) to run in the middle of the night (after a file is ready) to create a customized workbook (multiple sheets, pivot tables, charts, filters, outlines, custom formatting, etc.). Currently, the macro is fired up manually the next day. Furthermore, it needs to run unattended on a server (laptop goes home at night!). Anybody successfully do something like this? Please, no Unix-side hacks (e.g., Perl modules) - need full access to VBA features, including database functions. Thanks!

Well you have some options.

First, for all Excel has to be installed on server. Then you create a sheduled task to call a program.

In this case you can write e.g. a vbscript or .NET program to call the app, load the document and starts its content (your VBA). That should work at all. Or you move the VBA code to a program and target Excel with your code, but prolly more work.

If you do this with .NET you have prolly best success. e.g. you can add an eventlog for successful run, etc.

If you can leave Excel running on the server all the time, you can use Application.OnTime to schedule the next runs of a particular macro (once it's run, reschedule another in the macro code). When I worked in banking we used this all the time to run night-time jobs.

If you cannot leave Excel running, I have to say you may be in a world of pain. It's possible to start Excel using an AT job (scheduled task) but you may have headaches getting it to run under the correct user privileges and if you use any addins you'll experience regular disasters where they failed to load and stopped Excel from starting up. At the end of the day, Excel isn't really meant to be run on servers (it's actually a violation of the terms of use) and starting/running/stopping it is not going to be a reliable system even if you do get it to work.