XL 2004 macro crashes while looping through workbooks

[paulr]

I have a series of macros which cycle through identically structured
workbooks, collecting and reporting information from each. The macros
work(ed) fine in xl2001 and xl.x, but not in 2004. At first I found
that findfile had been dropped but replaced it using Dir. This works in
all 3 versions of xl but only briefly in xl2004. After a few (3-10)
cycles of looping, xl2004 will crash. I've only been able to avoid this
by disabling the statement which closes the sampled workbook at the
conclusion of the loop. No real solution since some of the macros cycle
through >1000 workbooks. Here's one of the problem chidren:

Dim stufile, cursht, studentsPath
SetPaths ' Set the path to top level of folders
stufile = Dir(MainPath & stufolder(J) & aps & "open" & aps, vbNormal)
'names first file in stufolder (J) aps = '

path separator
cursht = ActiveSheet.Name
Do While stufile <> "" ' Start the loop.
If LCase(Left(stufile, 3)) = _
stufolder(J) And (Right(stufile, 1) = "H" Or Right(stufile,
1) = "C" Or Right(stufile, 4) = "H.xls" Or Right(stufile, 4) = "C.xls")
Then ' trap for stray files
DisableEventsAlerts
Workbooks.Open studentsPath & stufolder(J) & aps & "open" & aps
& stufile
EnableEventsAlerts
monthlywrkshtdata 'read stats from each open workbook
DisableEventsAlerts
ActiveWorkbook.Saved = True
ActiveWorkbook.Close
EnableEventsAlerts
Else 'note any non-midas files found on error log
...
End If
stufile = Dir 'get next file
Loop
'Next
End Sub

Why does xl2004 crash while looping and why would closing workbooks be
a factor (there are no on-close events)? I've updated, repaired
permissions, dumped prefs, etc. I have a G5 imac and 10.4.8. I hope
this makes sense. Any help appreciated.

 

[Geoff Lilley]

Does it only occur on this machine?

 

[paulr]

Does it only occur on this machine?

No, it occurs on other machines as well. I have found more oddities
since my first post.

1. Problem occurs only with files from previous excel versions ie. xl x
and xl 2001. These are all mac files, not windows. Once I've saved a
batch of files in xl2004, they open without problem.

2. Problem occurs when I manually open files as well as by script. So I
guess it's not a vb problem.

3. Problem occurs when I open and close files one by one. Problem does
not occur if I open a batch of files (as many as 20+) and then "close
all" saving changes.

I would think this would be a noticeable problem for anyone upgrading
large numbers of xl files to xl 2004.

Any ideas?