如何在Workbook.SaveAs覆盖确认中处理“否"或“取消"? [英] How to handle 'No' or 'Cancel' on Workbook.SaveAs overwrite confirmation?

问题描述

我希望在VBA脚本开始修改内容之前提示用户保存工作簿.当另存为"对话框出现时，如果用户单击取消"，则会引发一个自定义错误并停止脚本.如果他们单击保存"并且文件名已经存在，我希望询问他们是否覆盖.

I'm want users to be prompted to save a workbook before the VBA script starts modifying content. When the SaveAs dialog box comes up, if the user clicks Cancel I raise a custom error and stop the script. If they click Save and the filename already exists I want them to be asked whether to overwrite.

这是我的代码:

Function SaveCurrentWorkbook(wkbSource As Workbook, strNewFileName As String) As Variant
    If Not bolDebug Then On Error GoTo errHandler
    Dim varSaveName As Variant

SaveAsDialog:
    varSaveName = Application.GetSaveAsFilename(InitialFileName:=strNewFileName, FileFilter:="Excel Files (*.xls), *.xls")
    If varSaveName <> False Then
        wkbSource.SaveAs Filename:=varSaveName, ConflictResolution:=True, Addtomru:=True
        Set SaveCurrentWorkbook = wkbSource
    Else
        SaveCurrentWorkbook = False
        Err.Raise 11111, , "Save Canceled"
    End If

exitProc:
    Exit Function

errHandler:
    Select Case Err.Number
        Case 1004 'Clicked "No" or "Cancel" - can't differentiate
            Resume SaveAsDialog
        Case esle
            MsgBox "File not saved. Stopping script.", vbOKOnly, Err.Description
            Resume exitProc
    End select

End Function

如果他们单击是"，它将覆盖它.如果他们单击否"，我希望出现另存为"对话框，以便他们可以选择新的文件名，但是却出现错误.如果他们单击取消"，我希望发生错误并使脚本停止.问题是我无法区分否"和取消"之间触发的错误.

If they click 'Yes', it overwrites it. If they click 'No', I want the SaveAs dialog box to come up so they can select a new filename, but instead I get an error. If they click 'Cancel', I want an error to occur and for the script to stop. The problem is I can't differentiate the errors triggered between 'No' and 'Cancel'.

任何建议如何处理? (请原谅对错误处理的任何不当使用-已有一段时间了.)

Any suggestions how to handle this? (Please excuse any poor use of error handling - it's been a while.)

P.S.此函数由另一个过程调用，因此，如果用户在另存为"对话框或解决冲突"对话框中单击取消"，我也希望该调用过程也停止.我认为可以通过检查SaveCurrentWorkbook返回什么(Workbook对象或False)来做到这一点.

P.S. This function is called by another procedure so if the user clicks 'Cancel' at either the SaveAs dialog box or the ResolveConflict dialog, I would like the calling procedure to stop as well. I figure I can do this by checking what SaveCurrentWorkbook returns (either a Workbook object or False).

推荐答案

您可以像这样简单地创建自己的覆盖?"问题:

You can simply create your own "overwrite?"-question like this:

Function SaveCurrentWorkbook(wkbSource As Workbook, strNewFileName As String) As Variant
    If Not bolDebug Then On Error GoTo errHandler
    Dim varSaveName As Variant

SaveAsDialog:

    varSaveName = Application.GetSaveAsFilename(InitialFileName:=strNewFileName, FileFilter:="Excel Files (*.xls), *.xls")
    If varSaveName <> False Then
      If Len(Dir(varSaveName)) Then 'checks if the file already exists
        Select Case MsgBox("A file named '" & varSaveName & "' already exists at this location. Do you want to replace it?", vbYesNoCancel + vbInformation)
        Case vbYes
          'want to overwrite
          Application.DisplayAlerts = False
          wkbSource.SaveAs varSaveName, ConflictResolution:=2, Addtomru:=True
          Application.DisplayAlerts = True
          Set SaveCurrentWorkbook = wkbSource
        Case vbNo
          GoTo SaveAsDialog
        Case vbCancel
          SaveCurrentWorkbook = False
          Err.Raise 11111, , "Save Canceled"
        End Select
      Else
        wkbSource.SaveAs varSaveName, ConflictResolution:=True, Addtomru:=True
        Set SaveCurrentWorkbook = wkbSource
      End If
    Else
      SaveCurrentWorkbook = False
      Err.Raise 11111, , "Save Canceled"
    End If

exitProc:
    Exit Function

errHandler:
    Select Case Err.Number
    Case 1004 'Clicked "No" or "Cancel" - can't differentiate
      Resume SaveAsDialog
    Case Else
      MsgBox "File not saved. Stopping script.", vbOKOnly, Err.Description
      Resume exitProc
    End Select

End Function

您已经注意到，否"和取消"之间没有区别(对于应用程序，因为它不会停止保存本身). Excel只是对自己说:我不能在这里保存"，并且在两种情况下都弹出相同的错误...因此，唯一的解决方案是创建自己的msgbox:(

As you have noticed, there is no difference between "No" and "Cancel" (for the application, because it will not stop the saving itself). Excel simply lies to itself saying: "I can't save here" and pops the same error for both cases... so the only real solution is to create your own msgbox :(

这篇关于如何在Workbook.SaveAs覆盖确认中处理“否"或“取消"?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！