VBA vlookup 公式引用另一个打开的工作簿
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23528909/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA vlookup formula refering another opened workbook
提问by user3614021
I am trying to add a vlookup formula by using VBA, however, it didn't work for me. It's refering to another workbook and the workbook's name is save in a variable called FileName1. Below are the code I wrote. Could any one please help?
我正在尝试使用 VBA 添加 vlookup 公式，但是，它对我不起作用。它引用另一个工作簿，工作簿的名称保存在名为 FileName1 的变量中。下面是我写的代码。有人可以帮忙吗？
WbkT3.Worksheets("Finance").Range("G2") = "=VLOOKUP(C2,[" & FileName1 & "]Divisor!$A:$F,3,FALSE)"
回答by Richard Vivian
The vlookup formula is not 100% correct. The range should refer to something like $B$3:$C$5. Add the missing row references, and the formula should work.
vlookup 公式不是 100% 正确的。该范围应指代 $B$3:$C$5 之类的内容。添加缺少的行引用，公式应该可以工作。
How excel is interpreting the file name is the most probable cause of the problem.
excel 如何解释文件名是问题的最可能原因。
Try this. Remove the square brackets from the formula replace with single quotes. Add teh square brackets to the file name variable if possible. Code below was tested and works.
尝试这个。从公式中删除方括号替换为单引号。如果可能，将方括号添加到文件名变量中。下面的代码已经过测试并且有效。
Dim FileName1 As String
FileName1 = "C:\temp\[Book2.xlsx]"
ActiveWorkbook.Worksheets("Sheet1").Range("G2") = "=VLOOKUP(C2,'" & FileName1 & "Sheet1'!$B:$C,2,FALSE)"
