Excel "Save as" dialog - when file already exists
 

Hi, I am trying to do something very simple: code up a "Save as" dialog with
a default filename. The code below nearly works.

With Application.FileDialog(msoFileDialogSaveAs)
.InitialFileName = "C:\Temp\Test.xls"
If .Show = -1 Then
.Execute
End If
End With

The problem is that if the file already exists, the user is asked a "do you
wish to overwrite?" question *twice* - the first time by the dialog and the
second by VBA on the "Execute". If the user responds "yes" followed by "no",
they get a runtime error "Method 'Execute' of 'FileDialog' failed."
What am I doing wrong?
I am using Excel 2002 Sp-2

Thanks in advance
MattM

Excel "Save as" dialog - when file already exists
 

Matt,
I don't have a version of Excel that supports this method, but :
Dim Retval As Variant
With Application
Retval = .GetSaveAsFilename()
If Retval < False Then
On Error Resume Next
'.EnableEvents = False
ThisWorkbook.SaveAs Retval
'.EnableEvents = True
On Error GoTo 0
End If
End With
'Cancel = True

Uncomment the lines above if this is in a _BeforeSave event, to prevents the
second firing.

NickHK

"MattM" wrote in message
...
Hi, I am trying to do something very simple: code up a "Save as" dialog
with
a default filename. The code below nearly works.

With Application.FileDialog(msoFileDialogSaveAs)
.InitialFileName = "C:\Temp\Test.xls"
If .Show = -1 Then
.Execute
End If
End With

The problem is that if the file already exists, the user is asked a "do
you
wish to overwrite?" question *twice* - the first time by the dialog and
the
second by VBA on the "Execute". If the user responds "yes" followed by
"no",
they get a runtime error "Method 'Execute' of 'FileDialog' failed."
What am I doing wrong?
I am using Excel 2002 Sp-2

Thanks in advance
MattM

Excel "Save as" dialog - when file already exists
 

Thanks for trying Nick. I used your idea to create the following:

Dim sFilename As String

With Application.FileDialog(msoFileDialogSaveAs)
.InitialFileName = "C:\Temp\Test.xls"
If .Show = -1 Then
sFilename = .SelectedItems(1)
End If
End With

If sFilename < "" Then
Application.EnableEvents = False
ActiveWorkbook.SaveAs Filename:=sFilename
Application.EnableEvents = True
End If

Unfortunately, this didn't work - I still got the "do you wish to replace"
message on the SaveAs.

The best I can come up with is this. Yuk!

Dim sFilename As String, fso

With Application.FileDialog(msoFileDialogSaveAs)
.InitialFileName = "C:\Temp\Test.xls"
If .Show = -1 Then
sFilename = .SelectedItems(1)
End If
End With

If sFilename < "" Then
Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FileExists(sFilename) Then
fso.DeleteFile (sFilename)
End If

ActiveWorkbook.SaveAs Filename:=sFilename
End If

Cheers
MattM

Excel "Save as" dialog - when file already exists
 

Nick - my apologies, your answer was spot on. The trick is to use your
GetSaveAsFilename, in preference to my FileDialog(msoFileDialogSaveAs). This
doesn't show a "do you wish to replace" query, hence the message on SaveAs is
the only one! All I needed was

Dim Retval As Variant

With Application
Retval = .GetSaveAsFilename("C:\Temp\Test.xls")
If Retval < False Then
ThisWorkbook.SaveAs Retval
End If
End With

Simpler code too. Many thanks!
MattM