prevent saving in .XLSX format

[hrlngrv]

Some of my co-workers have Excel 2003 while others have 2010, and both groups need to use some files with necessary macros and udfs. [Tangent: yes, I know these could be in separate add-ins, but IT has kittens about add-ins but not about VBA modules in regular workbooks.] For the time being, these files will remain in .XLS format.

As Murphy's Law provides, Excel 2010 displays compatibility warnings about loss of formatting when saving these .XLS files (incorrectly: saving as .XLS doesn't lose anything). Some Excel 2010 users react by cancelling saving as .XLS and save as .XLSX, and thus lose the necessary VBA.

I've spend a few hours bullet-proofing a BeforeSave event handler which detects Save-As operations and replaces the normal Save-As with a custom one which only provides .XLS, .XLSM and .XLSB file formats. It also checks whether the Save-As filename's extension is .XLSX, and if so displays a warning and exits without saving.

My question, now that I've perhaps wasted this time: is there a better/simpler way to prevent saving Excel workbooks in macros-stripped .XLSX format?

 

[xenou]

I don't think there's a better/simpler way than using a before save intercept (that I know of). This isn't dummy-proof as it can be defeated (for instance, by just disabling macros).

If you wanted to try an alternative it would be to click the box that says "don't ask me again" when it comes to the compatibility check. Then when the user hits save it will just save - though that may be a user-defined setting (I'm not sure).

 

[jmacky]

I'm in the same boat - could you post your code for preventing saving as .xlsx? I'm presuming you inserted this code as part of the Before Save routine.

Thanks,
JM