Search code examples
excelexcel-2007vba

Ref workbook by path and name - VBA

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!

Solution

  • 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")