VBA to force save excel document as macro-enabled & auto populating filename as reference in a cell

  • I have a macro enabled excel template, when the user opens a new document aspects of their data they entry create a filename in a designated cell. This code forces the excel document to be saved as a macro-enabled extension and no matter how they name it it will be named what is stated in the designated cell for the filename. 99% of the time it works great; however 1% of the time the file is saved without any extension and is unusable, it is not an excel document. Can someone review this code and offer any suggestions?


  • Welcome to the Forum. Please read the Forum Rules to understand how the Forum works and why I have added Code Tags to your post


    All VBA code posted in the forum must be wrapped in code tags, which you omitted, including single-line code snippets.Be sure to use them in future posts.


    How to use code tags

    Note: no apostrophe in the tags, just used for demonstration here.

    ['code]


    your code goes between these tags


    ['/code]


    Or, just highlight all of the code and press the <> in the post menu above button to add the code tags.


    Thanks.

  • If the workbook is not open on the sheet with the name in when the code is run, then it will not pick up the correct cell.


    You should include the sheet name


    Code
    ThisFile = WorkSheets("sheet name here").Range("W2").Value
  • Hi Roy,

    Thank you so much for helping me, I took your suggestion and added the worksheet name; however, the result is still saving as a no file extension. Is there something else you see that I am doing wrong?

  • I notice that sometimes when I am saving (save as) the file type shows macro-enabled however the filename is blank, this is when the saved file has no extension when saved. Other times the filename will show PR Template1 when saving, this is when the file saves correctly.


    The way this works is that they can save it with the given name which was downloaded when they open the template "PR Template1"; or even if they change the filename it will override when saving to be the filename within cell W2.


    Somehow the blank filename during saving is what is causing the problem.

  • Not sure what the purpose is of asking the user to specify a filename in the following code:

    Code
    xFileName = Application.GetSaveAsFilename(, "Excel Macro-Enabled Workbook (*.xlsm), *.xlsm", , "Save As xlsm file")

    It just seems to be a trigger to confirm saving the file.

    xFileName is not used in the code, the following code overrides whatever filename is chosen with the value of ThisFile:

    Code
    ActiveWorkbook.SaveAs Filename:=ThisFile, FileFormat:=xlOpenXMLWorkbookMacroEnabled

    The only thing I can see that GetSaveAsFilename will do is maybe change the destination folder of the saved file.

  • Thank you for your help on this issue. Just a little background details; this file is a template format which resides on our SharePoint site, when the user downloads the document it saves a .xlsx copy on their computer but when they save it should force the auto populated filename and the macro-enabled extension. Again thank you for your time!

  • Try this


    I have removed the password from the sheet so that I could check the file name was working correctly

  • Hi Roy, I entered the required data in order to obtain the auto populated filename and when saving it auto closed the file I was saving. When I went to see how it saved it was without an extension so I am not able to open the file in excel.

  • Possibly...

    If I've been helpful, let me know. If I haven't, let me know that too. 

  • Hi Danglor, I tried replacing my code with what you noted above however when I save the document it is not saving it as the auto-populated name "W2" (this is created as the user enters information in this form), it is saving it as PR Form.

  • Hi Roy, thank you again for staying with me on this issue. Cell W2 is created as the user enter information into the form (blue fields), and then it is to be saved where ever they choose. I think the file will need to be saved as a .xlsm since it will still contain macros, otherwise it may not open correctly, right?


    That is strange that it is working for you and not when I tried it :/

  • If the new workbook contains code then the before Save code will still work, which might cause problems. If the code is not needed then saving as xlsx will remove the code. The workbook will still open.


    I created six files using my code. Here's one of them.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!