vba - 使用 VBA 保存时显示 "Do you want to overwrite the file"对话框

Question

以下代码保存 Excel 工作表的选定区域。但是，如果我尝试保存与已存在的文件具有相同文件名的文件，它只会保存该文件，而不会显示“是否要覆盖该文件”对话框。

有没有办法更改此代码，以便它会询问我是否要覆盖预先存在的文件？

Option Explicit
Sub CreatePDF()
Dim wSheet As Worksheet
Dim vFile As Variant
Dim sFile As String

Set wSheet = ActiveSheet
sFile = Replace(Replace(wSheet.Name, " ", ""), ".", "_") _
        & "_" _
        & Format(Now(), "yyyymmdd\_hhmm") _
        & ".pdf"
sFile = ThisWorkbook.Path & "\" & sFile

vFile = Application.GetSaveAsFilename _
(InitialFileName:=sFile, _
    FileFilter:="PDF Files (*.pdf), *.pdf", _
    Title:="Select Folder and FileName to save")

If vFile <> "False" Then
wSheet.Range("B2:J44").ExportAsFixedFormat _
    Type:=xlTypePDF, _
    FileName:=vFile, _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=False

MsgBox "PDF file has been created."
End If
End Sub

Answer 1

按照建议，模拟行为的一种方法是检查所选的 SaveAsFilename:

Option Explicit

Sub CreatePDF()
    Dim wSheet As Worksheet
    Dim vFile As Variant
    Dim sFile As String

    Set wSheet = ActiveSheet
    sFile = Replace(Replace(wSheet.Name, " ", ""), ".", "_") _
            & "_" _
            & Format(Now(), "yyyymmdd\_hhmm") _
            & ".pdf"
    sFile = ThisWorkbook.Path & "\" & sFile

    vFile = Application.GetSaveAsFilename _
    (InitialFileName:=sFile, _
        FileFilter:="PDF Files (*.pdf), *.pdf", _
        Title:="Select Folder and FileName to save")

    If Dir(vFile) > vbNullString Then _
        If MsgBox("Overwrite File?", _
                   vbExclamation + vbYesNo, "Overwrite?") = vbNo Then Exit Sub

    If vFile <> "False" Then
        wSheet.Range("B2:J44").ExportAsFixedFormat _
            Type:=xlTypePDF, _
            Filename:=vFile, _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, _
            OpenAfterPublish:=False

        MsgBox "PDF file has been created."
    End If
End Sub