When is the title extension required?
I have a directory of excel files that communicate with each other via VBA code in the main file.
I've never had a problem with this before, but after copying the entire directory to do some work on the copy (keeping the original intact elsewhere), I run into an "index out of range" problem when referencing the book.
For example, everything went well with this line of code (nothing in the actual code was changed):
The code that now throws an error (never used):
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 had this problem before, but I could never pinpoint the cause of the problem.
Code that doesn't throw an error:
ScheduleLocation = Workbooks("Master Schedule.xlsm").Path
Hence my question: why is this? Why is a name (without an extension) sometimes insufficient and sometimes not?
source to share
Do you have "Show file extension of known file types" included in Windows Explorer? Try to run your code with extensions hidden and visible.
It is good practice to assign the workbook to a variable on open.
Dim wbInput as Workbook
Set wbInput = Workbooks.open ("C:\Master Schedule.xslx")
Now you can work with a variable without worrying about system settings for extensions.
source to share