VBA run-time error 9: subscript out of range

zeno

Board Regular
Joined
Feb 16, 2012
Messages
71
Hello,

I have been looking on internet for a solution to this common error message, but have not found it.

I open a file, and in the code activate that file before closing it. I give you the code below with the line where I get the error message.

Code:
            Select Case nStage
                Case 2
                    nFileCheck = nFileCheck + 1
                    Workbooks(NameFileToOpen & FileExtention).Activate 'run-time error 9: subscript out of range
                    ActiveWorkbook.Close
So in the line where the specific file is activated, I receive this error message.
A few clarifications:
NameFileToOpen: this correctly identifies the specific file name
FileExtention: in the earlier 2003 Excel version, I only specified ".xls" but with my recent upgrade to 2007 Excel version, I now specify this as ".xls*" so that the various Excel extensions can be taken into account. I have also tested with ".xls?" and ".xls" but I receive the same error.
I did not receive this error message in 2003 version, only in 2007 version.

Thank you for your advice.
 
Last edited:

BIGTONE559

Active Member
Joined
Apr 20, 2011
Messages
336
I normally receive this error message in my code when it's looking for a particular worksheet or workbook to interact with that isn't present. My guess is that it's looking to activate a workbook that doesn't exist (may be something to do with how you've defined the workbook name).
 

zeno

Board Regular
Joined
Feb 16, 2012
Messages
71
In the beginning, I thought that too, so I verified the name definitions, which are correct. To my understanding, with the "*" in ".xls*" it should be able to identify the open file. That file is open. And in 2003 version this worked?
 
Last edited:

BIGTONE559

Active Member
Joined
Apr 20, 2011
Messages
336
In the beginning, I thought that too, so I verified the name definitions, which are correct. To my understanding, with the "*" in ".xls*" it should be able to identify the open file. That file is open. And in 2003 version this worked.
my next assumption would be issues with interoperability. Seeing as my native systems are 2007+ i'm not extremely familiar with working in the 2003 environment. Was this procedure originally written for excel 2003?
 

zeno

Board Regular
Joined
Feb 16, 2012
Messages
71
It does indeed work for 2003, but to my understanding it should also work for 2007 version. I thought that by adding "*" it could take into account the 4 letter extension of version 2007?
 

Forum statistics

Threads
1,081,862
Messages
5,361,740
Members
400,653
Latest member
ProParadox

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top