5

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?

0m3r
  • 12,286
  • 15
  • 35
  • 71
Austin Wismer
  • 281
  • 1
  • 4
  • 16
  • The [Workbooks Collection](https://msdn.microsoft.com/en-us/library/office/aa224505(v=office.11).aspx) does not really explain this at all and in fact uses examples with and without the extension; the only difference being that the without example is already open. Is there any chance of naming ambiguity (a Master Schedule.xlsm and a Master Schedule.xla perhaps)? –  May 26 '15 at 20:55
  • 6
    http://www.cpearson.com/excel/fileextensions.aspx – Tim Williams May 26 '15 at 20:58
  • @TimWilliams Interesting. I happened on this same link. What might cause this setting in Windows to change? I simply copied the entire directory over to a different location to do some development work, and it started throwing the error! – Austin Wismer May 26 '15 at 21:01
  • Can't say - I've never seen it change by itself.... – Tim Williams May 26 '15 at 21:03
  • An .XLSM file extension indicates an Excel Open XML Macro-Enabled Spreadsheet, so it might be for a security reason (in case your workbook contains VBA macros). Best regards, – Alexander Bell May 27 '15 at 01:19
  • One potential conflict can be caused when you save (a temp file) as "Master Schedule" with extension **xlsx** in the same folder, and also have it be open at the same time - Workbooks Collection now contains 2 files with the exact name and the only distinction becomes the file extension – paul bica Sep 05 '15 at 23:48

1 Answers1

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.

MarekK
  • 21
  • 2
  • Unbelievable but this Windows setting impacts the Excel API behavior! Always use the extension, it will work whatever this setting. – Pragmateek Mar 04 '20 at 13:58