vba 运行时错误 - 无法访问文件

Run Time Error - Cannot access file

提问by user3853292

I have the following VBA script which saves a workbook and this works fine.

我有以下 VBA 脚本可以保存工作簿，这很好用。

However if commandbutton1 is clicked again it correctly brings up the option box

但是，如果再次单击 commandbutton1，它会正确显示选项框

'Do you want to replace the file'- Yes, No, Cancel.

“是否要替换文件”- 是、否、取消。

The yes option works fine but the No and Cancel option bring up an error box - RunTime error 1004: Cannot access 'file'.

yes 选项工作正常，但 No 和 Cancel 选项会显示一个错误框 - RunTime error 1004: Cannot access 'file'。

Can anyone point me in the right direction to solve the problem please. Code is below:

任何人都可以指出我解决问题的正确方向。代码如下：

Private Sub CommandButton1_Click()

Dim Path As String
Dim FileName1 As String
Dim FileName2 As String
Path = "C:\temp\Saved Invoices\"
FileName1 = Range("R12")
FileName2 = Range("S12")
ActiveWorkbook.SaveAs Filename:=Path & FileName1 & "-" & FileName2 & ".xlsm", FileFormat:=52 
End Sub

采纳答案by David Rachwalik

If you would still like a prompt for your user, you can do so using a MsgBox. Below is a simple Yes/No prompt.

如果您仍然希望为您的用户提供提示，您可以使用 MsgBox 来实现。下面是一个简单的是/否提示。

This code uses the Microsoft Scripting Runtime. If you don't already have this referenced, go to the Visual Basic Editor (ALT-F11) and in the Tools dropdown, select "References..." Tick the check-box next to "Microsoft Scripting Runtime" and then click the OK button.

此代码使用 Microsoft 脚本运行时。如果您还没有引用它，请转到 Visual Basic 编辑器 (ALT-F11) 并在工具下拉列表中，选择“引用...” 勾选“Microsoft Scripting Runtime”旁边的复选框，然后单击确定按钮。

The code below sets up the FileSystemObject, sets the path and filename you provided, and checks if that file already exists. If it doesn't find the file, it will SaveAs like before. If it already finds the file, it will give the user a MsgBox asking whether they'd like to overwrite the file or not. If they click YES, it does a SaveAs like before. Clicking NO will do nothing.

下面的代码设置 FileSystemObject，设置您提供的路径和文件名，并检查该文件是否已存在。如果它没有找到该文件，它将像以前一样另存为。如果它已经找到该文件，它会给用户一个 MsgBox，询问他们是否要覆盖该文件。如果他们单击 YES，它会像以前一样执行另存为。单击 NO 将不执行任何操作。

Private Sub CommandButton1_Click()
    Dim fso As FileSystemObject
    Dim Path As String
    Dim FileName1 As String
    Dim FileName2 As String
    Dim FilePath As String

Set fso = CreateObject("Scripting.FileSystemObject")

Path = "C:\temp\Saved Invoices\"
FileName1 = Range("R12")
FileName2 = Range("S12")
FilePath = Path & FileName1 & "-" & FileName2 & ".xlsm"

If fso.FileExists(FilePath) Then
    If MsgBox("Your file already exists in this location.  Do you want to replace it?", vbYesNo) = vbYes Then
        ActiveWorkbook.SaveAs FilePath, 52
    End If
Else
    ActiveWorkbook.SaveAs FilePath, 52
End If

End Sub

Application.DisplayAlerts provided by Thinkingcap is also a great method. The reason you received the error in the first place is because your script knew what to do when they clicked YES but had no direction for NO or CANCEL. When prompts are enabled, NO is the selected default for SaveAs. Wrapping your script in Application.DisplayAlerts = False not only disables prompts but changes the default value to YES, so it replaces the file every time without user input.

Thinkingcap 提供的 Application.DisplayAlerts 也是一个很好的方法。您首先收到错误的原因是因为您的脚本在单击“是”时知道要做什么，但没有“否”或“取消”的指示。启用提示时，“否”是另存为的选定默认值。将您的脚本包装在 Application.DisplayAlerts = False 中不仅会禁用提示，还会将默认值更改为 YES，因此它每次都会在没有用户输入的情况下替换文件。

回答by evenprime

All you need to do is to wrap the code in Application.DisplayAlerts

您需要做的就是将代码包装在 Application.DisplayAlerts 中

Application.DisplayAlerts = False
 Dim Path As String
 Dim FileName1 As String
 Dim FileName2 As String
 Path = "C:\temp\Saved Invoices\"
 FileName1 = Range("R12")
 FileName2 = Range("S12")
 ActiveWorkbook.SaveAs Filename:=Path & FileName1 & "-" & FileName2 & ".xlsm", FileFormat:=52
Application.DisplayAlerts = True