Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Autoexec Macro
How can I make a procedure (or macro) run every time a workbook is opened?
I've tried naming it Private Sub Autoexec() and Private Sub Startup() But neither work. I'm sure I've done this before but I just can't remember how. I looked for startup options (like in Access) but I can't find where to name a startup macro. I don't want to use a switch on the startup line. If anyone can help I'd really appreciate it. Thanks, Rick |
#2
|
|||
|
|||
2 ways, either put the macro in ThisWorkbook like
Private Sub Workbook_Open() 'your code here End Sub or name i Auto_Open Sub auto_open()'your code here End Sub I personally use the former, to get there right click the excel icon next to File menu and select view code or press alt + F11 and double click ThisWorkbook in the project pane to the left, then close with alt + Q - Regards, Peo Sjoblom (No private emails please) "Rick" wrote in message ... How can I make a procedure (or macro) run every time a workbook is opened? I've tried naming it Private Sub Autoexec() and Private Sub Startup() But neither work. I'm sure I've done this before but I just can't remember how. I looked for startup options (like in Access) but I can't find where to name a startup macro. I don't want to use a switch on the startup line. If anyone can help I'd really appreciate it. Thanks, Rick |
#3
|
|||
|
|||
I am trying to "call" Excel from the DOS Command line (I do this in Word and
it works fine): call "C:\Program Files\Microsoft Office\OFFICE11\excel.exe" /mPERSONAL.XLS!SusCancel 20050810.xls I have saved the Macro into the "personal.xls" workbook. It ain't getting control (it ain't workin'). Advice... "Rick" wrote: How can I make a procedure (or macro) run every time a workbook is opened? I've tried naming it Private Sub Autoexec() and Private Sub Startup() But neither work. I'm sure I've done this before but I just can't remember how. I looked for startup options (like in Access) but I can't find where to name a startup macro. I don't want to use a switch on the startup line. If anyone can help I'd really appreciate it. Thanks, Rick |
#4
|
|||
|
|||
Yes. Excel and Word are two different programs.
If you want your macro to always run when you open that file, you can name it auto_open (in a general module). Noe wrote: I am trying to "call" Excel from the DOS Command line (I do this in Word and it works fine): call "C:\Program Files\Microsoft Office\OFFICE11\excel.exe" /mPERSONAL.XLS!SusCancel 20050810.xls I have saved the Macro into the "personal.xls" workbook. It ain't getting control (it ain't workin'). Advice... "Rick" wrote: How can I make a procedure (or macro) run every time a workbook is opened? I've tried naming it Private Sub Autoexec() and Private Sub Startup() But neither work. I'm sure I've done this before but I just can't remember how. I looked for startup options (like in Access) but I can't find where to name a startup macro. I don't want to use a switch on the startup line. If anyone can help I'd really appreciate it. Thanks, Rick -- Dave Peterson |
#5
|
|||
|
|||
Thanks to all (or one or two of you), after you read this I need help on
making sure this application (Excel) performs all functions with a return code = 0, I saw soemthing on this but what I am really looking for is a way to insure that the VBS script did everything = OK, no failures along the way...Thanks in advance... Here is what I am using now: a batfile that calls the vbs file batfile: ====== REM CANCEL file processing rem files ready if not exist CAN-*.xls exit /b 9 rem prep if exist cancel.xls del cancel.xls rem set variables set file=CAN-*.xls set dir=toclient\ set backup=bkup\ for %%i in ( %dir%%file% ) do ( copy /b %dir%%%~nxi %backup%%%~nxi ) && ( call backup.bat %backup%,%%~nxi ) && ( cancel.vbs "%dir%%%~nxi") vbs file: ====== Dim macro Dim filename Dim XLApp Dim XLWkb Set XLApp = CreateObject("Excel.Application") xlapp.visible = true xlapp.Workbooks.Open "C:\DOCUME~1\xxx\APPLIC~1\Microsoft\Excel\XLSTART\ PERSONAL.XLS" filename = WScript.Arguments.item(0) xlapp.Workbooks.Open filename macro = "Personal.xls!Cancel" xlapp.run macro xlapp.ActiveWorkbook.Close xlapp.Application.Quit =================================== "Dave Peterson" wrote: Yes. Excel and Word are two different programs. If you want your macro to always run when you open that file, you can name it auto_open (in a general module). Noe wrote: I am trying to "call" Excel from the DOS Command line (I do this in Word and it works fine): call "C:\Program Files\Microsoft Office\OFFICE11\excel.exe" /mPERSONAL.XLS!SusCancel 20050810.xls I have saved the Macro into the "personal.xls" workbook. It ain't getting control (it ain't workin'). Advice... "Rick" wrote: How can I make a procedure (or macro) run every time a workbook is opened? I've tried naming it Private Sub Autoexec() and Private Sub Startup() But neither work. I'm sure I've done this before but I just can't remember how. I looked for startup options (like in Access) but I can't find where to name a startup macro. I don't want to use a switch on the startup line. If anyone can help I'd really appreciate it. Thanks, Rick -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with macro looping and color query function | Excel Discussion (Misc queries) | |||
Issuing macro in workbook from separate workbook | Excel Discussion (Misc queries) | |||
Playing a macro from another workbook | Excel Discussion (Misc queries) | |||
Date macro | Excel Discussion (Misc queries) | |||
Macro and If Statement | Excel Discussion (Misc queries) |