VBA script on document open or close

[Joseph N.]

I'm trying to put into Excel a procedure that I have put into Word
(through the assistance of Greg Maxey). There seems to be two issues:
making some procedure operate by default, and using the correct code.

For the first problem, I followed the online help and tried to put the
code in a workbook and then saved the workbook as
\\%userprofile%\application data\microsoft\templates\book.xlt . When
that did not work, I saved it as c:\program files\microsoft
office\office11\xlstart\book.xlt . That did not work either, so I
don't know if the process was incorrect or if the code--which is
verbatim the same as I used in Word--is incorrect (since I know next to
nothing about VBA), or something else is incorrect.

Here's the code I used:

Sub AutoOpen()
If ActiveDocument.BuiltInDocumentProperties("Title") = "" Then
With Dialogs(wdDialogFileSummaryInfo)
.Show
End With
End If
End Sub

Sub AutoClose()
If ActiveDocument.BuiltInDocumentProperties("Title") = "" Then
With Dialogs(wdDialogFileSummaryInfo)
.Show
End With
End If
End Sub

So... can anyone point me in the right direction on making Excel
always prompt for a Title if the Title properties field is blank? (And
the reason I need it on both open and close has to do with automatic
document creation generated sometimes by a different program.)

TIA

 

[Helmut Weber]

Hi Joseph,

I don't know about the path issue,
I avoid paths with spaces.

As for the second part of your question,
there are subtle differences between WordVBA and ExcelVBA.

And subtle differences are harder to control then obvious ones.

This one works for me:

Sub auto_close()
If ThisWorkbook.BuiltinDocumentProperties("Title") = "" Then
Application.Dialogs(xlDialogSummaryInfo).Show
End If
End Sub

Sub auto_open()
If ThisWorkbook.BuiltinDocumentProperties("Title") = "" Then
Application.Dialogs(xlDialogSummaryInfo).Show
End If
End Sub

AutoOpen (Word) vs. Auto_Open (Excel)
Autoclose (Word) vs. Auto_Close (Excel)

And many more differences...

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

on strange territory

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[Joseph N.]

Thanks Helmut. However, that code does not work for me.

I pasted it into the VB editor area of a workbook, making sure that it
was the workbook itself and not a template or add-in to which I was
adding the code. I digitally signed it with an accepted certificate,
since my macro security is set to "high," then I saved the workbook as
book.xlt . Following Microsoft's instructions, I first saved it in the
location of my templates, but that didn't work. Then I tried saving it
in the program's folder for startup templates, and that didn't work.
Two problems occurred: first, new workbooks did not adopt that .xlt
file as a template. Second, even when I pasted that code into a
workbook, it did not display the desired behavior. I am using Office
2003 with all updates. Any idea what could be the problem?

JN

 

[NickHK]

Joseph,
I guess you are trying to replace the default Excel workbook that is created
when you click File>New, click the New icon.
I seems to remember (when using Excel2000) altering the files "Book.xls" and
Sheet.xls" so all new WBs and WSs create incorporate these changes. However,
now I'm using Excel2002, I do not find these files on my system, so can't
tell you from which file(s) Excel generates these from, i.e. Excel
equivalent to Word's Normal.dot.

You can achieve the same result in code with Application level Events though
www.cpearson.com/excel/AppEvent.htm

NickHK

 

[Dave Peterson]

Helmut's code worked fine for me (xl2003, no signatures, macros set to low).

Make sure you updated your only copy of book.xlt.

Excel is pretty smart. If you upgraded over a previous version, you'll find
that excel can still find the previous XLStart folder. And if there's a
book.xlt workbook template in that folder, you may be updating the wrong
version.

I'd spend a little time making sure that there are files in only one XLStart
folder. And I'd use the location that excel expects.

You can use this to technique to find that folder.

Open the VBE and hit ctrl-g and type this and hit enter:
?application.startuppath

For me with xl2003 and winXP (home), I get this:
C:\Documents and Settings\(username)\Application Data\Microsoft\Excel\XLSTART

I'd close excel and delete (or move for backup purposes only) all the files I
find in other XLStart folders and only use this XLStart folder.

Then make sure you have Book.xlt in that XLStart folder.

