none
SaveAs Only Works With *.xls -- How To Save In *.xltx Template Format RRS feed

  • Question

  • I'm using VS 2008 and programming with VB.net.  I'm trying to save a workbook that has only one worksheet in it.  This worksheet is a template that will be used quite frequently in many different workbooks/reports.  The user will create this template, be prompted for the file name and save it to a default folder.

    Here is the code I was using to save the file:

    Globals.ThisAddIn.Application.ActiveWorkbook.SaveAs("C:\Templates\" & userFileName & ".xltx", Excel.XlFileFormat.xlTemplate)

    This code does save the workbook.  But, when I try to open the newly saved workbook, Excel does open but I get a message box that states "file format or file extension is not valid"

    I read, in this thread (SaveAs Excel .xlsx Method), that the SaveAs method only works with .xls format.  So I changed the code above to this:

    Globals.ThisAddIn.Application.ActiveWorkbook.SaveAs("C:\Templates\" & userFileName & ".xls", Excel.XlFileFormat.xlWorkbookNormal)

    This is working fine and does reopen as expected.

    This brings up 3 questions for me:

    1.  How can I save the workbook in .xltx template format and have it open without any problems?

    2.  What is the advantage of the template format?

    3.  Is the template format something I need to worry about in this situation?

    Saturday, April 7, 2012 3:13 PM

Answers

  • I think the problem for the template is that you added  ".xltx"  to the path. The path should end with just the filename and the extension property handles the format.  However, I can't explain why it worked for a normal workbook extension. 

    Regarding the comment that the saveas only works for normal workbook formats is not correct.  I just tested it and Workbooks can be saved as template files.

    The advantage of a template format is that when opened, a new workbook is generated based the template. All of the headers, footers, formatting of cells, etc are retained.  Think of the template as a master that does not get destroyed and it is the starting point for all new workbooks based on this template's formatting. 


    Kind Regards, Rich ... http://greatcirclelearning.com

    Monday, April 9, 2012 4:35 PM

All replies

  • Hi fdegree,

    Thanks for posting in the MSDN Froum.

    Would you please tell me you Excel version for further research?

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us

    Monday, April 9, 2012 6:36 AM
    Moderator
  • Hi Tom,

    I am using VB 2008 to create a Ribbon control add-in for Excel 2007.  I'm using Excel 2010 to test the code.

    Thanks...

    Monday, April 9, 2012 12:22 PM
  • I think the problem for the template is that you added  ".xltx"  to the path. The path should end with just the filename and the extension property handles the format.  However, I can't explain why it worked for a normal workbook extension. 

    Regarding the comment that the saveas only works for normal workbook formats is not correct.  I just tested it and Workbooks can be saved as template files.

    The advantage of a template format is that when opened, a new workbook is generated based the template. All of the headers, footers, formatting of cells, etc are retained.  Think of the template as a master that does not get destroyed and it is the starting point for all new workbooks based on this template's formatting. 


    Kind Regards, Rich ... http://greatcirclelearning.com

    Monday, April 9, 2012 4:35 PM
  • I think I tried to save it without the *.xltx extension and it didn't work either.  But, I tried quite a few different combinations that I may be mistaken.

    Thanks for the explanation of the template advantages, that really helps.  I doubt I need to save these workbooks in a template format then...this makes it a little easier on me.

    Thanks!!!

    Monday, April 9, 2012 9:35 PM