Automate Macros
 

Hi All,

I am trying to automate a few Excel Macros, in order that
I can used scheduled tasks to kick these macros off. For
instance, I use the AutoExec function in Access a lot,
and kick these off using scheduled tasks. I would like
to replicate this for some Excel Macros.

I have been told to use the Autorun function, with a Quit
command at the end of the Macro to close the spreadsheet
after the macro has run. However, I haven't got much
Visual Basic knowledge and I can't get this to work. If
anyone can help it will be very much appreciated.

Tom

Automate Macros
 

There is no way in Excel to designate a macro to run from the command line
or when the workbook is opened. You must use one of two macros for Excel to
run them when the workbook is opened. One is Sub Auto_Open. The other is
the Workbook_Open event handler in the ThisWorkbook module. Use either of
these and put your code in it.

Then use Scheduled Tasks to open the workbook.

--
Jim Rech
Excel MVP
"Tom" wrote in message
...
| Hi All,
|
| I am trying to automate a few Excel Macros, in order that
| I can used scheduled tasks to kick these macros off. For
| instance, I use the AutoExec function in Access a lot,
| and kick these off using scheduled tasks. I would like
| to replicate this for some Excel Macros.
|
| I have been told to use the Autorun function, with a Quit
| command at the end of the Macro to close the spreadsheet
| after the macro has run. However, I haven't got much
| Visual Basic knowledge and I can't get this to work. If
| anyone can help it will be very much appreciated.
|
| Tom
|

Automate Macros
 

Brilliant thank you very much Jim! Thats worked a
treat. Is there a command that I can specify at the end
of my macro to close the workbook after the macro has
ran? I have tried Quit and Close but neither are working.

Thanks once again,
Tom



-----Original Message-----
There is no way in Excel to designate a macro to run
from the command line
or when the workbook is opened. You must use one of two
macros for Excel to
run them when the workbook is opened. One is Sub
Auto_Open. The other is
the Workbook_Open event handler in the ThisWorkbook
module. Use either of
these and put your code in it.

Then use Scheduled Tasks to open the workbook.

--
Jim Rech
Excel MVP
"Tom" wrote in
message
...
| Hi All,
|
| I am trying to automate a few Excel Macros, in order
that
| I can used scheduled tasks to kick these macros off.
For
| instance, I use the AutoExec function in Access a lot,
| and kick these off using scheduled tasks. I would like
| to replicate this for some Excel Macros.
|
| I have been told to use the Autorun function, with a
Quit
| command at the end of the Macro to close the
spreadsheet
| after the macro has run. However, I haven't got much
| Visual Basic knowledge and I can't get this to work.
If
| anyone can help it will be very much appreciated.
|
| Tom
|


.

Automate Macros
 

Well, I think Application.Quit should do it. This code in a workbook that
was run by Scheduled Tasks opened Excel, the workbook, flashed the message
box and then closed Excel:

Sub Auto_Open()
MsgBox "xxx"
Application.Quit
End Sub


--
Jim Rech
Excel MVP
"Tom" wrote in message
...
| Brilliant thank you very much Jim! Thats worked a
| treat. Is there a command that I can specify at the end
| of my macro to close the workbook after the macro has
| ran? I have tried Quit and Close but neither are working.
|
| Thanks once again,
| Tom
|
|
|
| -----Original Message-----
| There is no way in Excel to designate a macro to run
| from the command line
| or when the workbook is opened. You must use one of two
| macros for Excel to
| run them when the workbook is opened. One is Sub
| Auto_Open. The other is
| the Workbook_Open event handler in the ThisWorkbook
| module. Use either of
| these and put your code in it.
|
| Then use Scheduled Tasks to open the workbook.
|
| --
| Jim Rech
| Excel MVP
| "Tom" wrote in
| message
| ...
| | Hi All,
| |
| | I am trying to automate a few Excel Macros, in order
| that
| | I can used scheduled tasks to kick these macros off.
| For
| | instance, I use the AutoExec function in Access a lot,
| | and kick these off using scheduled tasks. I would like
| | to replicate this for some Excel Macros.
| |
| | I have been told to use the Autorun function, with a
| Quit
| | command at the end of the Macro to close the
| spreadsheet
| | after the macro has run. However, I haven't got much
| | Visual Basic knowledge and I can't get this to work.
| If
| | anyone can help it will be very much appreciated.
| |
| | Tom
| |
|
|
| .
|

Automate Macros
 

"Tom" wrote in message ...
Is there a command that I can specify at the end
of my macro to close the workbook after the macro has
ran? I have tried Quit and Close but neither are working.



You can use a VB script to run the macro without even opening the
workbook.

Below is a sample script to run a macro that I found here a while ago:

********

' Here, the full path name of the workbook to open
Const xlName = "Insert the full file name of the workbook here"
Dim xl, Wbk
Set xl = WScript.CreateObject("Excel.Application")
Set Wbk = xl.Workbooks.Open(xlName)
' Here, the workbook macro name to execute (in a standard module)
xl.Run "Insert your macro name here"
Wbk.Save
Wbk.Close False
xl.Quit
Set xl = Nothing

********

If you copy the above script in a text file, customize the worksheet
location and macro name to fit your needs, and save the script with a
".vbs" extension (eg. myscript.vbs), the script will run the macro
whether or not the workbook is open or closed.

Then, instead of using windows scheduler to open the workbook, simply
use the scheduler to open the VB script.