+ Reply to Thread
Results 1 to 10 of 10

How to Make Forced Save As XLSM Work?

  1. #1
    Registered User
    Join Date
    08-24-2010
    Location
    California
    MS-Off Ver
    Excel 2007, 2010
    Posts
    45

    Unhappy How to Make Forced Save As XLSM Work?

    The purpose of having forced Save-As-XLSM is so that users will not accidentally save as XLSX and lose all the programming. I found a couple pieces of code online about forced Save As XLSM. Even though the users in these posts claimed they worked, they never worked for me. I wonder if anybody know if I'm doing anything wrong. I'm using Excel 2010 on Windows 7. Thanks so much in advance for your help!

    1. This piece does not do anything. If I click Save As, then pick/write the new file name to be saved at, click Save, it will just cancel the action and does nothing at all. However, if I comment out the "CANCEL = TRUE" line, it will behave exactly like #2 code below: it will prompt me to select a file with the XLSM filter, then prompt me to select the file again with a XLSX filter. After I correct the filter to XLSM and provide a new filename, it will save the new file. But it completely defects the purpose of automatically force saving in XLSM.
    (original post from http://www.ozgrid.com/forum/showthread.php?t=147820)

    Please Login or Register  to view this content.
    2. This piece will first prompt the Save-As file browse window with a default "XLSM" type, but once you put in a filename and click Save, it will pop up the same Save-As file browse window again, only this time having the default "XLSX" type. If you provide the new file name and reselect to XLSM to save, it will actually do Save-As under the new name, it completely defeats the purpose of automatically force saving to XLSM.
    (Original Post http://www.mrexcel.com/forum/excel-q...-workbook.html)

    Please Login or Register  to view this content.

  2. #2
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: How to Make Forced Save As XLSM Work?

    Hi m3,

    The following should help you save as your choice:
    a. .xls
    b. .xlsx
    c. .xlsm

    Lewis
    Please Login or Register  to view this content.

  3. #3
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: How to Make Forced Save As XLSM Work?

    Another way:

    Please Login or Register  to view this content.
    Entia non sunt multiplicanda sine necessitate

  4. #4
    Registered User
    Join Date
    08-24-2010
    Location
    California
    MS-Off Ver
    Excel 2007, 2010
    Posts
    45

    Re: How to Make Forced Save As XLSM Work?

    Hi Lewis,

    Thank you for the suggestion. I need to trigger the forced Save-As XLSM from user clicking the File -> Save As option and allow users to choose their own filenames. So I have to put it in the ThisWorkbook's Workbook_BeforeSave() event, instead of using any procedure/module. I took the part of your code for XLSM and put it in the BeforeSave() event. Interestingly enough, after entering the new filename, Excel prompt me to pick a file with the XLSX filter again. I think this is really the core of the issue I'm facing all along - nowhere in the code did I specify using a XLSX filter, I always explicitly specify the XLSM filter. I wonder why Excel will always default to the XLSX filter??

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    08-24-2010
    Location
    California
    MS-Off Ver
    Excel 2007, 2010
    Posts
    45

    Re: How to Make Forced Save As XLSM Work?

    Thanks shg!

    I tried it but it did nothing just like my original code. It would not save. If I comment out the "Cancel = True", it'll do the XLSX prompt. If I leave it as it, it will do nothing.

  6. #6
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: How to Make Forced Save As XLSM Work?

    Maybe:

    Please Login or Register  to view this content.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  7. #7
    Registered User
    Join Date
    08-24-2010
    Location
    California
    MS-Off Ver
    Excel 2007, 2010
    Posts
    45

    Re: How to Make Forced Save As XLSM Work?

    xladept,

    Thanks for reminding me. That is the stranger part. Even though the two pieces of code above didn't have that "FileFormat:=52" argument, I did test them. And I tested them again by adding them back to the code. It didn't work consistently. The first time I added back the FileFormat argument, the 2nd pop-up for file selection did show XLSM as the file type. I would have been able to live with it if that result is consistent. But when I tested it more, the 2nd pop-up for file selection has been showing XLSX every single time. I tried both "FileFormat:= 52" and FileFormat := xlOpenXMLWorkbookMacroEnabled" (as someone mentioned the numeric value being used is different for Windows and Mac) but the results were the same. This is really puzzling.

  8. #8
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: How to Make Forced Save As XLSM Work?

    m3,

    With a little research and some sweat I think I have a solution (tested using Excel 2003):
    a. Strip the extension off the file name if the user provides one
    b. Set Cancel = True (prevents asking twice for a file name)
    c. Disable events during save (may not be needed, but doesn't hurt)

    Reference post #2 in the following link
    http://www.vbaexpress.com/forum/show...ves-file-twice

    Lewis

    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    08-24-2010
    Location
    California
    MS-Off Ver
    Excel 2007, 2010
    Posts
    45

    Re: How to Make Forced Save As XLSM Work?

    Hi Lewis,

    Sorry for the late reply and thanks for experimenting with the code. I tried your latest suggestion on Excel 2010, and as with all my previous tests, it will not work. Yours will prompt me to enter a file name, then the input box will disappears and nothing happens - no save, save as, or anything. I have given up this attempt and resort to a pre/post save warning for users to not save as XLSX. I really appreciate your time!

    M3atball

  10. #10
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: How to Make Forced Save As XLSM Work?

    m3,

    Don't worry about the late reply. Not a problem. I'm sorry it didn't work for you.

    I tested the above routine in Excel 2003 just now (the only Excel I have) by cutting and pasting the code above into a new workbook and it worked perfectly every time, no matter what I did to try and make it fail.

    The mysteries of different Excel Versions.

    Lewis

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 10-04-2013, 10:12 AM
  2. Replies: 4
    Last Post: 03-03-2011, 05:27 AM
  3. Forced Tab event on close or save UserForm
    By Gabor in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-17-2007, 05:00 PM
  4. Replies: 1
    Last Post: 01-08-2006, 11:20 AM
  5. Replies: 1
    Last Post: 09-22-2005, 08:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1