Saveas问题覆盖现有文件(Excel VBA) [英] Saveas issue Overwrite existing file ( Excel VBA)

问题描述

我有以下正确的宏，除了SaveAs给我一个错误，如果我单击No或Cancel，如果我单击yes可以正常工作.

I have the following macro that is correct except for the SaveAs gives me an error if I click No or Cancel,if I click yes is working fine.

ActiveWorkbook.SaveAs Filename:=FileName, FileFormat:=xlWorkbook, ConflictResolution:=xlLocalSessionChanges

Application.DisplayAlert =True

但是当我进入SaveAs部分时，选择No保存时会出现以下错误. Excel消息:此位置已经存在一个名为"........."的文件.您要替换吗?我单击否"或cancel，并收到运行时错误1004.... 对象_Workbook的方法SaveAs失败.

But when I come to SaveAs part I get the following error when I select No to the save. Excel message: A file named " ......... " already exists in this location. Do you want to replace it? I click 'No' or cancel and get the run time error 1004 .... Method SaveAs of object _Workbook failed.

我不想使用Application.DisplayAlerts = False，因为我希望用户知道已经有一个文件名为同名文件.

I don't want to use the Application.DisplayAlerts = False, because I want the user to be aware that there is a file already named the same.

1. 为什么会出现此错误?为什么我不能选择否"
2. 我还需要显示什么其他选项以表明该文件已经存在 在那里，选择No或Cancel并没有得到运行时错误.

1. Why do I get this error? Why can't I select 'No'
2. What other option do I have to display that the file is already there and select No or Canceland not get the run-time error.?

推荐答案

尝试此方法.

我已注释了该代码，因此您在理解它时应该没有任何问题.如果您仍然这样做，则只需问:)

I have commented the code so you shouldn't have any problem understanding it. Still if you do then simply ask :)

Sub Sample()
    Dim fName As Variant

    '~~> Offer user to Save the file at a particular location
    fName = Application.GetSaveAsFilename

    '~~> Check if it is a valid entry
    If fName <> False Then
        '~~> Check before hand if the file exists
        If Not Dir(fName) <> "" Then
            '~~> If not then save it
            ActiveWorkbook.SaveAs Filename:=fName
        Else
            '~~> Trap the error and ignore it
            On Error Resume Next
            If Err.Number = 1004 Then
                On Error GoTo 0
            Else '<~~ If user presses Save
                ActiveWorkbook.SaveAs Filename:=fName, _
                FileFormat:=xlWorkbook, _
                ConflictResolution:=xlLocalSessionChanges
            End If
        End If
    End If
End Sub

这篇关于Saveas问题覆盖现有文件(Excel VBA)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！