Error btw Mac Excel 2003 and PC Excel 2007: "Run-time error '9' Subscript out of range"

[kachunyu]

Hi,

I have a Excel/VBA script written 5-6 years ago on a Mac running Excel -- probably 2003. (It is not an option to go back and work on that Mac and software version.) I need to get this to run in Excel 2007 on a PC.

This is my first time debugging VBA scripts. I've been able to remove some Mac-specific syntax that was causing problems. However I've run into a wall with a "Run-time error '9' Subscript out of range" error that has halted my progress.

As a novice at this, I may be including much more than is necessary so please bear with me. First the main script do_all_test() immediately calls multiple sub procedures:

Sub do_all_test()
Application.ScreenUpdating = False
get_all_file_names_auto
open_files
create_temp_name_list

The first queries the user for input Excel file(s):

Sub get_all_file_names_auto()
Sheets.Add Type:="Worksheet"
ActiveSheet.Name = "TEMP"
num_files = InputBox("How many files are being analyze?")
For i = 1 To num_files
    filepath = Application.GetOpenFilename("Excel-files,*.xls", 1, "Select one file to open", , False)
    Sheets("TEMP").Cells(nextRow, 1) = filepath
    For j = 1 To 100
        If Mid(filepath, Len(filepath) - j, 1) = ":" Then
            Exit For
        End If
    Next j
    Sheets("TEMP").Cells(nextRow, 2) = Mid(filepath, 1, 1000)
    nextRow = nextRow + 1
Next i
Sheets("TEMP").Range("A1").Select
Do Until ActiveCell.Value = ""
    If ActiveCell.Value = ActiveWorkbook.Name Then
        ActiveCell.EntireRow.Delete shift:=xlUp
    End If
    ActiveCell.Offset(1, 0).Select
Loop
Sheets("TEMP").Range("A1").Select
Error:
End Sub

The next procedure opens the file(s):

Sub open_files()
this_file = ThisWorkbook.Name
For x = 1 To num_files
    Sheets("TEMP").Range(Cells(x, 1), Cells(x, 1)).Select
    Workbooks.Open Filename:=ActiveCell.Value
    Workbooks(this_file).Activate
    Next x
End Sub

Finally the "Run-time error '9'" shows up in the third procedure, at the Workbooks().Activate line:

Sub create_temp_name_list()
    Sheets("temp_name_list").Cells.Clear
For current_file = 1 To num_files
    Sheets("temp_name_list").Cells.Clear
    file_to_work_with = Sheets("TEMP").Cells(current_file, 2).Value
    Workbooks(file_to_work_with).Activate
    ...
    Next current_file
End Sub

What is causing the script to crash in Windows but not on the Mac? Messages on these forums reveal that others who run into the same error message usually discover it's an issue of not opening the Workbook correctly. But hasn't that been properly done in the open_files() procedure?

--kachun

 

[shg]

You're welcome, good luck.