Autoexec Macro

[Rick]

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

[Peo Sjoblom]

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>

[ph8]

I have also been looking for the same thing. I tried using a sub with both the suggested names and neither of them worked for me. Is there something else I have to do? Is there another way?

Thank you for your time.

v/r
Eddie

[Chip Pearson]

If you use the Workbook_Open event procedure, it must be in the
ThisWorkbook module. If you use the Auto_Open procedure, it must
be in a regular code module, not the ThisWorkbook module.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"ph8" <[email protected]> wrote in
message news:[email protected]...
>
> I have also been looking for the same thing. I tried using a
> sub with
> both the suggested names and neither of them worked for me. Is
> there
> something else I have to do? Is there another way?
>
> Thank you for your time.
>
> v/r
> Eddie
>
>
> --
> ph8
> ------------------------------------------------------------------------
> ph8's Profile:
> http://www.excelforum.com/member.php...o&userid=19871
> View this thread:
> http://www.excelforum.com/showthread...hreadid=381486
>

[Dave Peterson]

And make sure you enable macros.

Tools|macro|security|security level tab

(after you change this, close and reopen your workbook.)

ph8 wrote:
>
> I have also been looking for the same thing. I tried using a sub with
> both the suggested names and neither of them worked for me. Is there
> something else I have to do? Is there another way?
>
> Thank you for your time.
>
> v/r
> Eddie
>
> --
> ph8
> ------------------------------------------------------------------------
> ph8's Profile: http://www.excelforum.com/member.php...o&userid=19871
> View this thread: http://www.excelforum.com/showthread...hreadid=381486

--

Dave Peterson

[Noe]

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]

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

[Noe]

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
>