如何使用vlookup vba访问封闭的Excel工作簿 [英] How to access a closed Excel Workbook using vlookup vba

问题描述

我正在尝试创建一个使用VLOOKUP访问封闭工作簿中的单元格范围的Excel VBA宏。我不太在使用VBA编辑器，但似乎没有显示许多关于错误的有用信息。

I'm trying to create a Excel VBA macro that uses VLOOKUP to access a range of cells in a closed workbook. I'm not too good at using the VBA editor, but it doesn't seem to show a lot of useful information about errors.

Sub WorkBookWithData()
    Dim currentWb As Workbook
    Set currentWb = ThisWorkbook
    Dim currentWs As Worksheet
    Set currentWs = currentWb.Sheets(1)
    Dim strFormula As String
    strFormula = "=VLOOKUP(currentWs.Range("B2"),'Macintosh HD:Users:myself:Documents:l[Master_Terms_Users.xlsm]Master_Terms_Users.csv'!A1:B222,2,false)"
    currentWs.Range("C2").Formula = strFormula

End Sub

Excel VBA编辑器挂在strFormula == VLOOKUP ...部分。

Excel VBA editor is hanging up on the "strFormula = "=VLOOKUP..." section.

谢谢

推荐答案

这看起来不像以前那样，但它可以工作。

This looks nothing like what I had previously, but it works.

Sub Check_Master_Values()

    Dim newCurWb As Workbook
    Set newCurWb = Workbooks(2)
    newCurWb.Activate

    newCurWb.Sheets(1).Range("C2").Formula = "=VLOOKUP(B2,'Macintosh HD:Users:myself:Documents:[Master_Terms_Users.xlsm]Master_Terms_Users.csv'!$A$1:$B$269,2,FALSE)"

End Sub

在我第一次尝试，我没有按照从工作簿到表格到范围的分配链。正如你可以在这段代码中看到的那样，我将一个新的工作簿 - 然后是大的哈哈时刻，我需要将其分配给正确的打开的工作簿。然后，我激活了工作簿，最后访问了表格对象和范围。

In my first attempt, I didn't follow the chain of assignments from workbook, to sheets, to ranges. As you can see in this code, I Dim a new Workbook - then the big ah-ha moment, I needed to assign it to the correct open workbook. Then, I activated the workbook, and finally accessed the Sheets object and Range.

我现在也知道我的工作簿选择号将根据打开多少其他工作簿而有所不同。 ThisBook没有工作，因为在这个过程中，本书引用的工作簿有所改变。这也许是为什么我的初始代码不起作用，除了在VLOOKUP中的不正确的编码。

I also know now that my workbook selection number will vary depending on how many other workbooks are open. The ThisBook didn't work because somehow in the process, the workbook that ThisBook referenced, changed. That is probably also why my initial code didn't work, in addition to the improper coding in the VLOOKUP.

如果有一种方法来指定哪个

It would be good if there was a way to specify which workbook on the fly.

感谢所有在VLOOKUP部分提供帮助的人。

Thanks to everyone who gave help on the VLOOKUP part.

这篇关于如何使用vlookup vba访问封闭的Excel工作簿的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！