Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Crash in procedure.
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 ExitHe 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 ExitHe End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Crash in procedure.
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 "Tim Rush" wrote in message ... 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 ExitHe 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 ExitHe End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Crash in procedure.
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. "Peter T" wrote: 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 "Tim Rush" wrote in message ... 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 ExitHe 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 ExitHe End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Crash in procedure.
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. "Peter T" wrote: 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 "Tim Rush" wrote in message ... 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 ExitHe 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 ExitHe End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Crash in procedure.
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. "Peter T" wrote: 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 "Tim Rush" wrote in message ... 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 ExitHe 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 ExitHe End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Crash in procedure.
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" wrote: 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. "Peter T" wrote: 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 "Tim Rush" wrote in message ... 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 ExitHe 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 ExitHe End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Crash in procedure.
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. "Tim Rush" wrote: 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. "Peter T" wrote: 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 "Tim Rush" wrote in message ... 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 ExitHe 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 ExitHe End Sub |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Crash in procedure.
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 Rush" wrote: 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" wrote: 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. "Peter T" wrote: 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 "Tim Rush" wrote in message ... 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 ExitHe 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 ExitHe End Sub |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Crash in procedure.
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. -- Tim Zych www.higherdata.com Compare data in worksheets and find differences with Workbook Compare A free, powerful, flexible Excel utility "Tim Rush" wrote in message ... 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 ExitHe 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 ExitHe End Sub |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Crash in procedure.
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 "Tim Zych" <tzych@NOSp@mE@RTHLINKDOTNET wrote in message ... 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. -- Tim Zych www.higherdata.com Compare data in worksheets and find differences with Workbook Compare A free, powerful, flexible Excel utility "Tim Rush" wrote in message ... 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 ExitHe 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 ExitHe End Sub |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Crash in procedure.
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" wrote in message ... 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. "Peter T" wrote: 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 "Tim Rush" wrote in message ... 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 ExitHe 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 ExitHe End Sub |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Crash in procedure.
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. "Peter T" wrote: 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 "Tim Zych" <tzych@NOSp@mE@RTHLINKDOTNET wrote in message ... 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. -- Tim Zych www.higherdata.com Compare data in worksheets and find differences with Workbook Compare A free, powerful, flexible Excel utility "Tim Rush" wrote in message ... 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 ExitHe 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 ExitHe End Sub |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Crash in procedure.
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" 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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Crash in procedure.
Comments in line -
"Tim Rush" 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 |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Crash in procedure.
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" 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to jump from a Form procedure to a Workbook or Module procedure? | Excel Programming | |||
Excel Crash - Help! | Excel Discussion (Misc queries) | |||
Excel ADO Crash | Excel Programming | |||
Opening a UserForm causes a crash in the Exit procedure (Which is never called) | Excel Programming |