Macro to open Save As window and automatically populate file name and type

Question

In order to help speed up the process of saving audit sheets there is a naming convention and that has been taken care of in a reference sheet within the workbook that has a cell Defined Name of savefile_name.

However the audit sheets are saved on a variety of computers in the field and are eventually migrated by each user into the archive once they return to the office. Therefore I would like my macro to open the Save As dialog, populate the name and select the .xlsm or xlOpenXMLWorkbookMacroEnabled (Excel Macro-Enabled Workbook) format without fail.

My current code, inspired by this post, will happily open the Save As dialog, and populate the name, but it will not select the .xlsm format; it always has the last format selected as when the the Save As dialog was last opened. How to I stop this from happening?

My VBA code:

Dim FileName As String
FileName = Range("savefile_name").Value

Dim fPth As Object
Set fPth = Application.FileDialog(msoFileDialogSaveAs)

With fPth
  .InitialFileName = FileName & ".xlsm"
  .Title = "Save your File"
  .InitialView = msoFileDialogViewList
  If .Show <> 0 Then
    ThisWorkbook.SaveAs FileName:=.SelectedItems(1), FileFormat:=xlOpenXMLWorkbookMacroEnabled
  End If
End With

score 4 · Accepted Answer · answered Jun 15 '18 at 20:59

Add .FilterIndex = 2 inside the with statement.

Also, depending on the version of Excel you're working with, you may not need to add the & ".xlsm" to the end of the .InitialFileName. If you want more detail on how to find other extensions/make it more variable in your code; you could check out the answer here.

Dim FileName As String
FileName = Range("savefile_name").Value

Dim fPth As Object
Set fPth = Application.FileDialog(msoFileDialogSaveAs)

With fPth
  .InitialFileName = FileName
  .Title = "Save your File"
  .FilterIndex = 2
  .InitialView = msoFileDialogViewList
  If .Show <> 0 Then
    ThisWorkbook.SaveAs FileName:=.SelectedItems(1), FileFormat:=xlOpenXMLWorkbookMacroEnabled
  End If
End With

Thank you that it was indeed what was missing. Thank you for the resource as well. — Fiztban, Jun 15 '18 at 21:05

score 2 · Answer 2 · answered Jun 15 '18 at 21:03

2

Try this way, I have the same problem to save in .xlsm and it's work on Excel 2013 :

Dim FileName As String
FileName = Range("savefile_name").Value

Dim fname As Variant
fname = Application.GetSaveAsFilename(fileFilter:="Excel Marcro-Enabled Workbook (*.xlsm),*.xlsm", _
InitialFileName:=FileName, _
Title:="Save your File")
If fname = False Then Exit Sub  'Exit if user hit Cancel
ThisWorkbook.SaveAs FileName:=fname, FileFormat:=52

answered Jun 15 '18 at 21:03

JC Guidicelli

1,296
7
16

2

This also works indeed however, even though in this case we do want it to be a `.xslm`, this method it actually disables all other save options of the `Save As` dialog. Even though its favourable it is limiting in case one day a version needs to be saved in another format. I will keep this code for that day. Thank you! – Fiztban Jun 15 '18 at 21:27

Macro to open Save As window and automatically populate file name and type

2 Answers2