Search code examples
excelvbafilenamessave-as

VBA: Save a file with a name by default in a specific folder

Please I want to make a macro in Excel 2010 than the user can save a file in a specific folder but the name of the file would be given by default, can not be changed by the user. This for avoid that the user click other file looking the specific folder to save the file and the name of it change. This is what I have:

ActiveWorkbook.Save
name = "File Name"
folder = Application.GetSaveAsFilename(name)
ActiveWorkbook.SaveAs Filename:= folder & "xlsm"
Solution

  • One option would be to enforce this rule after coming back from the SaveAs dialog. Since you are only interested in the folder, build your Filename like this:

    ActiveWorkbook.SaveAs Filename:=fso.GetParentFolderName(folder) & "\" & name & ".xlsm"

    This requires a reference to Microsoft Scripting Runtime and declaring fso like this:

    Dim fso As FileSystemObject
Set fso = New FileSystemObject