Excel 2010 crashes on Workbooks.Open for csv file -- sometimes

[Marchand]

I'm trying to open a csv file within Excel as a workbook. In the good
old days the simple

Dim i As Integer
Dim iNumRetries As Integer
Dim wkbT As Workbook

' Explicitly initialize the return value
Set openCSVFile = Nothing

' Read how many times to try
iNumRetries = U_System.Range("rngNumCSVRetries").Value

' If you can't open it then try, try again -- up to the limit
'On Error Resume Next
For i = 1 To iNumRetries
Set wkbT = Workbooks.Open(psRawName) ', 0, True, 6, , True, ,
Chr(9))
If Not (wkbT Is Nothing) Then
Exit For
End If
Next

Set openCSVFile = wkbT

worked fine. And it still does, it I walk through the code (e.g. F8).
However if the macro is called from either the ribbon or the immediate
window, Excel crashes at the Workbooks.Open() statement. And 'crashes'
in the sense of the pop-ups "Microsoft Excel has stopped working ....
is trying to recover your information ..."

Changing the code to be much more explicit, i.e.

Workbooks.OpenText Filename:=psRawName, origin:=xlWindows,
StartRow:=1, _
DataType:=xlDelimited,
TextQualifier:=xlTextQualifierNone, ConsecutiveDelimiter:=False, _
Tab:=False, Semicolon:=False, Comma:=True,
Space:=False, Other:=False

results in the same behavior. The file being opened is a plain
vanilla, nothing special, no weird characters, comma delimited file.

So I'm stumped. Anyone know why F8-ing through the code works but
letting it run as a full macro fails? Even better, any ideas on how to
fix the issue?

= M =

 

[Jim Rech]

I ran your code in Excel 2010 at full speed and had no problems. Might I
suggest running Excel in safe mode just to eliminate interactions with
add-ins or event code? (Run - > "Excel.exe /s" (no quotes).

 

[Marchand]

Jim,

Thanks for your thoughts here. There are external links in the source
Excel file. Unfortunately running things in "safe" mode shows the same
crashing behavior. Among other things I've tried (multiple times) is
running VBA CodeCleaner against the file and moving all worksheets
(and modules, etc. etc.) into a fresh workbook -- not to mention much
head banging -- all to no avail. Any other ideas?
And a new question: Other than the obvious timing issues, is there
anything else going on within Excel which would make stepping through
the code a different environment than normal invocation of a macro
thru a ribbon or other macro call?

= M =

 

[Jim Rech]

I too have seen bugs that do not occur when debugging but I never discovered
an explanation. I've guessed that Excel had time to re-right itself at
debugging speed that it did not have at normal speed. Nothing you can do
with that conjuncture even if it is right, of course.

As an aside I need to say that Excel 2007 and 2010 crash ten times more than
Excel 2003 ever did. I save changes very frequently...

I had no problem with your macro. If you put your code in a new, otherwise
empty workbook does it run okay? If so there is something about the real
workbook that is pissing Excel off. In situations like that I create a
succession of problem workbooks each one eliminating something else, until I
get to a basically empty workbook (hopefully you don't have to go that far).
For instance, first I'd create a version with all links deleted. If that
still crashed then I'd remove all defined names, then formulas, then
formats, then shapes, etc. Eventually the code runs. Then I start with the
original and eliminate just that last thing to see if that's the issue. If
I'm lucky it is. Then I refine that. Delete _some_ names or _some_ formats
or _some_ formulas. Eventually I find some stupid thing that shouldn't in a
million years be the problem but it is anyway. So then I remove that, my
code runs, life goes on and I'm really no wiser for it. Yeah, this can take
a while.


Jim,

Thanks for your thoughts here. There are external links in the source
Excel file. Unfortunately running things in "safe" mode shows the same
crashing behavior. Among other things I've tried (multiple times) is
running VBA CodeCleaner against the file and moving all worksheets
(and modules, etc. etc.) into a fresh workbook -- not to mention much
head banging -- all to no avail. Any other ideas?
And a new question: Other than the obvious timing issues, is there
anything else going on within Excel which would make stepping through
the code a different environment than normal invocation of a macro
thru a ribbon or other macro call?

= M =

 

[Marchand]

Jim,

Thanks again for your thoughts and observations. Interestingly, I
have been paring away non-essential parts of the workbook to see if I
can isolate the problem-causing areas. Dropping cell formulas
bringing in DDE data solved things for a bit but not in a consistent,
reproducible manner. And yes -- isolating the minimum VBA code cycling
through and opening all the csv files works flawlessly. (sigh)

= M =