获取“您要覆盖文件”对话框显示使用VBA保存时 [英] Getting the "Do you want to overwrite the file" dialog box to show when saving with VBA

问题描述

以下代码保存了我的Excel表格的选定区域。但是，如果我尝试保存与已存在的文件名相同的文件，那么只会保存文件，而不显示您要覆盖文件对话框。

The following code saves a selected area of my excel sheet. If I try to save a file that has the same file name as one that already exists, however, it will just save the file without showing the "Do you want to overwrite the file" dialog box.

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

Is there a way to change this code so that it will ask if I would like to overwrite the preexisting file?

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

推荐答案

根据建议模拟行为的一种方法是检查所选的SaveAsFilename：

One way to simulate the behavior, as suggested, is to check the selected 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

这篇关于获取“您要覆盖文件”对话框显示使用VBA保存时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！