Ref workbook by path and name - VBA

Question

Sub Quack()

Dim LookupWB As Workbook
Set LookupWB = Application.Workbooks("C:\Users\user1\Desktop\Book1.xlsx")

End Sub

This code gives an error:

Subscript out of range I think it's because of the "Set" line. How do you correctly reference a workbook by path?

If I write: Set LookupWB = Application.Workbooks("Book1.xlsx") (without full path) it work perfectly. Thanks a lot!

Answer 1

I think you need to use the Open function first:

Workbooks.Open Filename:="C:\Users\user1\Desktop\Book1.xlsx"
Set LookupWB = Application.Workbooks("Book1.xlsx")