Excel Crash in procedure.

[Tim Rush]

The procedure below works sometimes and sometimes not.
This is basically Chip Pearson's vbProject routines.
Everything works fine initializing all vb objects and beginning the add
procdure. Apparently I get the entire text file entered, then (stepping
through procdure) when the End With is highlighted just above the LOOP,
Excel does a complete dump and error recovery.
Happens 7 out of 10 times, the other times, it works fine, so that makes it
hard to figure out.
I'm using Excel2003, winXP
I do have the extendability library on per Chip
If somebody would like the whole file, I can zip and upload a declassified
version.

Private Sub AddProcedureToModule()
Dim module As String 'These lines added to test procedure only
module = "OVL"
'
'
On Error GoTo ErrorTrap
'Private Sub AddProcedureToModule(module As String) 'Procedure from Chip
Pearson
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule
Dim objFSO As Object
Dim objFile As Object
Dim LineNum As Long
Dim Source As String
Dim EntryLine As String
Const DQUOTE = """" ' one " character
Source = "Y:\Quality\ovlSetup\" & module & ".txt" 'location of code
to enter
Set VBProj = activeworkbook.VBProject
'Set VBComp = VBProj.VBComponents(module)
Set VBComp = VBProj.VBComponents(Worksheets(module).CodeName)
'modification by Dave Peterson
Set CodeMod = VBComp.CodeModule
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(Source, 1)
Do Until objFile.AtEndOfStream
EntryLine = objFile.readline
With CodeMod
LineNum = .CountOfLines + 1
.InsertLines LineNum, EntryLine
End With
Loop
ExitHere:
objFile.Close
Set objFSO = Nothing
Set objFile = Nothing
Exit Sub
ErrorTrap: 'added brief error handling
MsgBox ("Error in AddProcedure routine.") 'informs which procedure
to debug
'useful with on error
resume next
GoTo ExitHere:
End Sub

 

[Peter T]

Set VBProj = activeworkbook.VBProject

Is the Activeworkbook same as Thisworkbook. Trying to write code to
worksheet and thisworkbook modules of the same project that's running the
code is fraught with problems.

Regards,
Peter T

 

[Joel]

There is something wrong with the text file or with the way windows is
getting the text file. Try putting the text file on the C: drive instead of
the Y: drive and see if you get the same problem.

 

[Tim Rush]

The activeworkbook and the workbook with the running code are separate. I
have a hidden workbook with the procedures in it that are controlling this.
I then open the workbook(s) needing the update, and run the procedure while
they are active.

 

[Tim Rush]

The text file with the code being entered is just text. Nothing special. I
did a copy-paste of the code I wanted to use, right into notepad, saved it
normally.

 

[Tim Rush]

Originally it was on the C: drive. Had the problem at then as well. I moved
everything to a mapped drive so I can use it remotely later. Although I will
update all master files from here, I'll end up going on the shop floor and
upgrading the working copies, (although not as many), remotely.

 

[Joel]

Tim: I'm worried about the network disk drive having problems. Thought if we
put the text file on the C drive it would help to isolate the cause of the
problem. There may be disk errors on the drive or the drive may be busy and
you are having a time out problem.

 

[Joel]

Lets see if the problem is with reading the text file or writing to the VBA
code. Try commenting out the line

..InsertLines LineNum, EntryLine

If the problem disappears then we know the problem is the writing and not
the reading.

 

[Tim Zych]

One thing I've noticed while importing code, especially for large projects,
is that Excel can and does crash if the project is not compiled first. My
personal preference is not to use code that writes code in a production
environment for a number of reasons, but for personal use, definitely.
Compiling the project where the code is going before running the import
macro has been a reliable prerequisite for me. Perhaps this is why it works
for you sometimes, because the compiler was happy 3 out of 10 times you ran
it.

 

[Peter T]

That's interesting but I'm surprised that makes any difference. As soon as
you start writing new lines the project is no longer fully compiled.

Regards,
Peter T

 

[Peter T]

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

 

[Tim Rush]

Ok, the Sleep idea makes a a little sense. Certainly won't hurt anything,
I'm looking at 3 seconds of code anyway.

Here is a bit that I just noticed though...
I'm writing code to 2 modules so I'm running through the procedure twice.
The Excel dump appears to happen (most often) after the second module is
written.
Does not seem to matter which order I put them in.
One is a sheet module, the other is the 'ThisWorkbook' module.
Running just one (either one) does not give me any problems during the 20 or
so times I ran it.

Sad thing is, by the time we figure it out, I probably could have done the
200 or so files by hand , although i have further use for it after this.

 

[Tim Rush]

Update:
It appears that Excel does its crash during the write evolution. Stepping
through the procedures line by line, its always the same point, and appears
at the write statement.
Peter, I even tried using the shorter procedure you wrote with same effect.

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

 

[Peter T]

Comments in line -

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

 

[Tim Rush]

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.

 

[Tim Rush]

Final Answer:

What I learned thru this is to slow down. Using the sleep within the
procedure had some minor help. Since I was modifying more than one module,
Excel really hated me, so a sleep between modules was helpful.
That was not always enough. I then added a activeworkbook.save between the
write procedures and also after removing any old code. (Better again).
I ended up breaking up the whole thing and doing it in 2 pieces. One for
each module I was writing to.
In the end I was able to open several workbooks, delete the old code, write
a module, save, close go to next workbook. Then reopen workbooks, wrtie next
module, sav, close. FINISHED!.

Thanks to all those who helped.