Re: Excel Crash in procedure.

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Just writing a simple procedure to the modules. Not opening the module.
Think I might have stumbled on a fix, although not the right way probably.
This seems to be working.
Between each call of the procedure, I am adding activeworkbook.save, then
going on to the next one. Seems to clear up something. Key word here is
SEEMS to. I've done a few of my workbooks flawlessly since. Will see how it
goes.

Anyway, I really appreciate all the effort . Will post again here if I have
further problems.

"Peter T" wrote:

Comments in line -

"Tim Rush" <TimRush@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
Update:
It appears that Excel does its crash during the write evolution.

Yes that's the risky bit!

Stepping
through the procedures line by line, its always the same point, and
appears
at the write statement.

That's a shame, my next suggestion would have been to step through and see
if that helps, seems not. However it you say it always crashes at the same
point, so what exactly is that (ie what are you writing)

Peter, I even tried using the shorter procedure you wrote with same
effect.

If by "the shorter procedure" you mean read direct from file vs your FSO
approach, indeed that would not make any difference to the overall problem.
I added it "in passing" as I couldn't see the point of creating the FSO etc.

So, perhaps its a library routine I've reference? My Reference list has
several 'Visual Basic for Applications' each using a different .dll file.
Which is the best to use, or which do you guys use? And perhaps to do
this I
need another particular one. At the moment I have the following turned
on.
Visual Basic for Applications
Microsoft Excell 11.0 Object Library
OLE Automation
Microsoft Office 11.0 Object Library
Microsoft VBScript Regular Expressions 5.5
Microsoft Visual Basic for Applications Extensibility 5.3

These all look very normal and I doubt in any related to the problem.

Have you tried first deleting all existing code entirely, then writing back
the new stuff.

Regards,
Peter T


"Peter T" wrote:

Way back I vaguely recall Rob Bovey's highly respected Code Cleaner
causing
similar problems while writing (stripping code) to Object modules in
'another' project. Seemed to be a relatively rare and intermittent bug.
I
think Rob may have introduced various pauses and DoEvents at appropriate
times to slow things down, which I think solved a significant proportion
of
the problems. I don't think even that prevented 100% of problems why not
but
not give it a shot.

Private Declare Sub Sleep _
Lib "KERNEL32.DLL" (ByVal dwMilliseconds As Long)

to pause say half a second
Sleep 500

perhaps also add a DoEvents

Add a sleep (or maybe an application.wait) and DoEvents before & after
writing the lines.

In passing, I don't see any reason to use FSO, why not read directly, eg

Sub GetTextByLines()
Dim LineNo As Long
Dim iFF As Integer
Dim Source As String, EntryLine As String
iFF = FreeFile

Close iFF ' just in case not previously closed

Source = "Y:\Quality\ovlSetup\" & module & ".txt"

On Error GoTo errH
Open Source For Input Access Read As #iFF
On Error GoTo 0

Do While Not EOF(iFF)
LineNo = LineNo + 1
Line Input #iFF, EntryLine
Debug.Print LineNo; EntryLine
Loop

errH:
Close iFF
If LineNo = 0 Then MsgBox Source & " not found"
End Sub


Regards,
Peter T






.

Relevant Pages

  • Re: Excel Crash in procedure.
    ... Microsoft Excell 11.0 Object Library ... Dim LineNo As Long ... Dim iFF As Integer ...
    (microsoft.public.excel.programming)
  • Re: Excel Crash in procedure.
    ... Microsoft Excell 11.0 Object Library ... Dim LineNo As Long ... Dim iFF As Integer ...
    (microsoft.public.excel.programming)
  • Re: the use of iff and if and only if
    ... Is there a rule for the use of 'iff' and its expanded form 'if and only ... Traditionally, in English-language mathematical writing, definitions ... or a statement that is implied by and implies ...
    (sci.math)
  • Re: VBA dates versus Excel dates
    ... I used the .Value2 property when writing the dates to the worksheet. ... End Sub ... Jon Peltier, Microsoft Excel MVP ... This post is a suggestion for Microsoft, ...
    (microsoft.public.excel.programming)
  • Re: Backup toCD
    ... I don't have any disk writing software ... > I do not have any third party software of any kind for CD writing. ... Microsoft doesn't like to reveal the true ... file that gets into some version of Windows actually came from ...
    (microsoft.public.windowsxp.basics)