How to access a closed Excel Workbook using vlookup vba

1
votes

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

2
vbaexcel
1 First open the 2nd workbook. 2 In the first workbook, manually type the Vlookup formula. 3 Close the 2nd workbook 4 Now the Vlookup formula will show paths. Copy that formula as it is 5 strFormula = that formula which you copied – Siddharth Rout
currentWs.Range("B2") inside quotes doesn't make any sense :) – Siddharth Rout
Something like this I guess (Don't have MAC to test it) strFormula = "=VLOOKUP(B2,'Macintosh HD:Users:myself:Documents:l[Master_Terms_Users.xlsm]Master_Terms_Users.csv'!A1:B222,2,false)" – Siddharth Rout

2 Answers

2
votes

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)"
0
votes

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.