VBA遍历目录文件时触发Runtime Error 1004，求调试建议

我正在编写VBA代码以遍历目录中的文件并提取信息，使用的模板代码对绝大多数文件有效，但部分文件会触发Runtime Error 1004。我已调整出错文件的宏设置，问题仍存在，附上相关代码，恳请调试建议：

For Each oFile In oFolder.Files
        
            If LCase(Right(oFile.Name, 4)) = "xlsx" Then
        
            length = UBound(Inspections)
    
            ReDim Preserve Inspections(length + 1)
            ReDim Preserve fridgeDoc(length + 1)
            ReDim Preserve fridgeWalk(length + 1)
            ReDim Preserve nrgEff(length + 1)
            ReDim Preserve mheDoc(length + 1)
            ReDim Preserve mheWalk(length + 1)
            ReDim Preserve buildingDoc(length + 1)
            ReDim Preserve buildingWalk(length + 1)
            
            Workbooks.Open oFile
        
            Inspections(length) = Workbooks(oFile.Name).Worksheets(1).Cells(11, 7).Value
            fridgeDoc(length) = Workbooks(oFile.Name).Worksheets(1).Cells(12, 7).Value
            fridgeWalk(length) = Workbooks(oFile.Name).Worksheets(1).Cells(13, 7).Value
            nrgEff(length) = Workbooks(oFile.Name).Worksheets(1).Cells(14, 7).Value
            mheDoc(length) = Workbooks(oFile.Name).Worksheets(1).Cells(15, 7).Value
            mheWalk(length) = Workbooks(oFile.Name).Worksheets(1).Cells(16, 7).Value
            buildingDoc(length) = Workbooks(oFile.Name).Worksheets(1).Cells(17, 7).Value
            buildingWalk(length) = Workbooks(oFile.Name).Worksheets(1).Cells(18, 7).Value
            
            Workbooks(oFile.Name).Close
            
            End If
Next oFile 

调试建议 #

• 定位出错文件：在Workbooks.Open前后加入错误捕获逻辑，记录触发错误的文件名，方便针对性排查：

  On Error Resume Next
  Set wb = Workbooks.Open(oFile)
  If Err.Number <> 0 Then
      Debug.Print "打开文件失败：" & oFile.Path
      Err.Clear
      On Error GoTo 0
      Continue For
  End If
  On Error GoTo 0
  

• 验证文件完整性：手动打开出错文件，检查是否存在损坏、格式异常或密码保护情况，这类问题会导致代码无法正常读取文件。
• 优化工作簿引用：打开文件后将其赋值给变量，避免重复通过文件名引用（可能因同名文件或路径问题出错）：

  Set wb = Workbooks.Open(oFile)
  '后续操作直接用wb代替Workbooks(oFile.Name)
  Inspections(length) = wb.Worksheets(1).Cells(11, 7).Value
  

• 检查工作表有效性：确认出错文件中存在至少1个工作表，且第一个工作表未被隐藏或保护：

  If wb.Worksheets.Count = 0 Then
      Debug.Print "文件无工作表：" & oFile.Path
      wb.Close SaveChanges:=False
      Continue For
  End If
  

• 处理单元格访问问题：部分文件可能存在单元格保护、行数不足18行或单元格数据异常，可针对单个单元格操作加错误捕获：

  On Error Resume Next
  Inspections(length) = wb.Worksheets(1).Cells(11, 7).Value
  If Err.Number <> 0 Then
      Debug.Print "读取单元格G11失败：" & oFile.Path
      Err.Clear
  End If
  On Error GoTo 0
  

• 优化数组操作：初始数组未定义时，第一次UBound(Inspections)会触发错误，建议先初始化数组，或改用集合存储数据，避免频繁ReDim Preserve影响效率。
• 关闭文件时明确不保存：添加SaveChanges:=False参数，避免文件因自动计算等原因弹出保存提示中断代码：

  wb.Close SaveChanges:=False
  

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