saving a .xlt to a .xls

[TAMU]

Is it possible to set it up so when you have a .xlt file open and do a saveas that it would default to a .xls file in Excel 2000.

 

[byundt]

That's a good feature in older versions of Excel that has been removed from newer versions. Here's a macro that restores the functionality. If the file is a .xlt template and the user tries to save it--the macro displays a Save As dialog window with the same folder and filename (only with .xls extension). If the file already has a .xls extension, then the save proceeds normally.

This macro needs to go in the ThisWorkbook code pane of the template.

If you want to change the default behavior of Excel, then you can create a class module for the Excel application and trap the Save event. I don't recommend this latter approach because users can become confused if things don't behave the way that is expected--but it is possible.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'If file is a .xlt, display Save As dialog window. Default save with the same name and folder, only with .xls extension
On Error GoTo ErrorSub
Dim strFilename As String
Application.EnableEvents = False
strFilename = ActiveWorkbook.Name
If LCase(Right(strFilename, 3)) <> "xlt" Then
    Application.EnableEvents = True
    Exit Sub
End If
strFilename = ActiveWorkbook.Path & Application.PathSeparator & Left(strFilename, Len(strFilename) - 3) & "xls"
strFilename = Application.GetSaveAsFilename(strFilename, "Excel Workbook (*.xls), *.xls")
If LCase(strFilename) <> "false" Then
    ThisWorkbook.SaveAs (strFilename)
End If
    
ErrorSub:
    If Err <> 0 Then
        MsgBox Err.Description, vbCritical, "Error!"
    End If
    Application.EnableEvents = True
    Cancel = True
End Sub