CODEWITHSUNDEEP
excelexcel-2007vba
Sam
Sam

Reputation: 3177

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!

Upvotes: 8

Views: 87780

Answers (2)

Kes Perron
Kes Perron

Reputation: 477

If the workbook is already open, you don't need to include the path. That's why Set LookupWB = Application.Workbooks("Book1.xlsx") works.

If the workbook is closed, then you have to include the Open function (see @thornomad's answer).

Upvotes: 1

thornomad
thornomad

Reputation: 6797

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

Upvotes: 19

Related Questions