Macro misteriously crashes Excel 2007 - but not 2003

[myemail.an]

I - unfortunately - use Excel 2007. I wrote a macro for the following
task:

- a folder contains a number of files, each with 3 tabs, "Source
Data", "Details" and "Summary". These files are reports, all in the
same format as they are created by
another macro. "Details" and "Summary" contain formulas based on the
content of "Source Data"

- the macro cycles through all files in the folder and merges them in
2 files, pasting values only and leaving out the source data

- the output is therefore 2 files: "MergedDetails" and
"MergedSummaries"; each of those files contains one tab per original
source file. For example, if you want to quickly compare the details
of File1 and File2, you'll open MergedDetails and compare the tabs
File1 and File2. Same for the summaries.

The problem is, my code kept crashing Excel. To isolate the problem, I
separated the code into 2 different macros: one for the details and
one for the summary.
The latter works, the former keeps crashing Excel.

I tried all sorts of workarounds: I ran the macro on local (rather
than network) folders, I set 2 to 4 second delays before closing and
saving files, but nothing changed.

The weirdest thing is that, debugging the code, I noticed that Excel
always crashed at different points in the macro: sometimes after
processing the 3rd file, some other time after processing the 8th,
sometimes after closing one file, some other time after copying some
cells... I never got any out of memory error.

I don't get this. If my code is wrong, why does Excel always crash at
different points in the code?

Finally, I tried the very same code on a different PC running Excel
2003 (and on the very same source files), and it does work fine!

Would you have any suggestions on how I can get my code to run with
Excel 2007? Or at least on how I can get some additional help?

I know some Excel 2007 macro functionalities are no longer supported (
like Application.Filesearch, for instance), but at least in those
cases you get a clear error message that helps you understand what the
issue is!

 

[Jon Peltier]

And what does this errant code look like?

- Jon

 

[myemail.an]

And what does this errant code look like?

- Jon

The problem is this:

I have a folder with a number of files; each file has 3 tabs: source
data, detailed analysis and summary analysis. The number of files
varies from 2 to 40, depending on the situations.

My macro processes all files in the folder and merges the analysis
tabs into another file. For example, if there are 20 files, the macro
creates one file "summary.xls" with 20 tabs, one per file, and does
the same with the detailed analyses, copying them to "details.xls".
Only values are copied, not formulas, as source data is not copied.

To do the above, I wrote the sub MyMerge(), which in turn calls the
sub CopyTabVakuesToOtherFile().

If I run MyMerge on the summary analysis, it always works. If I run it
on the detailed analysis, it works in Excel 2003 but crashes in Excel
2007, always at different points in the code.
The code is:

Sub MyMerge(TargetFile, MyFolder, MyTabName, MyPath)
'TargetFile: the file to be created, where tabs will be copied
'MyFolder: the folder containing the files to process (from which to
copy)
'MyTabName: the name of the tab - worksheet - from which to copy
'Mypath: the path of the workbook contaning the macro


'for some misterious reason, this code works fine in Excel 2003 but
often crashes Excel 2007
'no error message is given, nor can I find any pattern in the
behaviour of the software:
'sometimes it crashes, sometimes it doesn't, and when it does, it
always does at different points in the code

'I added very detailed descriptions in the statusbar so as to
precisely identify
'what steps have been completed

' I only want 1 tab in the new workbooks this macro will create
Application.SheetsInNewWorkbook = 1

'creates the file where the reports will be merged
'and renames the worksheet to "ToBeDeleted" because the 1st, empty tab
will not be needed and we'll delete it
'at the end of the whole process
Workbooks.Add
ActiveSheet.Name = "ToBeDeleted"
ActiveWorkbook.SaveAs Filename:=MyPath & "\" & TargetFile,
CreateBackup:=False, FileFormat:=xlExcel8

thefile = Dir(MyFolder & "\")

FileNumber = 0
'we must specify a variable thefile=dir and then set the while
condition on it, non directly on dir
'otherwise dir would be called more times than needed
Do While thefile <> ""

'full path of the file being analyzed in the strats folder
thefilepath = MyFolder & "\" & thefile

'counter to update the status bar showing the progress
'since this is a do... loop (and not a for ... next) we don't know
how many files are in the path
'before running the whole cycle
FileNumber = FileNumber + 1
Application.StatusBar = "Processing file " & FileNumber

'opens the file with the data for segment currently being analyzed
Workbooks.Open (thefilepath)
CurrentlyOpenFile = ActiveWorkbook.Name

Application.StatusBar = "Processing file " & FileNumber & " - file
opened"

Call CopyTabValuesToOtherFile(MyTabName, TargetFile, thefile)

Application.StatusBar = "Processing file " & FileNumber & " - tab
copied to the target file"

'closes the file with the segment data
Workbooks(CurrentlyOpenFile).Close saveChanges:=False
Application.StatusBar = "Processing file " & FileNumber & " -
source file closed"

Workbooks(TargetFile).Save
Application.StatusBar = "Processing file " & FileNumber & " -
target file saved"

'searches the folder again to check if there is any other file
thefile = Dir()
Application.StatusBar = "Processing file " & FileNumber & " - end
of loop"
Loop

Application.StatusBar = "All files processed; almost done"
Call WaitSeconds(Wait)


Application.DisplayAlerts = False
Workbooks(TargetFile).Sheets("ToBeDeleted").Delete
Application.DisplayAlerts = True

Application.StatusBar = "Blank worksheet deleted"


Workbooks(TargetFile).Save


Application.StatusBar = "File saved"


Workbooks(TargetFile).Close

End Sub


Sub CopyTabValuesToOtherFile(SourceTab, TargetFile, TargetTab)
'copies SourceTab to TargetTab in TargetFile
'pasting values only

'stores the name of the currently active file
MyActiveFile = ActiveWorkbook.Name
Sheets(SourceTab).Copy Before:=Workbooks(TargetFile).Sheets(1)
Workbooks(TargetFile).Activate
Sheets(SourceTab).Select
Cells.Select
Selection.Copy

'pastes values
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

'clears the clipboard; this frees memory and makes sure the macro
is not interrumpted by the message asking
'whether we want to clear the clipboard or not
Application.CutCopyMode = False

'renames the recently copied tab
'trims the text to the first 30 characters
Sheets(SourceTab).Name = Left(TargetTab, 30)

'deletes named ranges in the file
'Necessary as all tabs have the same named ranges
For Each varname In ActiveWorkbook.Names
varname.Delete
Next

'reactivates the file which was active before calling this sub
Workbooks(MyActiveFile).Activate


End Sub

Any help would be enormously appreciated as this is driving me nuts!