none
Can I programmatically force saving an XLTM template to save as an XLSM file in Excel 2010 without defaulting all Excel file types? RRS feed

  • Question

  • I created an Excel template which includes VB and macros as an EXCEL.XLTM template file (I used a template file to keep users from overwriting the sample template file), but I have noticed that when users save the template, Excel 2010 defaults to saving the new file as a XLSX file (removing all the macros).

    I know you can set Excel 2010 to always save as a certain format, but I really would prefer not forcing all Excel files to be macro enabled for the user

    Is there a way to default the Save type to XLSM for this template only?  Like some code that I could put in that when the users saves, it saves as a macro enabled XLSM type only for this template file?

    My users aren't exactly Excel savvy, so I wanted to make it as simple as possible for them.

    Thanks!


    Alan Edwards

    Tuesday, March 17, 2015 3:31 PM

Answers

  • Hi Alan,

    >> Is there a way to default the Save type to XLSM for this template only?  Like some code that I could put in that when the users saves, it saves as a macro enabled XLSM type only for this template file?

    In my option, you could achieve this by adding Application.GetSaveAsFilename Method to the workbook BeforeSave event. Some key code like below:

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim FileNameVal As String
    If SaveAsUI Then
        FileNameVal = Application.GetSaveAsFilename(, "Excel Macro-Enabled Workbook (*.xlsm), *.xlsm")
        Cancel = True
        If FileNameVal = "False" Then 'User pressed cancel
            Exit Sub
        End If
        ThisWorkbook.SaveAs Filename:=FileNameVal & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled
        Application.EnableEvents = True
    End If
    End Sub

    But there is a limitation of this workaround, in this workaround, the save as will only support the macro enabled type, other save as types like "xlsx" will be ignored.​

    For more information about Application.GetSaveAsFilename Method (Excel), you could turn to the link below:

    #Application.GetSaveAsFilename Method (Excel)
    https://msdn.microsoft.com/en-us/library/office/ff195734.aspx

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, March 19, 2015 2:41 AM

All replies

  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel, I'll move your question to the MSDN forum for Excel

    http://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev&filter=alltypes&sort=lastpostdesc

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Thanks

    George Zhao
    Forum Support
    ________________________________________
    Come back and mark the replies as answers if they help and unmark them if they provide no help.
    If you have any feedback on our support, please click "tnfsl@microsoft.com"

    Wednesday, March 18, 2015 4:09 AM
  • Hi Alan,

    >> Is there a way to default the Save type to XLSM for this template only?  Like some code that I could put in that when the users saves, it saves as a macro enabled XLSM type only for this template file?

    In my option, you could achieve this by adding Application.GetSaveAsFilename Method to the workbook BeforeSave event. Some key code like below:

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim FileNameVal As String
    If SaveAsUI Then
        FileNameVal = Application.GetSaveAsFilename(, "Excel Macro-Enabled Workbook (*.xlsm), *.xlsm")
        Cancel = True
        If FileNameVal = "False" Then 'User pressed cancel
            Exit Sub
        End If
        ThisWorkbook.SaveAs Filename:=FileNameVal & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled
        Application.EnableEvents = True
    End If
    End Sub

    But there is a limitation of this workaround, in this workaround, the save as will only support the macro enabled type, other save as types like "xlsx" will be ignored.​

    For more information about Application.GetSaveAsFilename Method (Excel), you could turn to the link below:

    #Application.GetSaveAsFilename Method (Excel)
    https://msdn.microsoft.com/en-us/library/office/ff195734.aspx

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, March 19, 2015 2:41 AM
  • Hi Edward,

    That works really nice!  It's doing exactly what I wanted.

    Now when the user uses this template, it will default to saving the macro enabled format.

    Thank you so much!

    Alan


    Alan Edwards


    • Edited by Alan Edwards KAE Thursday, March 19, 2015 2:17 PM typo-gramatical error
    Thursday, March 19, 2015 2:06 PM
  • Hi Alan,

    I am glad your issue has been resolved, if you have any other issues, please feel free to post in this forum.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, March 20, 2015 5:08 AM