Excel 97/2000 AUTOEXEC

[Bernie Dwyer]

Greetings, programmers! I need some advice and help.

I've googled microsoft.public.excel.*, I've been to the MVP websites,
looked in Excel and VB help, and I can't find a way to make Excel run
some code *every* time it opens a workbook.

The AUTO_OPEN routine (or WORKBOOK_OPEN) is the closest I've come to,
but it has to be contained in the workbook itself (unless I've misread
the help), i.e. it only runs when the file containing it is opened. I
need to run or trigger the same code every time Excel opens a workbook.

The boss lost 2+ hours of work because he thought the AUTOSAVE add-in
was loaded and working - it was loaded, but wasn't turned on. I
suggested (very tactfully) that it might have been switched off while
working on a large (20+MB) spreadsheet file from home while connected to
the network via 64KB ISDN. He said he definately wouldn't have switched
it off. FWIW, I've repeatedly encouraged him to get into the habit of
pressing Ctrl-S every ten or fifteen minutes.

Anyway, I've found some code that verifies the AUTOSAVE add-in has been
loaded, and more code that sets the options the way I want them (thanks
to posts in this group from Michel Cinq-Mars, Mark Driscol, and Dick
Kusleika), but I need Excel to run that code every time it opens a
workbook.

If I place a workbook containing this code, and an AUTO_OPEN to run it
all (with some error trapping), in the XLSTART directory, Excel will run
the AUTO_OPEN once - when Excel starts - but not every time Excel opens
another file.

I've been thinking that I could use the OPEN or ACTIVATE event to
trigger my code, but how would I get the AUTO_OPEN routine contained in
the file in the XLSTARTUP directory to start, then sit and wait or poll
for other file's OPEN event?

Thanks for any help you can give me. And a happy new year to you all!
--

Bernie Dwyer
Dump the z to reply to me
*****************************

[Dave Peterson]

You need an application event. Chip Pearson has some notes (and a downloadable
example workbook) at:

http://www.cpearson.com/excel/AppEvent.htm

I think you'll want to look for App_WorkbookOpen in the code behind the Class
module (maybe App_NewWorkbook, too.)

--

Dave Peterson
ec3...@msn.com

[Bernie Dwyer]

Dave Peterson wrote:
>
> You need an application event. Chip Pearson has some notes (and a downloadable
> example workbook) at:
>
> http://www.cpearson.com/excel/AppEvent.htm
>
> I think you'll want to look for App_WorkbookOpen in the code behind the Class
> module (maybe App_NewWorkbook, too.)
>

Woo Hoo! It works! Great stuff, thanks - and thanks to Chip Pearson,
too. I extracted the various AUTOSAVE properties with some more code
from this group - thanks to Dick Kusleika. Google is your friend :-)

Here's what I run with the App_WorkbookOpen event - this verifies/loads
Autosave and sets the various parameters. You can switch off autosave,
but it will come on again next time you open a workbook or start Excel:
---------------------------------------------------
Sub Autosave_Add_In_Install_Verifier()
'adapted from code by Michel Cinq-Mars and Mark Driscol
' from microsoft.public.excel.programming

If AddIns("Autosave add-in").Installed = False Then
AddIns("Autosave add-in").Installed = True
End If
End Sub
--------------------------------------------------
Sub Autosave_Activation_Verifier()
'adapted from code by Michel Cinq-Mars and Mark Driscol
' from microsoft.public.excel.programming

' turn on autosave
If Workbooks("autosave.xla").Excel4IntlMacroSheets("loc table") _
.Range("ud01n.do_save").Value = False Then
Workbooks("autosave.xla").Excel4IntlMacroSheets("loc table") _
.Range("ud01n.do_save").Value = True
End If

' verify other parameters

' "prompt" should be false (don't ask user)
If Workbooks("autosave.xla").Excel4IntlMacroSheets("loc table") _
.Range("ud01b.prompt").Value = True Then
Workbooks("autosave.xla").Excel4IntlMacroSheets("loc table") _
.Range("ud01b.prompt").Value = False
End If

' "Frequency" should be 10 (every 10 minutes)
If Workbooks("autosave.xla").Excel4IntlMacroSheets("loc table") _
.Range("ud01n.Frequency1").Value <> 10 Then
Workbooks("autosave.xla").Excel4IntlMacroSheets("loc table") _
.Range("ud01n.Frequency1").Value = 10
End If

' "Option" should be 1 ("Save current" not "Save all")
If Workbooks("autosave.xla").Excel4IntlMacroSheets("loc table") _
.Range("ud01n.Save.Option").Value <> 1 Then
Workbooks("autosave.xla").Excel4IntlMacroSheets("loc table") _
.Range("ud01n.Save.Option").Value = 1
End If

End Sub
--------------------------------------------------