When is an extension required in workbook name? [duplicate]

Question

I have a directory of excel files that interact with each other through VBA code in a master file.

I've never had a problem with this before, but after copying the whole directory to do some development work on the copy (keeping the original intact in a different location) I'm running into a "subscript out of range" problem when referencing the workbook.

For example, everything ran fine previously with this line of code (nothing in the actual code has been changed):

Code that now throws an error (never used to):

ScheduleLocation = Workbooks("Master Schedule").Path

However, this line now throws an error. If I replace "Master Schedule" with "Master Schedule.xlsm" everything works again. I've had this problem before, but I've never been able to put a finger on the root cause of the problem.

Code that doesn't throw an error:

ScheduleLocation = Workbooks("Master Schedule.xlsm").Path

Hence my question: why is this? Why would the name (without extension) be insufficient sometimes, and sometimes not?

Answer 1

Have you got "Show file extension of known file types" turned on in windows explorer? Try to run the code with hidden and visible extensions.

It is good practice to assign the workbook to variable on open.

Dim wbInput as Workbook
Set wbInput = Workbooks.open ("C:\Master Schedule.xslx")

Now you can work on variable without caring about the system settings for extensions.