Excel/Inexplicable 'Subscript out of range' error for Worksheet reference
Thanks in advance for any help you can lend. I have a workbook that when you click on button "Load", VBA code cycles through the text files in a certain folder and copies the data (with the data on one text file being copied into one worksheet in the main workbook). From there, the information is aggregated on a Master spreadsheet. The program has been working fine for many months.
When developing the VBA code, I learned that the best approach was to open each file within Excel. I had tried other approaches, and this was the one that worked perfectly. This was many months ago, and I have been generating a new version of this workbook each week. Only the data imported from the text files changes week to week. The Workbook and the VBA code have otherwise been unchanged and have continued to work absolutely fine - until today.
Now, the following statement suddenly does not work:
Workbooks("WBName").Activate where I have substituted "WBName" for the current name of main workbook
This statement is in a loop that I alluded to, that copies from a series of text documents in a special folder and pastes the information into the respective worksheets of the WB. The code opens each text file within Excel, as an Excel document. The text file is then closed, and the statement above has been the means to get back to the main WB.
I have a repository of prior WBs, going back weeks, each the same except that the data for each week varies. These all worked perfectly at the time, and never generated any error such as what I am getting now. But now, mysteriously, they all show the same problem described above.
I very much appreciate any suggestions you may have.
This sounds like a problem with a windows setting. It has been a long time since I played with this but:
The success enjoyed when not using the extension is dependent on a windows level setting
- folders setting to hide known extensions.
So I believe that setting has changed and now excel does not recognize your workbook name.
(use the proper extension for your workbook) should always work regardless of the setting.
Workbooks("Myworkbook") only works when the setting is set to hide known extensions.
This all assumes that your example, which did not show an extension, is actually how you are addressing the workbook.
So if you don't add the extension in your code, then you would need to change the setting I highlighted to hide known extension.