How to access a closed Excel Workbook using vlooku

Question

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 editor is hanging up on the "strFormula = "=VLOOKUP..." section.

Thanks

Answer 1

Reference from Siddharth Rout's comments.

The main problem in your code is this line:

strFormula = "=VLOOKUP(currentWs.Range("B2"),'Macintosh HD:Users:myself:Documents:l[Master_Terms_Users.xlsm]Master_Terms_Users.csv'!A1:B222,2,false)"

because of this code currentWs.Range("B2"). We know that you want to indicate Range("B2") of Current Sheet(same sheet). So, you can use as follow:

strFormula = "=VLOOKUP(B2,'Macintosh HD:Users:myself:Documents:l[Master_Terms_Users.xlsm]Master_Terms_Users.csv'!A1:B‌​222,2,false)"

Why? It can use just B2 because you set formula to a cell which is in the same sheet. So, it is not need to indicate the Sheet Name.

And If you want to set a cell which is from other sheet, you need to indicate Sheet Name in that case. So, should use as follow:

strFormula = "=VLOOKUP(" & currentWs.name & "!B2,'Macintosh HD:Users:myself:Documents:l[Master_Terms_Users.xlsm]Master_Terms_Users.csv'!A1:B222,2,false)"

Answer 2

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.

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.

Thanks to everyone who gave help on the VLOOKUP part.