Option to overwrite with VBA initiated open/Save Common Dialog

[Guest]

XP, MSO 2003
I have an Excel routine that executes a call to the comon dialog open/save
file as per http://www.mvps.org/access/api/index.html Call Windows File
Open/Save Dialog box.

The only issue i have is, when saving, there is no option to overwrite.
(i.e. "The file filename.xls already exists, do you want to overwrite it?"
type-thing)

Is it possible to do that within the call to the dialog box, or does that
have to be coded as a separate search for the file and, if it's found use a
message box to ask, then call the dialog box.

I was told using DisplayAlert before the call would work but it doesn't.

TIA

BAC

 

[Chip Pearson]

Is there any particular reason you're using the API version rather than
Excel's own GetSaveAsFilename?

I was told using DisplayAlert before the call would work but it doesn't.

DisplayAlerts, being part of the Application object, can only affect
operations carried out by Excel. The API are completely independent of Excel
and therefore Excel doesn't know what it is calling and the DLL has no idea
from where it is being called.

Simpler than the API is to use Excel's own GetSaveAsFilename. There is also
the related method GetOpenFilename. See either one in VBA Help for more
info.

Sub AAA()
Dim FName As Variant
FName = Application.GetSaveAsFilename( _
filefilter:="Excel Files (*.xls), *.xls")
If FName = False Then
' user cancelled
Exit Sub
End If
Application.DisplayAlerts = False
ThisWorkbook.SaveAs Filename:=FName
Application.DisplayAlerts = True
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[JW]

The way I read your post is that you do want to see the overwrite
message, correct? If so, simply remove the DisplayAlerts lines from
Chip's code.
Sub AAA()
Dim FName As Variant
FName = Application.GetSaveAsFilename( _
filefilter:="Excel Files (*.xls), *.xls")
If FName = False Then
' user cancelled
Exit Sub
End If
On Error Resume Next
ThisWorkbook.SaveAs Filename:=FName
End Sub

 

[Chip Pearson]

The way I read your post is that you do want to see the overwrite

message, correct? If so, simply remove the DisplayAlerts lines from

I think you're right. I misread the post and thought he wanted to suppress
the overwrite? message.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)