Auto run macro

Question

Is it possible for a macro to run at a specific date and time without having the workbook initially open?

I am needing it to run every weekday morning accept for holidays.

If it could do this in the background without opening the workbook it would be even better.

If this is not possible, then I would need it to open, run, then close on the appropriate dates and times.

Can this be done?

Answer 1

The 'Select Case' block begins with the line 'Select Case Date' and ends with the line 'End Select' and includes all the lines in between.  You can't have the 'If..Then..End If' block inside the Select block.

Try this

If Weekday(Date) = 1 or Weekday(Date) = 7 Then

WScript.Quit

End If

Select Case Date

   Case "9/6/2010"

      WScript.Quit

   Case "10/11/2010"

      WScript.Quit

   Case "11/25/2010"

      WScript.Quit

   Case "11/26/2010"

      WScript.Quit

   Case "12/27/2010"

      WScript.Quit

End Select

arg1 = WScript.Arguments(0)

Set objXL = CreateObject("Excel.Application")

objXL.Workbooks.Open("C:\Users\The King's\Desktop\Test\wkbk1.xlsm")

objXL.Run "myMacro", CStr(arg1)

objXL.ActiveWorkbook.Save

objXL.ActiveWorkbook.Close

objXL.Quit

As far as setting it to launch at 7am each day, use the Task Scheduler to launch the command line.  I could modify the script to launch at specific times but it would have to be running all the time and wasting resources which doesn't make sense when you have the Task Scheduler available to do the job.

Answer 2

Could you include in the script for me; 7:00 central time as when to open the workbook?

Thanks

Answer 3

If I give my workbook out to a friend so that they can use it, would this be user friendly enough to modify the dates? Unfortunately I don't know of any way to have the dates that the stock market are closed and weekends automatically be entered; so a user would have to enter it themselves probably for each calendar year as time goes along? This looks good accept I would need to have an argument included to exclude weekends also.

Thanks,

Doug

Answer 4

Here is a bit more of a detailed example than I provided in my initial response.  Granted, the task I'm having the macro do is a bit silly but I wanted to keep it as simple as possible.

In the folder "C:\Test" I create the workbook, "wkbk1.xls".  In this workbook, I create a macro that is shown immediately below:

'------------------------------------

Sub myMacro(Optional myArg As String = "")

Cells(1, 1).Value = myArg

End Sub

'------------------------------------

All this macro does is to take a string argument and place the text string in cell A1 of the active sheet.  If no argument is given to the sub when it launches, a zero length string is the value of cell A1.

I make the folder "C:\Test" a trusted location so the macro can run without warnings that require user intervention.

On my desktop which is "C\Users\Steve\Desktop" I create a new text file that I name "RunXLmacro.vbs"  The contents of this text file with a vbs extension are immediately below:

'----------------------------------

Select Case Date

   Case "9/6/2010"

      WScript.Quit

   Case "10/11/2010"

      WScript.Quit

   Case "11/25/2010"

      WScript.Quit

   Case "11/26/2010"

      WScript.Quit

   Case "12/27/2010"

      WScript.Quit

End Select

arg1 = WScript.Arguments(0)

Set objXL = CreateObject("Excel.Application")

objXL.Workbooks.Open("C:\Test\wkbk1.xls")

objXL.Run "myMacro", CStr(arg1)

objXL.ActiveWorkbook.Save

objXL.ActiveWorkbook.Close

objXL.Quit

'----------------------------------

What the script is supposed to do is first check if the date today is Labor Day, Veteran's day, Thanksgiving or Christmas holiday and quit if it is a holiday.  If not, it retrieves the first command line argument presented to the script.  Finally, it opens Excel, opens the workbook wkbk1.xls, runs the macro named 'myMacro" giving it whatever command line argument it received and then saves and closes the workbook.

Finally, I set up the command line to launch the script in Windows Task Scheduler, or for testing I just pressed the Windows key and letter 'r' to get the Run line and entered the command line:

WScript.exe "C:\Users\Steve\Desktop\RunXLmacro.vbs" "cat"

This uses the executable WScript.exe to launch the vbScript and presents the word "cat" as a command line argument.  Now, if I go and open C:L\Test\wkbk1.xls, I will find the word "cat" in cell A1 on Sheet1.

Answer 5

One option would be to create a script in vbs that would open your target workbook and run the macro.  You could set the Windows task scheduler to run the vbs file (using WScript.exe or CScript.exe with the vbs file as the argument) at a specific time.  You would have to set up your macro to close the workbook when it was done with its job.

The vbs file is simply a text file with a vbs file extension rather than txt.  Suppose I have a macro named "myMacro" in the workbook named "C:\Test\wkbkA.xls".  The script

Set objXL = CreateObject("Excel.Application")

objXL.Workbooks.Open("C:\Test\wkbk1.xls")

objXL.Run "myMacro"

would open the workbook and run the macro.  If you have a list of holidays, you could modify the script to only run on days when it wasn't a holiday.  You could also set it up to feed arguments to the macro.  Having said that, it might be easier to have the script do whatever the macro is supposed to do and bypass Excel.  What is it that you want the macro to accomplish on this schedule?