VBA无法读取非活动工作表中存储的文件路径问题求助

问题核心原因 #

你遇到的报错来自两个常见的VBA引用问题：

1. 变量作用域问题：如果声明File、File2的代码和vlookup子过程分属不同作用域（比如在两个不同的子过程内，且变量为局部变量），那么vlookup过程无法读取到之前赋值的File2值，调用Workbooks.Open时传入空值自然报错。
2. 工作表引用未绑定所属工作簿：未加前缀的Sheets("Tool")默认指向当前活动工作簿的工作表集合，当你打开第一个文件后活动工作簿切换，此时再调用Sheets("Tool")会在第一个文件中查找对应工作表，找不到就会抛出错误。

解决方法 #

方案1：匹配过程内明确读取路径（最稳妥）

直接在vlookup子过程内部读取路径，并且明确指定路径存储在工具所在的工作簿（ThisWorkbook）中，完全不受活动工作表/活动工作簿切换影响，修改后代码如下：

Sub vlookup()
    Dim rw As Long, x As Range
    Dim extwbk As Workbook, twb As Workbook
    Dim File2 As String
    
    ' 明确从工具工作簿的Tool工作表读取路径，不依赖任何活动状态
    File2 = ThisWorkbook.Sheets("Tool").Range("B3").Value
    ' 可选增加路径有效性校验，提前拦截异常
    If Dir(File2) = "" Then
        MsgBox "第二个文件路径无效，请检查Tool表B3单元格", vbCritical
        Exit Sub
    End If

    Set twb = ThisWorkbook
    Set extwbk = Workbooks.Open(File2)
    Set x = extwbk.Worksheets("Material Availability").Range("A1:H1000")

    With twb.Sheets("Material Availability")
        For rw = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
            .Cells(rw, 2) = Application.vlookup(.Cells(rw, 1).Value2, x, 8, False)
        Next rw
    End With

    extwbk.Close savechanges:=False
End Sub

方案2：声明模块级公共变量

如果你需要在多个过程里复用File、File2的路径值，可以把变量声明在模块的顶部、所有子过程之外，作用域覆盖整个模块：

' 模块顶部声明，同模块下所有子过程都可以访问这两个变量
Dim File As String
Dim File2 As String

' 你之前读取路径的子过程
Sub ReadPath()
    File = ThisWorkbook.Sheets("Tool").Range("B2").Value
    File2 = ThisWorkbook.Sheets("Tool").Range("B3").Value
End Sub

' 后续的匹配子过程可以直接读取到赋值后的File2
Sub vlookup()
    ' 原有逻辑不变，不需要再重新读取路径
End Sub

额外优化建议 #

• 所有涉及工具工作簿内工作表的引用，都加上ThisWorkbook.前缀，彻底避免活动工作簿切换带来的引用错误。
• 如果数据量较大，可以取消循环VLOOKUP的写法，直接批量写入数组公式再转值，运行效率会提升数倍。

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