Forcing a FileSaveAs

[John Petty]

Is there a way that I can force the user to save a
template to an XLS upon opening the template (i.e. Sub
open_Workbook)

 

[Orlando Magalhães Filho]

Hi Jonh Petty,

Why don't you save your template as Excel template, XLT file?

HTH

 

[Tom Ogilvy]

As soon as it is opened using File=>New, it is an xls, so you could
certainly do it in the workbook_Open event.

 

[John Petty]

Thanks Tom.

-----Original Message-----
As soon as it is opened using File=>New, it is an xls, so you could
certainly do it in the workbook_Open event.

--
Regards,
Tom Ogilvy




.

 

[John Petty]

Oops. Sorry, didn't work. The file that is opened is a
template (aka XLT). But if a template is opened from
within Excel, you can overwrite it. Our users are usually
pretty good at saveas, but on occasion (when stress
overrides time), they will fill out the form and save
before sending through routing. This causes a problem for
the next user. I initially tied in some code that cleared
the contents, but that is really a bandaid and not a true
fix. What I am looking for is a way to foolproof a save
function.

 

[Tom Ogilvy]

Of course if you edit it as an xla, you would need to disable events or hold
down the shift key when you open it.

 

[Tom Ogilvy]

The proper way to use an XLT is to put it in the template directory and then
you do

file=New and select this template.

It is opened as an XLS and automatically has a new name.

If you navigate to the template and open it, then there is no advantage to
making it a template - you are just opening a workbook.

In your workbook.Open you can check to see what the situation is

Private sub Workbook_Open()
if thisworkbook.Fileformat = xlTemplate then Exit sub
if thisworkbook.FileFormat = xlWorkbookNormal then
if thisworkbook.FullName <> thisworkbook.Name then
' workbook has been saved before
exit sub
else
' code to force saveas
end if
End if
End sub