Application.GetSaveAsFilename(InitialFileName:=Range("O26") sometimes turns up a blank dialogue box

I have a sub to save my doc as a binary workbook. (Got from Stack Overflow.) I tried taking the value from a cell to use as the file name. Usually it works fine, I cannot figure out why sometimes not.

I have a sub to save my doc as a binary workbook. (Got from Stack Overflow.)

I tried taking the value from a cell to use as the file name.

Usually it works fine, I cannot figure out why sometimes not.

My data in cell O26 is always a text string.

Dim fname As Variant Dim FileFormatValue As Long fname = Application.GetSaveAsFilename(InitialFileName:=Range("O26"), filefilter:= _ " Excel Macro Free Workbook (*.xlsx), *.xlsx," & _ " Excel Macro Enabled Workbook (*.xlsm), *.xlsm," & _ " Excel 2000-2003 Workbook (*.xls), *.xls," & _ " Excel Binary Workbook (*.xlsb), *.xlsb", _ FilterIndex:=4, Title:="Save as xlsb") 'Find the correct FileFormat that match the choice in the "Save as type" list Select Case LCase(Right(fname, Len(fname) - InStrRev(fname, ".", , 1))) Case "xls": FileFormatValue = 56 Case "xlsx": FileFormatValue = 51 Case "xlsm": FileFormatValue = 52 Case "xlsb": FileFormatValue = 50 Case Else: FileFormatValue = 0 End Select If fname = isblank Then MsgBox "Project Not Saved!" Exit Sub End If ActiveWorkbook.SaveAs fname, FileFormat:= _ FileFormatValue, CreateBackup:=False
17

2 Answers

GetSaveAsFilename

The Three Sentences

  • Displays the standard Save As dialog box and gets a file name from the user without actually saving any files.

  • (This method returns the selected file name or the name entered by the user. The returned name may include a path specification). Returns False if the user cancels the dialog box.

  • When InitialFilename is used with an extension and a filter is applied, this extension must match the filter extension, otherwise the effective InitialFilename displayed in the dialog box will be an empty string.

The issues

  • No need to use case sensitivity on a dot (.):
    Not InStrRev(fName, ".", , 1), but InStrRev(fName, ".").

  • There is no isblank in VBA (it's all lower case anyway). ISBLANK is an Excel Function. The 2nd sentence addresses this issue (False).

  • The 3rd sentence is basically saying that you have to use either a filename without an extension or with the extension specified by FilterIndex which is xlsb in your case. If you need to have a file with another extension, then you can use a combination of Left and the newly created getExtension to get the filename without an extension.

The Code

Option Explicit ' Gets the extension (the string behind the last dot) of a filename. Function getExtension(ByVal fName As String) As String getExtension = LCase(Right(fName, Len(fName) - InStrRev(fName, "."))) End Function ' DisplayAlerts Version Sub getSaveFileDA() Dim fName As Variant Dim FileFormatValue As Long With Application fName = .GetSaveAsFilename( _ InitialFileName:=Range("O26").Value, FileFilter:= _ " Excel Macro Free Workbook (*.xlsx), *.xlsx," & _ " Excel Macro Enabled Workbook (*.xlsm), *.xlsm," & _ " Excel 2000-2003 Workbook (*.xls), *.xls," & _ " Excel Binary Workbook (*.xlsb), *.xlsb", _ FilterIndex:=4, Title:="Save as .xlsb") End With ' Find the correct FileFormat that matches the choice ' in the "Save as type" list. Select Case getExtension(fName) Case "xls": FileFormatValue = 56 Case "xlsx": FileFormatValue = 51 Case "xlsm": FileFormatValue = 52 Case "xlsb": FileFormatValue = 50 Case Else: FileFormatValue = 0 End Select If fName = False Then ' When user selects Cancel. MsgBox "Project Not Saved!" Exit Sub End If ' If fName exists then Excel will complain about it and when you ' press No or Cancel, an error will occur. To prevent this you can ' use Application.DisplayAlerts but keep in mind that then the file ' will be overwritten without the confirmation dialog popping up. Application.DisplayAlerts = False ActiveWorkbook.SaveAs fName, FileFormat:= _ FileFormatValue, CreateBackup:=False Application.DisplayAlerts = True MsgBox "Project successfully saved.", vbInformation End Sub ' On Error Resume Next Version Sub getSaveFileOE() Dim fName As Variant Dim FileFormatValue As Long With Application fName = .GetSaveAsFilename( _ InitialFileName:=Range("O26").Value, FileFilter:= _ " Excel Macro Free Workbook (*.xlsx), *.xlsx," & _ " Excel Macro Enabled Workbook (*.xlsm), *.xlsm," & _ " Excel 2000-2003 Workbook (*.xls), *.xls," & _ " Excel Binary Workbook (*.xlsb), *.xlsb", _ FilterIndex:=4, Title:="Save as .xlsb") End With ' Find the correct FileFormat that matches the choice ' in the "Save as type" list. Select Case getExtension(fName) Case "xls": FileFormatValue = 56 Case "xlsx": FileFormatValue = 51 Case "xlsm": FileFormatValue = 52 Case "xlsb": FileFormatValue = 50 Case Else: FileFormatValue = 0 End Select If fName = False Then GoTo NotSaved ' When user selects Cancel. On Error Resume Next ActiveWorkbook.SaveAs fName, FileFormat:= _ FileFormatValue, CreateBackup:=False If Err.Number <> 0 Then On Error GoTo 0 GoTo NotSaved Else On Error GoTo 0 MsgBox "Project successfully saved.", vbInformation End If Exit Sub NotSaved: MsgBox "Project Not Saved!", vbExclamation End Sub
3

Beyond simple! I just needed to add .xlsb to my text string in Cell O26! Now my extension matches the filtered extension. (The initial value in Cell O26 is a formula, so adding it is not a big deal, I just included it in the formula.)

ncG1vNJzZmirpJawrLvVnqmfpJ%2Bse6S7zGiorp2jqbawutJobWtoZG1%2BdX%2BOmqeppJmYrrW1zqdkoJ2kqK63scCsnaKklaOurrHIp6CtoZGhs6q4xKeYpp1dp66vs8SoaW9lo6S6psDIppysZaSqv6%2B%2FjK6nZpk%3D

 Share!