I have a macro which opens and reads about 90 workbooks in a
directory. There a 5 workbooks out of the 90 which, when opened,
rename themselves by appending a digit to the end of the workbook
name.

That is:
070218 Fred Smith.xls
gets opened as
070218 Fred Smith1.xls

I can't see what is different about these 5 out of 90, which will not
allow them to be opened without renaming. If I open them manually, I
don't have this problem.

The code is:

X = "070218"

With Application.FileSearch
.NewSearch
.LookIn = ProcessedFolder
.Filename = x & "*.XLS"
.SearchSubFolders = False
.Execute

For i = 1 To .FoundFiles.Count

For Each r In Range("A2:A500") 'This is a range in another
workbook .. this is working OK
If Trim(r) < "" Then 'Process only non-blank cells
If InStr(.FoundFiles(i), r) 0 Then
Workbooks.Open .FoundFiles(i), ReadOnly:=True
wbfilename = Mid(.FoundFiles(i), InStrRev(.FoundFiles(i), "\") +
1, 200)
Application.StatusBar = "Processing " & wbfilename
r.Offset(0, 2) = Workbooks(wbfilename).Sheets("Charge
Sheet").Range("CS_ThisVersion").Value '<=== Fails here with Error 9
- Subscript out of Range
Workbooks(wbfilename).Close SaveChanges:=False
End If
End If
Next r
Next i
End With

The macro fails at
r.Offset(0, 2) = Workbooks(wbfilename).Sheets("Charge
Sheet").Range("CS_ThisVersion").Value
... with a subscipt out of range error (because the wbfilebame
variable is no longer the same as the short file name due to the
renaming).

Does anyone know what would force a spreadsheet to open renamed? I
happens to the same 5 workbooks, so I think it is file attribute or
file name related but my investigations don't show any obvious
differences.


Greg