Using GetSaveAs

  • Thread starter Thread starter Ron McCormick
  • Start date Start date
R

Ron McCormick

I want users to be able to save a file by prompting them
with a suggested file name but allowing them to overwrite
it with and alternative, but I would also like the usual
Excel prompt to appear The filename Newname already exists
etc, so that they can change their option. I was trying to
use the undernoted code but this does not bring up the
usual alerts. How do I get the alerts to appear?

Application.GetSaveAsFilename Initialfilename:=Newname

TIA
Ron
 
I just ask myself:

Option Explicit
Sub testme99()

Dim resp As Long
Dim sFilename As Variant
Dim newName As String

newName = "C:\my documents\excel\book1.xls"

sFilename = Application.GetSaveAsFilename( _
InitialFileName:=newName)
If sFilename = False Then Exit Sub

If Dir(sFilename) <> "" Then
resp = MsgBox(prompt:="It exists, Overwrite?", Buttons:=vbYesNo)
If resp = vbNo Then
Exit Sub
End If
End If

application.displayalerts = false
'file|saveas stuff here
application.displayalerts = true

End Sub

But if you want the standard excel stuff, you may just want to display that
dialog:

Sub testme09()
Dim newName As String
newName = "C:\my documents\excel\book1.xls"
Application.Dialogs(xlDialogSaveAs).Show arg1:=newName
End Sub

I like the first better. It seems more straight forward to catch if they hit
the cancel key. (But you could check later, too.)
 
Back
Top