Saving as a macro enabled worksheet

Question

I have an app I inherited (originally created in Excel XP) which is distributed as a template. The app forces a save which is not working under 2010.

In the BeforeClose and Before Save events of ThisWorkbook it calls a form which has some textboxes to fill in data that will be used to generate a name. Then there is a Save button with the following code:

    name = "S:\PRECIPART MASTER\Quotes-Customers\Excel Spreadsheet Quotes\Quote " & TextBox3 & Customer & preciPN & " (" & TextBox1 & ")"

'    Path = Application.GetSaveAsFilename(name, fileFilter:="Excel Files (*.xls; *.xlsx;),*.xls;*.xlsx") 'Displays the standard Save As dialog box and gets a file name from the user without actually saving any files

    Path = Application.GetSaveAsFilename(name, fileFilter:="Excel Files (*.xlsm),*.xlsm") 'Displays the standard Save As dialog box and gets a file name from the user without actually saving any files

    If Path <> "False" Then ThisWorkbook.SaveAs Path 'Saves changes to the workbook in a different file

(Note I commented out the first Path line to force a save as a xlsm file.)

But this code is giving me the following error:

The following features cannot be saved in macro-free workbooks:

o VB project

To save a file with these features, clikc No, and then choose a macro-enabled file type in the File Type list.

To continue saving as a macro-free workbook, click Yes.

But I am saving as an xlsm! so why doesn't it like it or what am I doing wrong?

Answer 1

I'm actually not what the -4143 equates to.  I suspect it is saying "whatever default format has been set in Excel".

With Excel 2007/2010 the filename extension needs to match the specified FileFormat parameter (which is the position where your -4143 is showing up at).  If you don't specify a FileFormat parameter, it saves in the format setup in Excel as the default which can be .xls, .xlsx or .xlsm (and probably others such as .xlts and .xltm for templates).

Best to match explicitly using code like

ThisWorkbook.SaveAs  FilePath, fileformatParameter

Where fileformatParameter can be:

xlExcel8 (has numeric value of 56) to save with .xls file extension

xlOpenXMLWorkbookMacroEnabled (has numeric value of 52) (use with .xlsm files)

or

xlOpenXMLWorkbook (has numeric value of 51) (use with .xlsx files)

As for working with the template and saving it without triggering the Workbook_BeforeClose() event, here is one way:

Just before you get ready to save the file as a template file again, go into the VB Editor and type this into the Immediate window:

Application.EnableEvents=False [Enter]

That will prevent any events, including the _BeforeClose() event, from firing off.  You can then save the file without the annoyance of the forced save to a different file type.

The problem with the Application.EnableEvents=False statement is that it is persistent, so no other events will fire until it is re-enabled.  You could go back into the VB Editor and type this into the Immediate window:

Application.EnableEvents=True [Enter]

or just shut down Excel and re-open it, doing that will reset the .EnableEvents feature to =True.

Answer 2

Even though you have specified an extension of .xlsm, your SaveAs command is still saving the file as non-macro enabled. Try the following:

If Path <> "False" Then ThisWorkbook.SaveAs Filename:=Path, FileFormat:=xlOpenXMLWorkbookMacroEnabled

Answer 3

As for working with the template and saving it without triggering the Workbook_BeforeClose() event, here is one way:

Just before you get ready to save the file as a template file again, go into the VB Editor and type this into the Immediate window:

Application.EnableEvents=False [Enter]

That will prevent any events, including the _BeforeClose() event, from firing off.  You can then save the file without the annoyance of the forced save to a different file type.

The problem with the Application.EnableEvents=False statement is that it is persistent, so no other events will fire until it is re-enabled.  You could go back into the VB Editor and type this into the Immediate window:

Application.EnableEvents=True [Enter]

or just shut down Excel and re-open it, doing that will reset the .EnableEvents feature to =True.

 

Thanks JL

I'm printing that out and putting it my Tips book as I'm sure I will need it again in the future.

Answer 4

OK, I think I finally got it. LifeScholar, your suggestion worked. I was able to save a macro enabled sheet. I was then able to use THAT file to save as a template.

Answer 5

Even though you have specified an extension of .xlsm, your SaveAs command is still saving the file as non-macro enabled. Try the following:

If Path <> "False" Then ThisWorkbook.SaveAs Filename:=Path, FileFormat:=xlOpenXMLWorkbookMacroEnabled

Ok, That may solve another problem I came across. I did get it to save with an xlsm extension, but when I try to open it, I get a wrong file type extension. I found another suggestion to use this:

If Path <> "False" Then ThisWorkbook.SaveAs FilePath, -4143

Does the -4143 do the same as the xlOpenXMLWorkbookMacroEnabled parameter?

But, how do I save this as a template, when this save code automatically runs whenever I try to save???