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

dleister · Dec 22nd 2020

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?

Code

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)


Dim xFileName As String


If SaveAsUI <> False Then
Cancel = True
ThisFile = Range("W2").Value
xFileName = Application.GetSaveAsFilename(, "Excel Macro-Enabled Workbook (*.xlsm), *.xlsm", , "Save As xlsm file")
If xFileName <> "False" Then
Application.EnableEvents = False
ActiveWorkbook.SaveAs Filename:=ThisFile, FileFormat:=xlOpenXMLWorkbookMacroEnabled


Application.EnableEvents = True
Else
MsgBox "Action Cancelled"
Cancel = True
Exit Sub
End If
End If
End Sub

Display More

**royUK** · Dec 22nd 2020

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.

**royUK** · Dec 22nd 2020

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

dleister · Dec 22nd 2020

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?

dleister · Dec 22nd 2020

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.

gijsmo · Dec 26th 2020

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.

**royUK** · Dec 27th 2020

Can you attach the file

dleister · Jan 4th 2021

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!

**royUK** · Jan 5th 2021

I think that you only want to give the user the option to pick the destination folder. Is this correct?

dleister · Jan 5th 2021

Yes, that is correct.

**royUK** · Jan 5th 2021

I'll have a look at the code.

**royUK** · Jan 5th 2021

Try this

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

dleister · Jan 5th 2021

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.

dangelor · Jan 6th 2021

Possibly...

Code

Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim f As Object, sPath As String, sFileName As String
    If SaveAsUI = False Then
        Cancel = True
        sFileName = Worksheets("PR Form").Range("W2").Value2 & ".xlsm"
        Set f = Application.FileDialog(msoFileDialogFolderPicker)
        With f
            .InitialFileName = "C:"
            If .Show = True Then
                sPath = .SelectedItems(1)
            End If
        End With
        If sPath <> vbNullString Then
            Application.EnableEvents = False
            ActiveWorkbook.SaveAs Filename:=sPath & Application.PathSeparator & sFileName, FileFormat:=52
            Application.EnableEvents = True
        Else
            MsgBox "Action cancelled.", vbInformation + vbOKOnly, "No Path Selected"
            Cancel = True
        End If
    End If
End Sub

Display More

**royUK** · Jan 6th 2021

It saved every time for me wit the .xlsx extension. What exactly is in W2 when saving?

dleister · Jan 6th 2021

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.

**royUK** · Jan 6th 2021

That code is also saving as macro enabled, I thought you wanted to save as no macros - .xlsx.

dleister · Jan 6th 2021

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

**royUK** · Jan 6th 2021

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.

dleister · Jan 7th 2021

Hi Roy, I entered data in the blue fields to show you how the auto-populated field (W2) looks. When the document is saved it should save the file with the filename shown on W2.

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

Participate now!

Share

Users Viewing This Thread