Providing suggested filename

[Barb Reinhardt]

I want to provide a suggested filename when the user chooses to "SAVEAS". I
get a suggestion and then if I change it, I get another prompt to save as.

Here is my code:



If Not SaveAsUI Then Exit Sub

filesavename = Application.GetSaveAsFilename( _
InitialFileName:=SuggName, _
fileFilter:="Excel Files (*.xls), *.xls")


What am I missing?

Thanks,
Barb Reinhardt

 

[Doug Glancy]

Barb,
If I understand correctly what you wish, there are 3 things you need to do:

Set Cancel to True to cancel Excel's SaveAs dialog, which you are replacing
Do the SaveAs yourself
Validate that they did not clear the name box in the SaveAs Dialog or cancel
it, because if they did, the GetSaveAs will return "False"

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim filesavename As String

If SaveAsUI Then
Cancel = True
filesavename = Application.GetSaveAsFilename( _
InitialFileName:="test3", _
fileFilter:="Excel Files (*.xls), *.xls")
'If they blanked out the name or Canceled, GetSaveAsFilename will return
"False" and you don't want to save
If filesavename <> "False" Then
ThisWorkbook.SaveAs filesavename
End If
Else
Exit Sub
End If
End Sub

hth,

Doug

 

[Barb Reinhardt]

Thanks. That does it.

Barb,
If I understand correctly what you wish, there are 3 things you need to do:

Set Cancel to True to cancel Excel's SaveAs dialog, which you are replacing
Do the SaveAs yourself
Validate that they did not clear the name box in the SaveAs Dialog or cancel
it, because if they did, the GetSaveAs will return "False"

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim filesavename As String

If SaveAsUI Then
Cancel = True
filesavename = Application.GetSaveAsFilename( _
InitialFileName:="test3", _
fileFilter:="Excel Files (*.xls), *.xls")
'If they blanked out the name or Canceled, GetSaveAsFilename will return
"False" and you don't want to save
If filesavename <> "False" Then
ThisWorkbook.SaveAs filesavename
End If
Else
Exit Sub
End If
End Sub

hth,

Doug