Question about ActiveWorkbook.SaveAs()
 

Here is a basic sample of my code:

For j = 1 to 3

ActiveWorkbook.SaveAs Filename:="myfile.xlsm", _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, _
CreateBackup:=False

Next ' j

If the SaveAs filename already exists, the user will be
prompted with a dialog box that says:
"A file named 'myfile.xlsm' already exists in this location.
Do you want to replace it?"

Is there a way to determine whether the user selected
"Yes" or "No" to replace the file?

Question about ActiveWorkbook.SaveAs()
 

Hi Robert,

Am Thu, 24 Dec 2015 01:44:18 -0700 schrieb Robert Crandal:

Is there a way to determine whether the user selected
"Yes" or "No" to replace the file?

you could determine to replace always with
Application.DisplayAlerts=False
e.g.:

Dim j As Integer
Const mypath = "C:\data\"

Application.DisplayAlerts = False
For j = 1 To 3
ActiveWorkbook.SaveAs Filename:=mypath & "myfile" & j & ".xlsm", _
FileFormat:=xlOpenXMLWorkbookMacroEnabled
Next ' j
Application.DisplayAlerts = True

You also could create an addition to the name if the direction exists:
Dim j As Integer
Dim extra As String
Const mypath = "C:\data\"

For j = 1 To 3
extra = IIf(Dir(mypath & "myfile" & j & ".xlsm") < "", "New", "")
ActiveWorkbook.SaveAs Filename:=mypath & "myfile" & j & extra &
".xlsm", _
FileFormat:=xlOpenXMLWorkbookMacroEnabled
Next ' j

If the saving is already done you could open the file and check the
creation data. if it is older as expected the user selected "no":

Dim j As Integer
Const mypath = "C:\data\"

Workbooks.Open mypath & "myfile1.xlsm"
MsgBox "Creation Date: " & ActiveWorkbook.BuiltinDocumentProperties(11)


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Question about ActiveWorkbook.SaveAs()
 

Alternatively, you can check if the workbook is saved and if not prompt
the use with your own alert...

Dim vRet

If FileExists("myfile.xlsm") Then
vRet = MsgBox("A file named 'myfile.xlsm' already exists in this
location. Do you want to replace it?", vbYesNo)

If vRet = vbYes Then
'do this
Else
'do that
End If



Public Function FileExists(Filename$) As Boolean
Dim nAttr As Long
' Grab this files attributes, and make sure it isn't a folder.
' This test includes cases where file doesn't exist at all.
nAttr = GetFileAttributes(Filename)
If (nAttr And vbDirectory) < vbDirectory Then
FileExists = True
ElseIf Err.LastDllError = ERROR_SHARING_VIOLATION Then
FileExists = True
End If
End Function 'FileExists()

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Question about ActiveWorkbook.SaveAs()
 

Oops, should read...

"Alternatively, you can check if the file exists and if so
prompt the use with your own alert...

Dim vRet

If FileExists("myfile.xlsm") Then
vRet = MsgBox("A file named 'myfile.xlsm' already exists in this
location. Do you want to replace it?", vbYesNo)

If vRet = vbYes Then
Kill "myfile.xlsm" '//delete it before saving
Else
FileCopy "myfile.xlsm", "myfile.xlsm.bak" '//create a backup
End If

...which escapes Excel's intervention entirely. Thus, no need to specify
a value for the 'CreateBackup' arg of the SaveAs method...

ActiveWorkbook.SaveAs Filename:="myfile.xlsm", _
FileFormat:=xlOpenXMLWorkbookMacroEnabled

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Question about ActiveWorkbook.SaveAs()
 

Claus and Gary, thanks for your solutions. Those are
both acceptable.

I wish ActiveWorkbook.SaveAs() returned a value to
give status information, but oh well.