VBA simple Vlookup to closed workbook - syntax error

2
votes

I have a spreadsheet which uses a regular Vlookup function to access data in another workbook called archive.xlsx, saved in the same location as my active workbook. The normal/non-VBA function looks like this and works as intended:

=VLOOKUP(A2,[archive.xlsx]watchlist!A2:L2000,5,FALSE)

I am trying to recreate this in VBA with the goal of being able to run it without having to manually open/close the archive.xlsx file. I'm not very experienced with VBA so I looked at some other posts here asking similar questions and ended up with this line of code (adapted to my own formula) that apparently works for some other users:

Sub Vlookup()

Range("E2") = Application.Vlookup(Range("A2"),([archive.xlsx]watchlist.Range("A2:L2000")),5,FALSE)

End Sub

(the Vlookup result should go to cell E2)

This however gives me a syntax error. I've been looking at this for like an hour now and tried changing dozens of little things but I can't get it to work at all. It's probably something very silly but can anyone see what I'm doing wrong here?

3
excelvbavlookup

3 Answers

1
votes

You can't use that syntax for many reasons. First, that is not the correct syntax to access ranges. Also, that is no way to pass archive.xlsx (it's not a variable!). You need to get the path (which is the same as your current workbook's path since they are in the same location) and then build up the correct string reference as below:

Sub Vlookup()

Dim VLPath As String
With ThisWorkbook
    'Now get the full path to the table being looked up in archive.xlsx
    VLPath = "'" & .Path & "\[archive.xlsx]watchlist'!A2:L2000"
    .Range("E2").Formula = "=VLOOKUP(A2," & VLPath & ",5,FALSE)"
End With

End Sub

Some Explanation:

If you closed archive.xlsx and checked the formula in Cell E2 of your current workbook, it would contain the full path of archive.xlsx, such as:

VLOOKUP(A2,'C:\blah\[archive.xlsx]watchlist'!A2:L2000,5,FALSE)

This is the path and string pattern that we need to use in VBA. Now how do we get it? Since we know that it is in the same folder as your current workbook, we call the .Path function to get us that location and then we can build the exact replica of the lookup string we have above. And that's what the code does!

1
votes

If you apply this function =VLOOKUP(A2,[archive.xlsx]watchlist!A2:L2000,5,FALSE) in a spreadsheet, and close the workbook archive.xlsx, you will see the path in the Function turning to Full Path to the File.

Will become something like:

=VLOOKUP(A2,'C:\-------\[archive.xlsx]watchlist'!A2:L2000,5,FALSE)

Now in VBA I think you should use the Function with Full path like this:

Range("E2").Formula = "=Vlookup(" & Range("A2") & ",'C:\-----\[archive.xlsx]watchlist'!A2:L2000,5,FALSE)"
1
votes

If you need formula in cell it could also work to simply write something like that in E2 Cell

=VLOOKUP(1,'D:[archive.xlsx]Sheet1'!$A$1:$L$2000,5,FALSE)

If you want to make it work with VBA, you can do:

Sub Vlookup()
    Range("E2").FormulaR1C1 = "=VLOOKUP(1,'D:\[archive.xlsx]Sheet1'!R1C1:R2000C12,5,FALSE)"
    Application.Calculate
    Range("E2").Value = Range("E2").Value
End Sub

just change file path