XL 2004 macro crashes while looping through workbooks

Posted: 12-12-2006, 10:35 PM
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.

Reply With Quote

Responses to "XL 2004 macro crashes while looping through workbooks"

Geoff Lilley
Guest
Posts: n/a
 
Re: XL 2004 macro crashes while looping through workbooks
Posted: 12-14-2006, 11:23 PM
Does it only occur on this machine?

paulr wrote:
> 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.
Reply With Quote
paulr
Guest
Posts: n/a
 
Re: XL 2004 macro crashes while looping through workbooks
Posted: 12-15-2006, 07:40 PM


On Dec 14, 6:23 pm, "Geoff Lilley" <ruthlesslightn...@gmail.com>
wrote:
> 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?

Reply With Quote
 
LinkBack Thread Tools Display Modes
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are Off
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel 2004 Macro security bbbentley@gmail.com Mac Office Excel 5 09-21-2006 03:09 AM
Excel crashes when attempting to create a macro rjklaus@earthlink.net Mac Office Excel 1 03-14-2006 01:18 PM
Excel 2004: properly saved workbooks slip up under toolbars? Norman R. Nager, Ph.D. Mac Office Excel 6 02-20-2005 11:15 PM
Excel Macro Language and Office 2004 dalgleishj72@alum.darden.edu Mac Office Excel 6 12-16-2004 09:42 PM
PowerPoint 2004 in Office 2004 Mac crashes on all older documents. Kurt Lang Mac Office Power Point 4 10-12-2004 01:10 AM


1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90