I have the following files in my XLStart folder:
personal.xla
book.xlt
sheet.xlt

Then when you click on the New icon on the standard toolbar, excel will use that
book.xlt workbook to create a new workbook. It won't (and shouldn't!) open the
file as a template. It won't create a workbook with .xlt as an extension--in
fact, there shouldn't be any extension on that new workbook at all.

The name of the workbook will be book1 (or book2 or book3...). It won't get the
extension until you save the file (as a normal workbook???).

 

[Gord Dibben]

Joesph

Open a new workbook. Customize your as you wish.

File>Save As Type: scroll down to Excel Template(*.XLT) and select. Name your
workbook "BOOK"(no quotes). Excel will add the .XLT to save as BOOK.XLT.

Store this workbook in the XLSTART folder usually located at........

C:\Documents and Settings\username\Application Data\Microsoft\Excel\XLSTART

This will be the default workbook for File>New or the Toolbar button File>New

Do not use File>New...Blank Workbook or you will get the Excel default workbook.

NOTE: Existing workbooks are not affected by these settings.

You can also open a new workbook and delete all but one sheet. Customize as
you wish then save this as SHEET.XLT in XLSTART folder also. It now becomes
the default Insert>Sheet.

More can be found on this in Help under "templates"(no quotes).


Gord Dibben Excel MVP

Joseph,
I guess you are trying to replace the default Excel workbook that is created
when you click File>New, click the New icon.
I seems to remember (when using Excel2000) altering the files "Book.xls" and
Sheet.xls" so all new WBs and WSs create incorporate these changes. However,
now I'm using Excel2002, I do not find these files on my system, so can't
tell you from which file(s) Excel generates these from, i.e. Excel
equivalent to Word's Normal.dot.

You can achieve the same result in code with Application level Events though
www.cpearson.com/excel/AppEvent.htm

NickHK

Gord Dibben MS Excel MVP

 

[NickHK]

That's what I remembered, but as I do not currently have such files on my
system, where is Excel retrieving these default files from ?

NickHK

Joesph

Open a new workbook. Customize your as you wish.

File>Save As Type: scroll down to Excel Template(*.XLT) and select. Name your
workbook "BOOK"(no quotes). Excel will add the .XLT to save as BOOK.XLT.

Store this workbook in the XLSTART folder usually located at........

C:\Documents and Settings\username\Application Data\Microsoft\Excel\XLSTART

This will be the default workbook for File>New or the Toolbar button File>New

Do not use File>New...Blank Workbook or you will get the Excel default workbook.

NOTE: Existing workbooks are not affected by these settings.

You can also open a new workbook and delete all but one sheet. Customize as
you wish then save this as SHEET.XLT in XLSTART folder also. It now becomes
the default Insert>Sheet.

More can be found on this in Help under "templates"(no quotes).


Gord Dibben Excel MVP

 

[Gord Dibben]

Nick

Are the new workbooks and sheets customized?

If so, you must have a template in one of your XLSTART folders.

Excel's default File>New...Blank Workbook and Insert>Worksheet are not
customized.

AFAIK no one has figured out how to customize the New...Blank Workbook default.

Note the ellipsis after New

Customized workbooks saved as BOOK.XLT will open as Book1 when you hit File>New
or hit the New Icon on the Standard Toolbar.

See also Dave's response.


Gord

That's what I remembered, but as I do not currently have such files on my
system, where is Excel retrieving these default files from ?

NickHK

 

[NickHK]

I was curious as to where the uncustomized, default WB and WS are generated
from, as there is no apparent template for Excel to use.
Yes, if I customize either and save it to XLSTART, Excel will use those, but
before that..
Not vital, just made me wonder.

NickHK

Nick

Are the new workbooks and sheets customized?

If so, you must have a template in one of your XLSTART folders.

Excel's default File>New...Blank Workbook and Insert>Worksheet are not
customized.

AFAIK no one has figured out how to customize the New...Blank Workbook default.

Note the ellipsis after New

Customized workbooks saved as BOOK.XLT will open as Book1 when you hit File>New
or hit the New Icon on the Standard Toolbar.

See also Dave's response.


Gord