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

**royUK** · Jan 7th 2021

The last two characters of the name are .5, that seems to be causing your problem. Excel is interpreting the .5 as a file extension so is not adding .xlsx.

Change your formula to =CONCATENATE(F33," ",S25," ",F18,F20,M22," ","PR"," ",P7&".xlsx") and the code works.

If you want to save then macros then use

=CONCATENATE(F33," ",S25," ",F18,F20,M22," ","PR"," ",P7&".xlsm")

Also change the 51 in this line of code to 52

Code

ActiveWorkbook.SaveAs Filename:=sFldr & Application.PathSeparator & ThisFile, _
FileFormat:=51, CreateBackup:=False

dleister · Jan 7th 2021

Hi Roy,

I made the changes you noted above; however I am getting the VB compile error. I attached the error message.

Deborah

**royUK** · Jan 8th 2021

I thought that I had replaced the function. Add this into a Standard Module

Code

''///choose a folder starting from the folder passed in argument path
Function GetFolder(strPath As String) As String
Dim oFD As FileDialog
Dim sItem As String
Set oFD = Application.FileDialog(msoFileDialogFolderPicker)
With oFD
.Title = "Select a Folder"
.AllowMultiSelect = False
.InitialFileName = strPath
If .Show <> -1 Then GoTo exit_func
sItem = .SelectedItems(1)
End With
exit_func:
GetFolder = sItem
Set oFD = Nothing
End Function

Display More

dleister · Jan 8th 2021

Hi Roy,

I added the above code to a new module and entered data in the worksheet and when I tried to save I got this run-time error.

I am so sorry that this has taken so much of your time, but I really appreciate your help!

Deborah

**royUK** · Jan 8th 2021

Can you attach the file that is producing the errors. It works perfectly for me so I would think that it is a problem in the file name.

dleister · Jan 9th 2021

Hi Roy,

I entered data as the end-user would do and it did not give me the error this time, however it is not saving it as the auto-populated name.

**royUK** · Jan 10th 2021

It works for me.

I had to amend your code. You were trying to save the file as a macro enabled file with the wrong file extension. It should have generated an error message.

If you want to save as macro enabled then you need to use .xlsm with file format 52.

You need xlsx with 51 to save as a non macro workbook. I think that is best because if you need to save any changes to the generated workbook then the code will run in that workbook.

dleister · Jan 11th 2021

Hi Roy it works!!!

Thank you so much for all your help and patience with me I am going to make it into a Macro-Enabled Template document and place it into SharePoint, finger crossed that it work when the user downloads it. I will let you know..................thanks again!!

Deborah

**royUK** · Jan 11th 2021

Pleased to help

dleister · Jan 11th 2021

Hi Roy,

Sorry to bother you again, but I want to remove the data I entered into the form and change the code from 52 to 51 and leave it as a macro-enabled document in order to save it on my SharePoint site; however I normally could do these changes and then do ctrl S to save and then just end the task for excel in order to get out and all my changes were done. However I cannot to that action with the new code, any suggestions?

**royUK** · Jan 12th 2021

That is why I suggested removing the code. Why do you need to save as macro enabled?

dleister · Jan 12th 2021

You're right the spreadsheet does not have any macros so i don't need the splash screen. I just need to remove the existing data in the form and still have the code you wrote in it so that I can place it on the sharepoint site for the staff to use as a template, I don't want anyone to save over this file. How can I do this since it saves it without the code?

**royUK** · Jan 12th 2021

Open a copy without enabling macros

dleister · Jan 12th 2021

You are a genius!! Thanks again I was able to get it on our SharePoint site and I tested it and it worked like a charm, just as I was hoping. Okay hopefully you will not hear from me again on this form Thanks!

**royUK** · Jan 12th 2021

Pleased to help

Participate now!

Share