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

May 2024 ยท 4 minute read

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

The issues

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