Scheduling A Macro To Run Via Task Scheduler

I’m trying to get a macro to run each week via the task scheduler and I’ve never done this before. I googled it and found some code, but some of it wouldn’t allow me to get past the security halt for executing a macro. At least this code doesn’t do that, but it only pulls up the workbook and I never see any execution of the macro. I hoping someone here can help.

The workbook is this case is named Acronis Backup check.xlsm and the macro inside it is Backup_Check. I created a folder and put the workbook and a run.bat and a script.vbs file in there, and am trying to execute the run.bat file. As I said it’ll put up the workbook but nothing ever happens with the execution of the macro. Then if I close the open workbook Excel crashes and restarts.

Here’s the code in script.vbs:

Dim args, objExcel

Set args = wscript.Arguments
Set objExcel = CreateObject("Excel.Application")

objExcel.Workbooks.Open args(0)
objExcel.Visible = True

objExcel.Run "Acronis Backup Check!Backup_Check"

objExcel.Activeworkbook.Save
objExcel.Activeworkbook.Close(0)
objExcel.Activeworkbook.Quit

Here’s the code in run.bat:

cscript script.vbs "C:\_Excel Macro Scripts\Acronis Backup Check.xlsm"

Any idea how I can get a macro to run via scripting so I can execute it via the task scheduler?

.
.
I can't make any comments about the VB script of BAT file ... however,
in your workbook :

In the ThisWorkbook module do you have a call to the macro when the workbook opens ?

Option Explicit

Private Sub Workbook_Open()
    Backup_Check
End Sub

Originally Posted by Logit

.
.
I can't make any comments about the VB script of BAT file ... however,
in your workbook :

In the ThisWorkbook module do you have a call to the macro when the workbook opens ?

Option Explicit

Private Sub Workbook_Open()
    Backup_Check
End Sub

No, I don't. Should I? I thought that the statement:

objExcel.Run "Acronis Backup Check!Backup_Check"

would do that, running that macro. Is that not true?

Originally Posted by cytop

As you are creating the Excel Object there is no need to include the workbook name, there's only 1 workbook open so just pass "Backup_Check" as the parameter - works fine for me.

But...

A Script file knows nothing about what is happening in Excel - it will continue and close Excel while the VBA code is running. You need to work around that.

For example, creating a test workbook with a simple procedure to show a userform

Sub Test
UserFrom1.Show
End Sub

will work. But if the procedure is changed to show the userform modelessly, then the script errors...

Well, I'm new at all this so I guess I really don't understand, other than getting the fact that the script knows nothing about what Excel is doing after it executes the macro. I added this to my macro although I really don't understand what it does:

UserForm1.Show

Then I added a one minute wait after the macro was invoked to give it time before the script closes it, so now the script looks like this:

Dim args, objExcel

Set args = wscript.Arguments
Set objExcel = CreateObject("Excel.Application")

objExcel.Workbooks.Open args(0)
objExcel.Visible = True

objExcel.Run "Backup_Check"
WScript.Sleep(60*1000)  'Sleep for 1 Minute

objExcel.Activeworkbook.Save
objExcel.Activeworkbook.Close(0)
objExcel.Activeworkbook.Quit

But still nothing happens at all. It just opens the workbook and sits there as far as I can tell. And when I close it minutes later Excel still crashes and restarts.

Showing a userform modally is a 'blocking' action. No other code can execute until the userform is unloaded so the procedure in Excel that loads the userform never continues until the userform is unloaded. That stops your script continuing and closing the workbook.

If the userform is shown modelessly then the code continues after the userform is loaded, control is returned back to the script which then shuts down Excel, and pops an error.

The attached shows both (you'll need to rename the TXT file as VBS - can't attah script files). First the userform will be displayed modally, the script will be paused until you click the Close button on the userform. It'll then show the userform modelessly. You'll note the msgbox from the script saying "Back from modeless test" (or something like that) will be displayed over the userform. The script has continued to execute after the userform is loaded. Click 'OK' on that message and the script crashes...

Originally Posted by cytop

Showing a userform modally is a 'blocking' action. No other code can execute until the userform is unloaded so the procedure in Excel that loads the userform never continues until the userform is unloaded. That stops your script continuing and closing the workbook.

If the userform is shown modelessly then the code continues after the userform is loaded, control is returned back to the script which then shuts down Excel, and pops an error.

The attached shows both (you'll need to rename the TXT file as VBS - can't attah script files). First the userform will be displayed modally, the script will be paused until you click the Close button on the userform. It'll then show the userform modelessly. You'll note the msgbox from the script saying "Back from modeless test" (or something like that) will be displayed over the userform. The script has continued to execute after the userform is loaded. Click 'OK' on that message and the script crashes...

Cytop: Thanks, and I realize I'm likely being dense here, but please help me out. If I execute your code you sent it'll do just what you said it would. But I'm not sure how I should add that to my existing macro to get it run as a scheduled task. I don't want to display a form that requires user interaction from the scheduled task to continue, right? Using the form with a close button would do that I would think. And if the goal is to not return to the script where it saves and closes the workbook and quits before the macro has executed, wouldn't the Sleep command in the script, before the close, do that as well?

Can you help me understand what code I should add to my existing macro, or the script, to get it to run? Like I said, I'm likely just no understanding things right. - Joel

The absolute simplest thing to do is load th euserform in he workbook open event and then call your procedure from the userform Activate event, if possible. Then you can leave the userform displayed on screen displaying little bits of info as to progress and simply unload it when the procedure finishes. Saves a lot of complication in the script.

Above offered knowing absolutely nothing about the procedure; I'm like a little bit of electricity - always looking for the easiest path through

Originally Posted by cytop

The absolute simplest thing to do is load th euserform in he workbook open event and then call your procedure from the userform Activate event, if possible. Then you can leave the userform displayed on screen displaying little bits of info as to progress and simply unload it when the procedure finishes. Saves a lot of complication in the script.

Above offered knowing absolutely nothing about the procedure; I'm like a little bit of electricity - always looking for the easiest path through

Let me attach my macro, since I'm not sure I follow that exactly. Thanks again.

OK - it's not a 5 minute jobbie to go through that but 1 question arises first.

In the procedure BackUp_Check is the line

            Workbooks.Open ("C:\_My Files\_My OCCF Files\Acronis Backup Check.xlsx")

That is actually the name of the workbook you attached. Regardless if it's a separate copy, Excel will not open 2 workbooks with the same name even it they exist in different directories. Does that line ever get executed?

Also, there is a total lack of any error handling. While that's probably not a bad thing while working on this it's not a good idea for a 'production release'.

Originally Posted by cytop

OK - it's not a 5 minute jobbie to go through that but 1 question arises first.

In the procedure BackUp_Check is the line

            Workbooks.Open ("C:\_My Files\_My OCCF Files\Acronis Backup Check.xlsx")

That is actually the name of the workbook you attached. Regardless if it's a separate copy, Excel will not open 2 workbooks with the same name even it they exist in different directories. Does that line ever get executed?

Also, there is a total lack of any error handling. While that's probably not a bad thing while working on this it's not a good idea for a 'production release'.

Oops. That one line can be commented out. This version of the macro assumed the macro was being run from outside the workbook, but since the bat file now actually opens it that line isn't needed.