如何为指定VBA代码添加错误处理，解决Runtime error '70'权限拒绝问题

Hey there! Let's sort out that frustrating Runtime Error 70 you're hitting with the FileCopy command. This error usually pops up for two main reasons: either the target file already exists (and FileCopy won't overwrite it automatically) or your macro lacks permission to write to the destination folder.

Below is your updated code with targeted error handling, plus safeguards to handle the "file already exists" scenario proactively:

Sub CopyDocumentWithErrorHandling()
    Dim str1 As String
    Dim PlanDocTemplate As String
    Dim answer1 As String
    Dim overwriteAnswer As VbMsgBoxResult
    
    ' First, confirm workbook is saved in correct location
    answer1 = MsgBox("Is this Workbook saved in the appropriate folder?", vbYesNo + vbQuestion)
    If answer1 = vbNo Then
        MsgBox "Please save this Workbook in the appropriate folder then run again.", vbExclamation
        Exit Sub
    End If
    
    ' Define source and target paths (fill in the missing part of your PlanDocTemplate assignment!)
    str1 = "C:\user\desktop\document.docx"
    PlanDocTemplate = Application.ActiveWorkbook.Path & "\" & Range("A1").Value & ".docx" ' Added file extension as example
    
    ' Proactively check if target file exists
    If Dir(PlanDocTemplate) <> "" Then
        overwriteAnswer = MsgBox("Target file already exists! Do you want to overwrite it?", vbYesNo + vbExclamation)
        If overwriteAnswer = vbNo Then
            MsgBox "Copy operation cancelled.", vbInformation
            Exit Sub
        Else
            ' Delete existing file to allow overwrite (requires permission!)
            On Error Resume Next ' Handle case where file is locked
            Kill PlanDocTemplate
            On Error GoTo 0
            ' Check if file was successfully deleted
            If Dir(PlanDocTemplate) <> "" Then
                MsgBox "Cannot overwrite file - it may be open or locked by another program.", vbCritical
                Exit Sub
            End If
        End If
    End If
    
    ' Attempt file copy with error handling
    On Error GoTo ErrorHandler
    FileCopy str1, PlanDocTemplate
    MsgBox "File copied successfully!", vbInformation
    
Cleanup:
    ' Reset error handling
    On Error GoTo 0
    Exit Sub
    
ErrorHandler:
    Select Case Err.Number
        Case 70 ' Permission Denied
            MsgBox "Error: Permission denied. Ensure you have write access to the target folder and the file isn't open.", vbCritical
        Case 53 ' File Not Found (source file missing)
            MsgBox "Error: Source file not found at " & str1, vbCritical
        Case Else
            MsgBox "Unexpected error occurred: #" & Err.Number & " - " & Err.Description, vbCritical
    End Select
    Resume Cleanup
End Sub

Key Improvements Explained: #

• Proactive File Existence Check: Before running FileCopy, we use Dir() to check if the target file exists. If it does, we ask the user if they want to overwrite it, avoiding the error entirely if they choose not to.
• Overwrite Handling: If the user agrees to overwrite, we use Kill to delete the existing file first. We add a quick error check here in case the file is locked (e.g., open in Word).
• Structured Error Handling: The On Error GoTo ErrorHandler block catches unexpected issues, and the Select Case handles specific error codes (like 70 for permission problems) with clear, user-friendly messages.
• Cleanup Block: Ensures error handling is reset even if an error occurs, preventing weird behavior in subsequent code runs.

Just remember to fill in the missing part of your PlanDocTemplate assignment (I added a .docx extension as an example—adjust it to match your actual file type)!

内容的提问来源于stack exchange，提问作者Rick