Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
saveas ActiveWorkbook.SaveAs Filename:=Range("A1").Value | Excel Programming | |||
ActiveWorkbook.SaveAs | Excel Programming | |||
ActiveWorkbook.SaveAs question(s) | Excel Programming | |||
ActiveWorkbook.SaveAs | Excel Programming | |||
activeworkbook.saveas | Excel Programming |