SaveCopyAs and Overwrite Alert

[Chris Watts]

I have written a vba Function, for Excel 2007 under XP SP2, that offers a
user the opportunity to save a safety copy of his/her file before
proceeding. Copy below
The function operates exactly as I would expect except that it does not
raise an Overwrite Alert where appropriate - it just proceed to overwrite
regardless. Can anbody see/suggest what I have missed or am doing wrong?

Suggestions welcome
TIA
Chris

======Copy of macro ========
Option Explicit

Sub TestSafetySave()
Dim bProceed
bProceed = SafetySave
MsgBox bProceed
End Sub


Function SafetySave() As Boolean

' Offers the user the option to save a security copy before proceding with
the facility.
' Returns True if the user wishes to proceed with the facility.
' Returns False if user wishes to abandon execution of facility.

Dim strPath As String
Dim strFileName As String
Dim Response As VbMsgBoxResult
Dim bProceed As Boolean


Response = MsgBox("Do you want to save a copy before proceeding?",
vbYesNoCancel + vbExclamation + vbDefaultButton3, "Safety Save")

Select Case Response

Case vbYes ' Save and flag to proceed
strPath = ActiveWorkbook.Path
ChDir strPath
strFileName = Application.GetSaveAsFilename("Safety Copy.xlsm", _
"Excel Workbook (*.xlsx), *.xlsx,Excel Macro-Enabled Workbook
(*.xlsm), *.xlsm,Excel Workbook 97-2003 (*.xls), *.xls" _
, 3 _
, "Safety Copy")
Application.DisplayAlerts = True
ActiveWorkbook.SaveCopyAs (strFileName)
bProceed = True

Case vbNo ' Do not save but flag to proceed
bProceed = True
Exit Function

Case Else ' Flag to abandon
bProceed = False

End Select
SafetySave = bProceed
End Function

========= End===========

 

[Dave Peterson]

That's the way .savecopyas works.

Maybe you could just check yourself...

dim TestStr as string
dim okToContinue as long

oktocontinue = vbok
teststr = ""
on error resume next
teststr = dir(strfilename)
on error goto 0

if teststr = "" then
'doesn't exist
else
oktocontinue = msgbox(prompt:="it already exists. Overwrite", _
buttons:=vbokcancel)
end if

if oktocontinue = vbok then
'do the savecopyas
else
'do something else
end if

(All untested, uncompiled. Watch for typos.)

 

[Chris Watts]

Thanks, Dave.
Glad to know that it is how it is, and not me - now I canl ook a work around
such as you suggest.

Many thanks
Chris

 

[GS]

You could just use...

ActiveWorkbook.SaveAs strFileName

which does raise the overwrite alert.

 

[Chris Watts]

True, Garry, but that results in the intial workbook being closed and the
active one becoming that defined by strFileName. To the basic users, for
whom I am catering, will see it as the workbook having changed name - which
is likely to confuse them I could do, it is true, what you say then do a
second SaveAs with the original name and turning alerts off for that one.

cheers, and thanks
Chris

 

[GS]

Chris Watts submitted this idea :

True, Garry, but that results in the intial workbook being closed and the
active one becoming that defined by strFileName. To the basic users, for
whom I am catering, will see it as the workbook having changed name - which
is likely to confuse them I could do, it is true, what you say then do a
second SaveAs with the original name and turning alerts off for that one.

cheers, and thanks
Chris

Hi Chris,
In that case Dave's suggestion is the way to go!