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

I've come up against a problem I can't think my way around so was hoping you guys could help.

We use Excel 2007 at work and I created a macro that validates data. Once the data has been validated with no errors, the macro makes available a button which when clicked calls a macro to copy the validated data to a new workbook.

My organisation uses a document management system called TRIM. TRIM has a COM add-in for MS Office that takes care of the saving so when the save button is clicked or the save/save-as menu item is selected, the add-in launches TRIM's save dialog and not the usual windows/office dialog. This save dialog does not give you the option to save the file as a particular type so will just automatically save the file into TRIM as whatever type the document was created as.

After help I've previously received from Norie, the way the "copy data" macro works is as follows: A new workbook is created; a hidden sheet with code already in the code module for that sheet is copied over to the new workbook; the data that has just been successfully validated is then copied and pasted into that new sheet in the new workbook. The result is a new workbook containing a sheet of validated data and a macro in the code module for that sheet. Which has been working well for me since I created it last year.

The time is coming now to roll this out to my colleagues to use but it has occurred to me that because I write macros all day long, I have changed the Save options in Excel Options to automatically create new documents as macro enabled.

Save Options.png

This means that the TRIM add-in automatically saves the new document as an Excel Macro-Enabled Workbook (*.xlsm) document. My colleagues however will have theirs setup as the standard Excel Workbook (*.xlsx) which means when they try to save, the TRIM add-in will automatically save it as those file types which will throw up warnings and prevent the save because that extension cannot include macros (or more accurately, it just saves it without the warnings and the result is the code is stripped out)

I had considered working out some kind of Workbook_BeforeSave code to capture the user's save event but the problem with that approach is the same as the problem that gets mentioned in the post I linked to above in that I'll need to insert code into the new workbook. So I considered forcing a save in the same macro that copies the data as soon as the data is copied with something like this:

TheFileName = "Example.xlsm"
wBook.SaveAs TheFileName, xlOpenXMLWorkbookMacroEnabled

But it turns out that the TRIM add-in only intercepts UI initiated save commands and so a VBA initiated save command actually launches the usual Windows/Office save dialog which is no use as you can't save to TRIM from there.

Can anybody think of a way around this? Like can I use VBA to tell Excel to save a file but make Excel "think" the command came from a UI selection? Or is there some way for me to view the code of a COM add-in so I can find out what procedure I'd need to call within that add-in?

My posts always seem to be very long so apologies, but I'm a great believer in explaining a situation properly. That said, if there's more I can add, please let me know.

Thanks for any help you can give me.

This will call up the SaveAs dialog. I'm not sure if that's what you're looking for.

TheFileName = "Example.xlsm"
Application.Dialogs(xlDialogSaveAs).Show TheFileName, xlOpenXMLWorkbookMacroEnabled

Originally Posted by AlphaFrog

This will call up the SaveAs dialog. I'm not sure if that's what you're looking for.

TheFileName = "Example.xlsm"
Application.Dialogs(xlDialogSaveAs).Show TheFileName, xlOpenXMLWorkbookMacroEnabled

Thanks AlphaFrog. I tested that and it just bring up the Windows/Office SaveAs dialog too.

Then I'm not quite sure what you're asking for.

This will change Excel's default SaveAs file format to macro enabled.

Application.DefaultSaveFormat = xlOpenXMLWorkbookMacroEnabled

Originally Posted by AlphaFrog

Then I'm not quite sure what you're asking for.

This will change Excel's default SaveAs file format to macro enabled.

Application.DefaultSaveFormat = xlOpenXMLWorkbookMacroEnabled

Now that is perfect AlphaFrog, that'll suit my needs brilliantly!

I was literally just about to post a separate thread there asking how to determine what a user's default file format is set to so I could popup some convoluted instructions to the user to change it if it wasn't set up as xlsm already. I didn't think I'd just be able to go ahead and change it with VBA myself. You read my mind entirely, and you said you weren't quite sure what I was asking for! lol

It doesn't matter either way for my purposes, but just so I know, does that create a permanent change or a change that's only valid for that session?

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

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.