CODEWITHSUNDEEP
vbaexcelruntime-error
Maksim
Maksim

Reputation: 371

Run-time error 9 in vba

Good day, I'm pretty much new in VBA, and I have a problem with path specifying, consider the following:

x = Workbooks(ThisWorkbook.Path & "/" & "06-17.xlsx").Worksheets(1).Cells(2, 1).Value

With this piece of code I have run-time error 9, but if I use the following code it works fine:

Workbooks.Open (ThisWorkbook.Path & "/" & "06-17.xlsx")
x = ActiveWorkbook.Worksheets(1).Cells(2, 1).Value

I have founded that run-time error 9 means that elements of arrays and members of collections can only be accessed within their defined ranges, but how it relates to my case, I have no idea. Why it happens?

Upvotes: 0

Views: 1248

Answers (2)

YowE3K
YowE3K

Reputation: 23974

The index to the Workbooks collection is the file name (or an integer number), not the file path and name.

So Workbooks(ThisWorkbook.Path & "/" & "06-17.xlsx") would not be valid (because it includes the path), but Workbooks("06-17.xlsx") will be valid.

This is the reason why (or, at least, related to the reason why) you can't have two workbooks open in Excel at the same time if they have the same file name, even if they have been opened from two different directories.

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96753

In the second example, the workbook is open when the second line executes.
In the first example, there is no assurance that the workbook will be open at the time of execution.

Upvotes: 2

Related Questions