Excel VBA偶发runtime 1004错误求助：从Access取数后复制工作表存盘异常

Hey Kevin, sorry to hear you're hitting that pesky Runtime 1004 error intermittently—those random, inconsistent bugs are the worst! Let’s break down the most likely culprits and actionable fixes for your Excel 2013 VBA workflow on Windows 7 64-bit:

1. Unreleased Object References (Memory Leaks) #

Intermittent errors often stem from leftover object locks when you don’t properly clean up Access/Excel objects. Over time, this can cause resource conflicts that trigger 1004.

• Fix: Explicitly close and release every object you use, even if an error occurs. Add cleanup code at the end of your procedure (or in an error handler):

  ' Clean up Access objects
  If Not rs Is Nothing Then
      rs.Close
      Set rs = Nothing
  End If
  If Not db Is Nothing Then
      db.Close
      Set db = Nothing
  End If
  If Not accApp Is Nothing Then
      accApp.Quit
      Set accApp = Nothing
  End If
  
  ' Clean up Excel objects
  Set templateSheet = Nothing
  Set newSheet = Nothing
  

• Pro Tip: Wrap cleanup in an On Error Resume Next block (sparingly) to avoid errors if an object was already closed.

2. File Path or Permission Glitches #

Saving to a secondary path can fail randomly if the target is temporarily locked (e.g., antivirus scan, background file sync) or you lack write permissions.

• Fixes:
  • Verify the target path exists before saving, and create it if needed:

    Dim saveDir As String
    saveDir = "C:\Your\Target\Directory\"
    If Dir(saveDir, vbDirectory) = "" Then MkDir saveDir
    

  • Avoid saving to system-protected folders (like C:\Program Files). Use user-specific paths instead, e.g., Environ("USERPROFILE") & "\Documents\SavedFiles\".
  • Explicitly define the file format when saving to avoid compatibility issues:

    ThisWorkbook.SaveAs saveDir & "OutputFile.xlsx", FileFormat:=xlOpenXMLWorkbook
    

3. Worksheet Copy Interference #

Excel’s background processes (auto-calculation, screen updates) can disrupt worksheet copying, especially if your template has complex formulas or formatting.

• Fixes:
  • Temporarily disable screen updates and auto-calculation during the copy/save steps:

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    ' Your worksheet copy and save code here
    
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    

  • Ensure your template worksheet isn’t protected. If it is, temporarily unprotect it before copying:

    templateSheet.Unprotect Password:="YourTemplatePassword" ' Omit password if none
    ' Copy logic here
    templateSheet.Protect Password:="YourTemplatePassword"
    

4. 64-Bit Compatibility Issues #

On Windows 7 64-bit, misconfigured references or unadjusted API declarations can cause intermittent failures.

• Fixes:
  • Check your VBA references: Go to Tools > References and confirm Microsoft Access 15.0 Object Library is checked (matches Excel 2013). Uncheck any missing or conflicting references.
  • If you use Windows API functions in your code, add 64-bit compatibility declarations:

    #If VBA7 Then
        Declare PtrSafe Function YourAPIFunction Lib "user32.dll" (...) As LongPtr
    #Else
        Declare Function YourAPIFunction Lib "user32.dll" (...) As Long
    #End If
    

5. Hidden Excel Instance Conflicts #

Sometimes VBA leaves hidden Excel.exe processes running in the background, which consume resources and cause random errors.

• Fix:
  • Avoid creating new Excel instances with CreateObject("Excel.Application") unless absolutely necessary. Use ThisWorkbook.Application to reference the active instance.
  • Open Task Manager, kill any orphaned EXCEL.EXE processes, then test your script again.

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