Save As Macro Enabled Workbook

[CC268]

Hey guys,

I would like to create a VBA code that simply opens the Save As dialog window (in order to type your desired file name) and saves it as a Macro Enabled Workbook.

Thanks

 

[JackDanIce]

See: https://www.mrexcel.com/forum/excel-questions/607455-save-workbook-macro-enabled-workbook-vba.html

 

[CC268]

See: https://www.mrexcel.com/forum/excel-questions/607455-save-workbook-macro-enabled-workbook-vba.html

Hmm...so that code works, but when I save the file the file dialog window opens again and I have to press cancel. Why would it be opening twice like that?

 

[JackDanIce]

Without seeing your code, wouldn't be able to comment... maybe you're calling the dialog box twice? Maybe it's inside a loop? Maybe you have other code that's causing the second window to pop open?

 

[CC268]

Without seeing your code, wouldn't be able to comment... maybe you're calling the dialog box twice? Maybe it's inside a loop? Maybe you have other code that's causing the second window to pop open?

Dim tDate As String
Dim FileSaveName As String
Dim fName As String


fName = ActiveWorkbook.FullName
tDate = VBA.Format(DateSerial(Year(Date), Month(Date), Day(Date)), "dd-mm-yyyy")
FileSaveName = Application.GetSaveAsFilename(InitialFileName:=fName, filefilter:="Excel Files(*.xlsm),*.xlsm", Title:="Please save the file")
With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
.Show
ActiveWorkbook.SaveAs Filename:=fName & tDate & ".xlsm", FileFormat:=52
End With

I want it to pop open and use the current file name plus add the current date (10-10-2017). I am close but not quite there. Also if I press save or cancel the same window pops open again regardless. Not sure why.

 

[JackDanIce]

Parts in blue creates pop-up windows, your code is calling a pop-up window twice:

fileSaveName = Application.GetSaveAsFilename(InitialFileName:=fname, filefilter:="Excel Files(*.xlsm),*.xlsm", Title:="Please save the file")
With Application.FileDialog(msoFileDialogFolderPicker)

I think you can reduce your code to:

Dim tDate As String
Dim FileSaveName As String
Dim fName As String


fName = ActiveWorkbook.FullName & "_" & Format(today, "DD-MM-YYYY")
FileSaveName = Application.GetSaveAsFilename(InitialFileName:=fName, filefilter:="Excel Files(*.xlsm),*.xlsm", Title:="Please save the file")
ActiveWorkbook.SaveAs FileName:=FileSaveName, FileFormat:=52

 

[CC268]

Parts in blue creates pop-up windows, your code is calling a pop-up window twice:

fileSaveName = Application.GetSaveAsFilename(InitialFileName:=fname, filefilter:="Excel Files(*.xlsm),*.xlsm", Title:="Please save the file")
With Application.FileDialog(msoFileDialogFolderPicker)

I think you can reduce your code to:

Dim tDate As String
Dim FileSaveName As String
Dim fName As String


fName = ActiveWorkbook.FullName & "_" & Format(today, "DD-MM-YYYY")
FileSaveName = Application.GetSaveAsFilename(InitialFileName:=fName, filefilter:="Excel Files(*.xlsm),*.xlsm", Title:="Please save the file")
ActiveWorkbook.SaveAs FileName:=FileSaveName, FileFormat:=52

Thanks for the help. That makes sense when I step into the code and run it line by line. Two things:

1. Is there any way to have it add the current date (10-10-2017) to the end of the file name when you name it?
2. If I press the cancel button it still saves the file, but saves it as "False.xlsm". How can I make it just not do anything if I press the cancel button?

Thanks

 

[JackDanIce]

Untested (again!), try:

Dim tDate As String
Dim FileSaveName As String
Dim fName As String

fName = ActiveWorkbook.FullName & "_" & Format(today, "DD-MM-YYYY")
FileSaveName = Application.GetSaveAsFilename(InitialFileName:=fName, filefilter:="Excel Files(*.xlsm),*.xlsm", Title:="Please save the file")
if FileSaveName <> FALSE Then ActiveWorkbook.SaveAs FileName:=fName, FileFormat:=52

 

[CC268]

Untested (again!), try:

Dim tDate As String
Dim FileSaveName As String
Dim fName As String

fName = ActiveWorkbook.FullName & "_" & Format(today, "DD-MM-YYYY")
FileSaveName = Application.GetSaveAsFilename(InitialFileName:=fName, filefilter:="Excel Files(*.xlsm),*.xlsm", Title:="Please save the file")
if FileSaveName <> FALSE Then ActiveWorkbook.SaveAs FileName:=fName, FileFormat:=52

Bummer...that didn't work:

1. No file name shows up in Save As window - it just blank - have to type in the file name yourself or select one from current folder
2. It didn't like

if FileSaveName <> FALSE Then

as it gave me a "Run-time error 13: Type mismatch"

 

[JackDanIce]

Why do you need the pop-up window? You could save it directly since you're using the Activeworkbook's full name:

Dim fName As String

fName = ActiveWorkbook.FullName & "_" & Format(today, "DD-MM-YYYY") & ".xlsm"

' FileSaveName = Application.GetSaveAsFilename(InitialFileName:=fName, filefilter:="Excel Files(*.xlsm),*.xlsm", Title:="Please save the file")
' If FileSaveName <> False Then ActiveWorkbook.SaveAs FileName:=fName, FileFormat:=52

ActiveWorkbook.SaveAs FileName:=fName, FileFormat:=52