Mac下用VBA的Dir()函数同时引用不同目录工作簿时遇运行时错误

错误原因分析 #

1. Dir()函数的单状态限制：Dir()是单状态函数，第二次调用Dir(新路径)会覆盖第一次的遍历状态。你内层循环遍历完output目录后，外层再调用Dir()时，已经丢失了input目录的遍历上下文，所以触发运行时错误5。
2. Mac路径分隔符错误：Mac系统的路径分隔符是/，不是Windows的\，原代码用\会导致路径无效。
3. 路径引用错误：内层打开paste文件时，错误使用了fileDir_copy路径，应该指向fileDir_paste。

修正方案：用数组存储文件名再遍历 #

先把两个目录下的文件名分别存入数组，再通过遍历数组来处理文件，避开Dir()的状态冲突问题。

修正后的代码：

Sub CopySheetsBetweenFolders()
    Dim fileDir_copy As String, fileDir_paste As String
    Dim fileCriteria As String
    Dim copyFiles() As String, pasteFiles() As String
    Dim i As Integer, j As Integer
    Dim wb_copy As Workbook, wb_paste As Workbook
    
    ' Mac下用斜杠作为路径分隔符
    fileDir_copy = Application.ActiveWorkbook.Path & "/input/"
    fileDir_paste = Application.ActiveWorkbook.Path & "/output/"
    fileCriteria = "*.xlsx"
    
    ' 收集input目录下的所有xlsx文件到数组
    ReDim copyFiles(0)
    fileName = Dir(fileDir_copy & fileCriteria)
    Do While Len(fileName) > 0
        copyFiles(UBound(copyFiles)) = fileName
        ReDim Preserve copyFiles(UBound(copyFiles) + 1)
        fileName = Dir()
    Loop
    ' 移除最后一个空元素
    If UBound(copyFiles) > 0 Then ReDim Preserve copyFiles(UBound(copyFiles) - 1)
    
    ' 收集output目录下的所有xlsx文件到数组
    ReDim pasteFiles(0)
    fileName = Dir(fileDir_paste & fileCriteria)
    Do While Len(fileName) > 0
        pasteFiles(UBound(pasteFiles)) = fileName
        ReDim Preserve pasteFiles(UBound(pasteFiles) + 1)
        fileName = Dir()
    Loop
    If UBound(pasteFiles) > 0 Then ReDim Preserve pasteFiles(UBound(pasteFiles) - 1)
    
    ' 遍历两个数组处理文件
    For i = LBound(copyFiles) To UBound(copyFiles)
        Set wb_copy = Workbooks.Open(fileDir_copy & copyFiles(i))
        
        For j = LBound(pasteFiles) To UBound(pasteFiles)
            Set wb_paste = Workbooks.Open(fileDir_paste & pasteFiles(j))
            
            ' 在这里写你的复制粘贴逻辑
            ' 示例：复制wb_copy的第一个工作表到wb_paste的最后
            wb_copy.Sheets(1).Copy After:=wb_paste.Sheets(wb_paste.Sheets.Count)
            
            wb_paste.Close SaveChanges:=True
        Next j
        
        wb_copy.Close SaveChanges:=True
    Next i
End Sub

额外注意事项 #

• 如果input和output目录下的文件是一一对应（比如文件名相同），可以直接遍历其中一个数组，用相同文件名匹配另一个目录的文件，不需要嵌套循环。
• 处理文件时建议加上错误捕获（On Error Resume Next/On Error GoTo），避免单个文件出错导致整个程序中断。

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