scheduling a server-based app

[Guest]

This question follows on from my last one (subj = "deploying Custom.xls"),
and may not be appropriate for this forum: how can I get a server-based Excel
workbook to automatically update itself on a weekly basis? (Currently, I
manually load the workbook and run a macro.)

Thanks!

 

[RB Smissaert]

Use the Windows task scheduler to open the workbook.
In the Workbook_Open event put the code that updates and saves
the workbook and at the end quits Excel.

RBS

 

[Guest]

Or if you want a solution that is easier to maintain and control you should
read this blog post:
http://krgreenlee.blogspot.com/2006/04/excel-running-excel-on-windows-task.html

I use an Excel Controller (in this case a VBS script) to control Excel
externally and then use Windows Task Scheduler to manage execution time.

Kim Greenlee

 

[RB Smissaert]

You might be right, but I couldn't make out from the page in the link
what is wrong with just the task scheduler and the workbook.
What are the exact advantages?

RBS

 

[Guest]

RBS,

If you start your workbook from the scheduler using "Excel myworkbook.xls"
you have to control the entire workbook exectution from within
myworkbook.xls. As you stated this includes shutting it down.

This makes it very hard to debug and change without setting various flags
and conditions, or using a state machine. You don't want the application to
start up, do its work, and then immediately shut down. You will need a
chance to set a break point. It is always beneficial for a developer to
actually watch the program execution to ensure that the behavior she is
expecting is actually happening.

But wait there's more...

There is a link on that blog post that goes to another post where I played
around with your suggested method. I was figuring out how to automate remote
workbook execution so that I could run my workbooks on a compute grid. It
seemed like the obvious answer, but running an Excel workbook in an automated
fashion has a lot of caveats, read this post:
http://krgreenlee.blogspot.com/2006/03/digipede-distributing-excel_10.html
for work arounds.

The main problem with automating Excel is that Excel is designed to have the
GUI up and running. This is Microsoft's take on Excel automation:
http://support.microsoft.com/kb/257757/, which basically says, "Don't do it."


By using an Excel Controller, you can set all the flags you need to set
BEFORE you open Excel so that you can stop Excel from doing anything
"GUIish". This technique also runs Excel in the background, in another
instance, so it is less likely to interfere with an already running instance
of Excel...say one that a user is currenty working in.

Hope that helps,

Kim Greenlee

 

[RB Smissaert]

OK, thanks, I will give it a try.
Must say though that if there is no chance other users are using Excel at
the time the scheduler starts I have never seen any problems from it.
The situation I need to schedule Excel for is to run some database queries
out of hours and store the data in text files. That is all.

RBS