运行时错误 - 无法访问文件 [英] Run Time Error - Cannot access file

问题描述

我有以下VBA脚本可以保存工作簿，这样可以正常工作。

但是，如果再次单击了commandbutton1，它会正确打开选项框

'要替换文件吗？ - 是，否，取消。

yes选项工作正常，但No和Cancel选项会出现错误框 - RunTime error 1004：无法访问文件。任何人都可以指出正确的方向来解决问题。代码如下：

  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文件名：= Path& FileName1&  - & FileName2& .xlsm，FileFormat：= 52 
 End Sub 
  

解决方案

如果您仍然希望为用户提示，可以使用MsgBox进行此操作。以下是一个简单的Yes / No提示。

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

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

  Private Sub CommandButton1_Click（）
 Dim fso As FileSystemObject 
 Dim Path As String 
 Dim FileName1 As String 
 Dim FileName2 As String 
 Dim FilePath As String 
 
设置fso = CreateObject（Scripting.FileSystemObject）
 
 Path =C：\temp\Saved Invoices\
 FileName1 = Range（R12）
 FileName2 = Range（S12）
 FilePath = Path& FileName1&  - & FileName2& .xlsm
 
如果fso.FileExists（FilePath）然后
如果MsgBox（您的文件已经存在于此位置，是否要替换它？，vbYesNo）= vbYes然后
 ActiveWorkbook.SaveAs FilePath，52 
 End If 
 Else 
 ActiveWorkbook.SaveAs FilePath，52 
 End If 
 
 End Sub $ b $由Thinkingcap提供的应用程序.DisplayAlerts也是一个很好的方法。首先收到错误的原因是因为您的脚本知道当他们点击YES但是没有NO或CANCEL的方向时该怎么办。当启用提示时，否是SaveAs的默认选项。在Application.DisplayAlerts = False中包装您的脚本不仅禁用提示，而且将默认值更改为YES，因此每次不用用户输入即可替换该文件。
 
I have the following VBA script which saves a workbook and this works fine. 

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

  
'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'. 

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

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

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.

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.
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.

                        
这篇关于运行时错误 - 无法访问文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！