How do I force saving as *.xlsm without using .SaveAs

Originally Posted by AlphaFrog

I think that's a permanent (beyond the current session) change. You could store their current setting, change to macroenabled, then restore the original when closing the workbook.

'Standard code module declare a public variable
Public OrigSaveFormat As Long

'Before they save
OrigSaveFormat = Application.DefaultSaveFormat
Application.DefaultSaveFormat = xlOpenXMLWorkbookMacroEnabled

'Before workbook close
Application.DefaultSaveFormat = OrigSaveFormat

Having just tested it with our set up here, it seems that after the setting has been changed the new workbook still "wants to be" a .xlsx file. However, if I shut down Excel and relaunch it, then run the macro again without instructing another change to the default save as file type, then the new workbook wants to be a .xlsm file.

It seems as though the setting is only read by Excel at Excel startup time. Would that be right? I suppose this behaviour could also be something to do with that TRIM add-in.

Like I say though, it doesn't matter for my purposes as a permanent change to this setting is fine so thanks to you I can now test for this and display a short message asking the user to shutdown and relaunch Excel if the setting isn't correct when the workbook is opened which should naturally only appear once (or if the user changes the setting manually from .xlsm at any point)

Originally Posted by kadeo

It seems as though the setting is only read by Excel at Excel startup time. Would that be right? I suppose this behaviour could also be something to do with that TRIM add-in.

I really don't know. I'll take your word for it if you've tested it.

What if you just saved the workbook as a temporary .xlsm file? Then later delete the temp file after the workbook is closed or SavedAs another file.

Originally Posted by AlphaFrog

...What if you just saved the workbook as a temporary .xlsm file? Then later delete the temp file after the workbook is closed or SavedAs another file.

Yeah, I had thought of that. In fact that's how I get around another issue with the TRIM add-on that doesn't allow me to specify a name for saving a new file as. The only way you can name a new file being saved to TRIM is to do it in the TRIM save dialog so my macros save to a temp file so that when I then save that to TRIM its no longer a new file and TRIM just uses the name the file already has. Thats OK for me cos I know what's happening and know to hit SaveAs to get it into TRIM. Others though are used to just hitting Save on a new document and as per normal Excel operation, if the file hasn't been previously saved, the Save function operates as SaveAs. So doing it this way risks the user just saving to the temporary file location instead of into TRIM. Using Workbook_BeforeSave to alter this brings me right back to original issue of inserting code into a new workbook. It would be so much easier if I could just get my hands on whatever API the TRIM add-in uses but it has eluded my efforts thus far.