Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
.insertlines automation error excel crash
Hi,
I'm having a quite strange problem in a VBA macro that I made that is driving me crazy. First of all, I am using Excel 2003. Well, when I try to insert lines of code in vba worksheet "sheet1", I always get an Automation Error that causes the restart of excel. The precise error is: "Automation Error. connection to type library or object library for remote process has been lost. Press OK for dialog ot remove reference." The error always happens at the SECOND insertline instruction (the first line in sheet1 is written correctly) in the following code: ----...---- Set VBCodeModmej = ActiveWorkbook.VBProject.VBComponents(ActiveWorkbo ok.Worksheets("Sheet1").CodeName).CodeModule VBCodeModmej.InsertLines 1, "Private Sub Worksheet_SelectionChange(ByVal Target As Range) " VBCodeModmej.InsertLines 2, "Dim Touched As Range" VBCodeModmej.InsertLines 3, "Dim void As String" etc etc ---...--- Note that with Office 2007 this never happens ...! I tried the following things, read here and there for the network (I tried them one at a time ...): -Put option explicit: useless -Put on error resume next: useless -Put Instead of 'private sub' the string 'sub': useless -Put Application.EnableEvents = True: useless -Put Application.OnTime Now + TimeValue("00:00:10") before the Set line: useless Can you help me somehow? Thank you so much right now Mark |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
.insertlines automation error excel crash
You may find something here.
http://www.cpearson.com/excel/vbe.aspx I've not done anything like what you are doing, but I wonder if you already have some lines in the code module and are trying to insert lines where some already exist. Have you checked that? -- HTH, Barb Reinhardt "perf" wrote: Hi, I'm having a quite strange problem in a VBA macro that I made that is driving me crazy. First of all, I am using Excel 2003. Well, when I try to insert lines of code in vba worksheet "sheet1", I always get an Automation Error that causes the restart of excel. The precise error is: "Automation Error. connection to type library or object library for remote process has been lost. Press OK for dialog ot remove reference." The error always happens at the SECOND insertline instruction (the first line in sheet1 is written correctly) in the following code: ----...---- Set VBCodeModmej = ActiveWorkbook.VBProject.VBComponents(ActiveWorkbo ok.Worksheets("Sheet1").CodeName).CodeModule VBCodeModmej.InsertLines 1, "Private Sub Worksheet_SelectionChange(ByVal Target As Range) " VBCodeModmej.InsertLines 2, "Dim Touched As Range" VBCodeModmej.InsertLines 3, "Dim void As String" etc etc ---...--- Note that with Office 2007 this never happens ...! I tried the following things, read here and there for the network (I tried them one at a time ...): -Put option explicit: useless -Put on error resume next: useless -Put Instead of 'private sub' the string 'sub': useless -Put Application.EnableEvents = True: useless -Put Application.OnTime Now + TimeValue("00:00:10") before the Set line: useless Can you help me somehow? Thank you so much right now Mark |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
.insertlines automation error excel crash
Hi,
yes yes, I only write lines if the module is empty, otherwise I delete all lines and then I write them. your link was useful, and I made some change in the code, that (apparently without reason) allowed me to write in the sheet1 module if the module is originally empty. But I still have that problem if the module is not empty: after deleting the rows, as soon as I try to write in the module, there is the automation error. moreover, I am noticing that the SAVE command is not working properly with ..xls. Sometimes it saves the fresh code in the module, sometimes not. randomly. this is sooooo unpredictable... not good at all : ( thanks "Barb Reinhardt" wrote: You may find something here. http://www.cpearson.com/excel/vbe.aspx I've not done anything like what you are doing, but I wonder if you already have some lines in the code module and are trying to insert lines where some already exist. Have you checked that? -- HTH, Barb Reinhardt "perf" wrote: Hi, I'm having a quite strange problem in a VBA macro that I made that is driving me crazy. First of all, I am using Excel 2003. Well, when I try to insert lines of code in vba worksheet "sheet1", I always get an Automation Error that causes the restart of excel. The precise error is: "Automation Error. connection to type library or object library for remote process has been lost. Press OK for dialog ot remove reference." The error always happens at the SECOND insertline instruction (the first line in sheet1 is written correctly) in the following code: ----...---- Set VBCodeModmej = ActiveWorkbook.VBProject.VBComponents(ActiveWorkbo ok.Worksheets("Sheet1").CodeName).CodeModule VBCodeModmej.InsertLines 1, "Private Sub Worksheet_SelectionChange(ByVal Target As Range) " VBCodeModmej.InsertLines 2, "Dim Touched As Range" VBCodeModmej.InsertLines 3, "Dim void As String" etc etc ---...--- Note that with Office 2007 this never happens ...! I tried the following things, read here and there for the network (I tried them one at a time ...): -Put option explicit: useless -Put on error resume next: useless -Put Instead of 'private sub' the string 'sub': useless -Put Application.EnableEvents = True: useless -Put Application.OnTime Now + TimeValue("00:00:10") before the Set line: useless Can you help me somehow? Thank you so much right now Mark |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
.insertlines automation error excel crash
There can be unpredictable problems writing to worksheet modules in the same
workbook that contains the code that's writing the changes. This can lead to crashing Excel. It might depend on what changes are being made and possibly if the sheet that's being modified is also the active-sheet. Whatever the reason, the underlying cause is probably due to the project trying to recompile while code is running. Regards, Peter T "perf" wrote in message ... Hi, yes yes, I only write lines if the module is empty, otherwise I delete all lines and then I write them. your link was useful, and I made some change in the code, that (apparently without reason) allowed me to write in the sheet1 module if the module is originally empty. But I still have that problem if the module is not empty: after deleting the rows, as soon as I try to write in the module, there is the automation error. moreover, I am noticing that the SAVE command is not working properly with .xls. Sometimes it saves the fresh code in the module, sometimes not. randomly. this is sooooo unpredictable... not good at all : ( thanks "Barb Reinhardt" wrote: You may find something here. http://www.cpearson.com/excel/vbe.aspx I've not done anything like what you are doing, but I wonder if you already have some lines in the code module and are trying to insert lines where some already exist. Have you checked that? -- HTH, Barb Reinhardt "perf" wrote: Hi, I'm having a quite strange problem in a VBA macro that I made that is driving me crazy. First of all, I am using Excel 2003. Well, when I try to insert lines of code in vba worksheet "sheet1", I always get an Automation Error that causes the restart of excel. The precise error is: "Automation Error. connection to type library or object library for remote process has been lost. Press OK for dialog ot remove reference." The error always happens at the SECOND insertline instruction (the first line in sheet1 is written correctly) in the following code: ----...---- Set VBCodeModmej = ActiveWorkbook.VBProject.VBComponents(ActiveWorkbo ok.Worksheets("Sheet1").CodeName).CodeModule VBCodeModmej.InsertLines 1, "Private Sub Worksheet_SelectionChange(ByVal Target As Range) " VBCodeModmej.InsertLines 2, "Dim Touched As Range" VBCodeModmej.InsertLines 3, "Dim void As String" etc etc ---...--- Note that with Office 2007 this never happens ...! I tried the following things, read here and there for the network (I tried them one at a time ...): -Put option explicit: useless -Put on error resume next: useless -Put Instead of 'private sub' the string 'sub': useless -Put Application.EnableEvents = True: useless -Put Application.OnTime Now + TimeValue("00:00:10") before the Set line: useless Can you help me somehow? Thank you so much right now Mark |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
.insertlines automation error excel crash
yes, it can be as you say.
do you think there can be a solution to this? for istance, disabling each type of operation when writing code? I do not know if you can do it, though. by the way, the code in the worksheet is written by a macro xla, so something 'outside' the workbook itself. you know... this unpredictability makes the product completely useless. would be almost better to have a product that always crashes under certain conditions, rather than have one that crashes sometimes. I can't give it around to customers, under these conditions! thanks, regards mark "Peter T" wrote: There can be unpredictable problems writing to worksheet modules in the same workbook that contains the code that's writing the changes. This can lead to crashing Excel. It might depend on what changes are being made and possibly if the sheet that's being modified is also the active-sheet. Whatever the reason, the underlying cause is probably due to the project trying to recompile while code is running. Regards, Peter T "perf" wrote in message ... Hi, yes yes, I only write lines if the module is empty, otherwise I delete all lines and then I write them. your link was useful, and I made some change in the code, that (apparently without reason) allowed me to write in the sheet1 module if the module is originally empty. But I still have that problem if the module is not empty: after deleting the rows, as soon as I try to write in the module, there is the automation error. moreover, I am noticing that the SAVE command is not working properly with .xls. Sometimes it saves the fresh code in the module, sometimes not. randomly. this is sooooo unpredictable... not good at all : ( thanks "Barb Reinhardt" wrote: You may find something here. http://www.cpearson.com/excel/vbe.aspx I've not done anything like what you are doing, but I wonder if you already have some lines in the code module and are trying to insert lines where some already exist. Have you checked that? -- HTH, Barb Reinhardt "perf" wrote: Hi, I'm having a quite strange problem in a VBA macro that I made that is driving me crazy. First of all, I am using Excel 2003. Well, when I try to insert lines of code in vba worksheet "sheet1", I always get an Automation Error that causes the restart of excel. The precise error is: "Automation Error. connection to type library or object library for remote process has been lost. Press OK for dialog ot remove reference." The error always happens at the SECOND insertline instruction (the first line in sheet1 is written correctly) in the following code: ----...---- Set VBCodeModmej = ActiveWorkbook.VBProject.VBComponents(ActiveWorkbo ok.Worksheets("Sheet1").CodeName).CodeModule VBCodeModmej.InsertLines 1, "Private Sub Worksheet_SelectionChange(ByVal Target As Range) " VBCodeModmej.InsertLines 2, "Dim Touched As Range" VBCodeModmej.InsertLines 3, "Dim void As String" etc etc ---...--- Note that with Office 2007 this never happens ...! I tried the following things, read here and there for the network (I tried them one at a time ...): -Put option explicit: useless -Put on error resume next: useless -Put Instead of 'private sub' the string 'sub': useless -Put Application.EnableEvents = True: useless -Put Application.OnTime Now + TimeValue("00:00:10") before the Set line: useless Can you help me somehow? Thank you so much right now Mark . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
.insertlines automation error excel crash
If the code writing the changes is in a different project I don't have an
explanation for your problems, not the same as I had in mind. Post enough code to replicate the problem with details how you are call in the code. On which point, if you are calling the code in the xla from the workbook that the xla will change, that in effect as the same as the code being in the same project. So, give details. Regards, Peter T "perf" wrote in message ... yes, it can be as you say. do you think there can be a solution to this? for istance, disabling each type of operation when writing code? I do not know if you can do it, though. by the way, the code in the worksheet is written by a macro xla, so something 'outside' the workbook itself. you know... this unpredictability makes the product completely useless. would be almost better to have a product that always crashes under certain conditions, rather than have one that crashes sometimes. I can't give it around to customers, under these conditions! thanks, regards mark "Peter T" wrote: There can be unpredictable problems writing to worksheet modules in the same workbook that contains the code that's writing the changes. This can lead to crashing Excel. It might depend on what changes are being made and possibly if the sheet that's being modified is also the active-sheet. Whatever the reason, the underlying cause is probably due to the project trying to recompile while code is running. Regards, Peter T "perf" wrote in message ... Hi, yes yes, I only write lines if the module is empty, otherwise I delete all lines and then I write them. your link was useful, and I made some change in the code, that (apparently without reason) allowed me to write in the sheet1 module if the module is originally empty. But I still have that problem if the module is not empty: after deleting the rows, as soon as I try to write in the module, there is the automation error. moreover, I am noticing that the SAVE command is not working properly with .xls. Sometimes it saves the fresh code in the module, sometimes not. randomly. this is sooooo unpredictable... not good at all : ( thanks "Barb Reinhardt" wrote: You may find something here. http://www.cpearson.com/excel/vbe.aspx I've not done anything like what you are doing, but I wonder if you already have some lines in the code module and are trying to insert lines where some already exist. Have you checked that? -- HTH, Barb Reinhardt "perf" wrote: Hi, I'm having a quite strange problem in a VBA macro that I made that is driving me crazy. First of all, I am using Excel 2003. Well, when I try to insert lines of code in vba worksheet "sheet1", I always get an Automation Error that causes the restart of excel. The precise error is: "Automation Error. connection to type library or object library for remote process has been lost. Press OK for dialog ot remove reference." The error always happens at the SECOND insertline instruction (the first line in sheet1 is written correctly) in the following code: ----...---- Set VBCodeModmej = ActiveWorkbook.VBProject.VBComponents(ActiveWorkbo ok.Worksheets("Sheet1").CodeName).CodeModule VBCodeModmej.InsertLines 1, "Private Sub Worksheet_SelectionChange(ByVal Target As Range) " VBCodeModmej.InsertLines 2, "Dim Touched As Range" VBCodeModmej.InsertLines 3, "Dim void As String" etc etc ---...--- Note that with Office 2007 this never happens ...! I tried the following things, read here and there for the network (I tried them one at a time ...): -Put option explicit: useless -Put on error resume next: useless -Put Instead of 'private sub' the string 'sub': useless -Put Application.EnableEvents = True: useless -Put Application.OnTime Now + TimeValue("00:00:10") before the Set line: useless Can you help me somehow? Thank you so much right now Mark . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
.insertlines automation error excel crash
Hi,
New to the forum, so forgive me if I duplicate. I also had similar problems, where excel crashes when starting to build up text in a sheet. I found out the reason for it not working is that Excel screens the data you put in, and if not complete, it provides the error you would get when you were to manual enter in. So, starting with "Public sub xxxxxx ()" will cause an error, (missing end sub). (try "Public sub xxxxxx ():end sub" and she's okay, but that doesn't help us if we need code in the sub. What I do now is start building the text from the back, and always do an "InsertLines 1, text" That seems to do the trick. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
.insertlines automation error excel crash
That's a very interesting idea and makes perfect sense! It's also entirely
consistent with what I suggested in my first post here, about the project attempting to recompile while other code was running. Just one thing though, I would only expect the crash problem to occur when code is writing changes to "self", if also the activeworkbook/sheet also may make things even more prone for worse for some reason. Thanks for posting. Peter T "Rene" wrote in message ... Hi, New to the forum, so forgive me if I duplicate. I also had similar problems, where excel crashes when starting to build up text in a sheet. I found out the reason for it not working is that Excel screens the data you put in, and if not complete, it provides the error you would get when you were to manual enter in. So, starting with "Public sub xxxxxx ()" will cause an error, (missing end sub). (try "Public sub xxxxxx ():end sub" and she's okay, but that doesn't help us if we need code in the sub. What I do now is start building the text from the back, and always do an "InsertLines 1, text" That seems to do the trick. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
.insertlines automation error excel crash
oh thanks!!!!
this can help me a lot! so, do you suggest to do this? ..InsertLines 1, "end sub" ..InsertLines 1, "code line n" ..InsertLines 1, "code line n-1" .... ..InsertLines 1, "private sub ()" very clever. I'll try soon to do it and I let you know: ) mark "Peter T" wrote: That's a very interesting idea and makes perfect sense! It's also entirely consistent with what I suggested in my first post here, about the project attempting to recompile while other code was running. Just one thing though, I would only expect the crash problem to occur when code is writing changes to "self", if also the activeworkbook/sheet also may make things even more prone for worse for some reason. Thanks for posting. Peter T "Rene" wrote in message ... Hi, New to the forum, so forgive me if I duplicate. I also had similar problems, where excel crashes when starting to build up text in a sheet. I found out the reason for it not working is that Excel screens the data you put in, and if not complete, it provides the error you would get when you were to manual enter in. So, starting with "Public sub xxxxxx ()" will cause an error, (missing end sub). (try "Public sub xxxxxx ():end sub" and she's okay, but that doesn't help us if we need code in the sub. What I do now is start building the text from the back, and always do an "InsertLines 1, text" That seems to do the trick. . |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
.insertlines automation error excel crash
I haven't tested myself but try something like this -
Dim i as Long Dim LinesCount as Long Dim arrLines() as String Redim arrLines(1 to LinesCount) ' populate the string array with lines of code to be inserted in normal order arrLines(1) = "Public sub xxxxxx ()" '' etc arrLines(LinesCount) = "End Sub" for i = ubound(arrLines) to 1 step -1 ..InsertLines 1, arrLines(i) next ' hold your breath! Regards, Peter T "perf" wrote in message ... oh thanks!!!! this can help me a lot! so, do you suggest to do this? .InsertLines 1, "end sub" .InsertLines 1, "code line n" .InsertLines 1, "code line n-1" ... .InsertLines 1, "private sub ()" very clever. I'll try soon to do it and I let you know: ) mark "Peter T" wrote: That's a very interesting idea and makes perfect sense! It's also entirely consistent with what I suggested in my first post here, about the project attempting to recompile while other code was running. Just one thing though, I would only expect the crash problem to occur when code is writing changes to "self", if also the activeworkbook/sheet also may make things even more prone for worse for some reason. Thanks for posting. Peter T "Rene" wrote in message ... Hi, New to the forum, so forgive me if I duplicate. I also had similar problems, where excel crashes when starting to build up text in a sheet. I found out the reason for it not working is that Excel screens the data you put in, and if not complete, it provides the error you would get when you were to manual enter in. So, starting with "Public sub xxxxxx ()" will cause an error, (missing end sub). (try "Public sub xxxxxx ():end sub" and she's okay, but that doesn't help us if we need code in the sub. What I do now is start building the text from the back, and always do an "InsertLines 1, text" That seems to do the trick. . |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
.insertlines automation error excel crash
Hi peter!
I held my breath.. but it was not enough! I can write all the code lines but the first. everytime I reach the LinesCount to write the line "sub ZZZ()" I obtain an automation error, like before. I this doesn't happen always, expecially it's ok when I do it in debug mode, proceeding line by line writing the code. what a pity, this solution was very very clever... thanks anyway for your support! mark "Peter T" wrote: I haven't tested myself but try something like this - Dim i as Long Dim LinesCount as Long Dim arrLines() as String Redim arrLines(1 to LinesCount) ' populate the string array with lines of code to be inserted in normal order arrLines(1) = "Public sub xxxxxx ()" '' etc arrLines(LinesCount) = "End Sub" for i = ubound(arrLines) to 1 step -1 ..InsertLines 1, arrLines(i) next ' hold your breath! Regards, Peter T "perf" wrote in message ... oh thanks!!!! this can help me a lot! so, do you suggest to do this? .InsertLines 1, "end sub" .InsertLines 1, "code line n" .InsertLines 1, "code line n-1" ... .InsertLines 1, "private sub ()" very clever. I'll try soon to do it and I let you know: ) mark "Peter T" wrote: That's a very interesting idea and makes perfect sense! It's also entirely consistent with what I suggested in my first post here, about the project attempting to recompile while other code was running. Just one thing though, I would only expect the crash problem to occur when code is writing changes to "self", if also the activeworkbook/sheet also may make things even more prone for worse for some reason. Thanks for posting. Peter T "Rene" wrote in message ... Hi, New to the forum, so forgive me if I duplicate. I also had similar problems, where excel crashes when starting to build up text in a sheet. I found out the reason for it not working is that Excel screens the data you put in, and if not complete, it provides the error you would get when you were to manual enter in. So, starting with "Public sub xxxxxx ()" will cause an error, (missing end sub). (try "Public sub xxxxxx ():end sub" and she's okay, but that doesn't help us if we need code in the sub. What I do now is start building the text from the back, and always do an "InsertLines 1, text" That seems to do the trick. . . |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
.insertlines automation error excel crash
Explain clearly, is there any connection between the code workbook (the one
that's writing the changes) and the workbook that's having its project modified, that includes the "modified workbook" calling code in some other workbook. If not, is there any problem to modify a brand new workbook. Is there something else going on that we are not aware of. If nothing above is of relevance post the ENTIRE code. Regards, Peter T "perf" wrote in message ... Hi peter! I held my breath.. but it was not enough! I can write all the code lines but the first. everytime I reach the LinesCount to write the line "sub ZZZ()" I obtain an automation error, like before. I this doesn't happen always, expecially it's ok when I do it in debug mode, proceeding line by line writing the code. what a pity, this solution was very very clever... thanks anyway for your support! mark "Peter T" wrote: I haven't tested myself but try something like this - Dim i as Long Dim LinesCount as Long Dim arrLines() as String Redim arrLines(1 to LinesCount) ' populate the string array with lines of code to be inserted in normal order arrLines(1) = "Public sub xxxxxx ()" '' etc arrLines(LinesCount) = "End Sub" for i = ubound(arrLines) to 1 step -1 ..InsertLines 1, arrLines(i) next ' hold your breath! Regards, Peter T "perf" wrote in message ... oh thanks!!!! this can help me a lot! so, do you suggest to do this? .InsertLines 1, "end sub" .InsertLines 1, "code line n" .InsertLines 1, "code line n-1" ... .InsertLines 1, "private sub ()" very clever. I'll try soon to do it and I let you know: ) mark "Peter T" wrote: That's a very interesting idea and makes perfect sense! It's also entirely consistent with what I suggested in my first post here, about the project attempting to recompile while other code was running. Just one thing though, I would only expect the crash problem to occur when code is writing changes to "self", if also the activeworkbook/sheet also may make things even more prone for worse for some reason. Thanks for posting. Peter T "Rene" wrote in message ... Hi, New to the forum, so forgive me if I duplicate. I also had similar problems, where excel crashes when starting to build up text in a sheet. I found out the reason for it not working is that Excel screens the data you put in, and if not complete, it provides the error you would get when you were to manual enter in. So, starting with "Public sub xxxxxx ()" will cause an error, (missing end sub). (try "Public sub xxxxxx ():end sub" and she's okay, but that doesn't help us if we need code in the sub. What I do now is start building the text from the back, and always do an "InsertLines 1, text" That seems to do the trick. . . |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
.insertlines automation error excel crash
Dear Peter,
Maybe I just solved the problem with a trivial solution. I noticed that deleting the possible code present in sheet1, then save the file, close it, reopen it and at that stage write the new code from down to up seems to work well. at least so far. I do not why, but the combination of saving, closing and writing in a reverse way it's good. answering to your question, posting all code would require too much time, because the xla module are many. I can say that a connection between the xla and the file is there, because the xla opens a dialog menu that searches in a database and allows to open from the menu the selected file. and then it modifies the code... it sounds a little complicated, I know... but now it works, this is the most important thing! thank to you all mark : ) "Peter T" wrote: Explain clearly, is there any connection between the code workbook (the one that's writing the changes) and the workbook that's having its project modified, that includes the "modified workbook" calling code in some other workbook. If not, is there any problem to modify a brand new workbook. Is there something else going on that we are not aware of. If nothing above is of relevance post the ENTIRE code. Regards, Peter T "perf" wrote in message ... Hi peter! I held my breath.. but it was not enough! I can write all the code lines but the first. everytime I reach the LinesCount to write the line "sub ZZZ()" I obtain an automation error, like before. I this doesn't happen always, expecially it's ok when I do it in debug mode, proceeding line by line writing the code. what a pity, this solution was very very clever... thanks anyway for your support! mark "Peter T" wrote: I haven't tested myself but try something like this - Dim i as Long Dim LinesCount as Long Dim arrLines() as String Redim arrLines(1 to LinesCount) ' populate the string array with lines of code to be inserted in normal order arrLines(1) = "Public sub xxxxxx ()" '' etc arrLines(LinesCount) = "End Sub" for i = ubound(arrLines) to 1 step -1 ..InsertLines 1, arrLines(i) next ' hold your breath! Regards, Peter T "perf" wrote in message ... oh thanks!!!! this can help me a lot! so, do you suggest to do this? .InsertLines 1, "end sub" .InsertLines 1, "code line n" .InsertLines 1, "code line n-1" ... .InsertLines 1, "private sub ()" very clever. I'll try soon to do it and I let you know: ) mark "Peter T" wrote: That's a very interesting idea and makes perfect sense! It's also entirely consistent with what I suggested in my first post here, about the project attempting to recompile while other code was running. Just one thing though, I would only expect the crash problem to occur when code is writing changes to "self", if also the activeworkbook/sheet also may make things even more prone for worse for some reason. Thanks for posting. Peter T "Rene" wrote in message ... Hi, New to the forum, so forgive me if I duplicate. I also had similar problems, where excel crashes when starting to build up text in a sheet. I found out the reason for it not working is that Excel screens the data you put in, and if not complete, it provides the error you would get when you were to manual enter in. So, starting with "Public sub xxxxxx ()" will cause an error, (missing end sub). (try "Public sub xxxxxx ():end sub" and she's okay, but that doesn't help us if we need code in the sub. What I do now is start building the text from the back, and always do an "InsertLines 1, text" That seems to do the trick. . . . |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
.insertlines automation error excel crash
I don't understand what you are saying here
I can say that a connection between the xla and the file is there, because the xla opens a dialog menu that searches in a database and allows to open from the menu the selected file. Are you saying code in the file that will be modified calls the xla which in turn modifies the code. If so that is the reason for the problem (as I have been trying to say all along), and there is probably a better solution. Regards, Peter T "perf" wrote in message ... Dear Peter, Maybe I just solved the problem with a trivial solution. I noticed that deleting the possible code present in sheet1, then save the file, close it, reopen it and at that stage write the new code from down to up seems to work well. at least so far. I do not why, but the combination of saving, closing and writing in a reverse way it's good. answering to your question, posting all code would require too much time, because the xla module are many. I can say that a connection between the xla and the file is there, because the xla opens a dialog menu that searches in a database and allows to open from the menu the selected file. and then it modifies the code... it sounds a little complicated, I know... but now it works, this is the most important thing! thank to you all mark : ) "Peter T" wrote: Explain clearly, is there any connection between the code workbook (the one that's writing the changes) and the workbook that's having its project modified, that includes the "modified workbook" calling code in some other workbook. If not, is there any problem to modify a brand new workbook. Is there something else going on that we are not aware of. If nothing above is of relevance post the ENTIRE code. Regards, Peter T "perf" wrote in message ... Hi peter! I held my breath.. but it was not enough! I can write all the code lines but the first. everytime I reach the LinesCount to write the line "sub ZZZ()" I obtain an automation error, like before. I this doesn't happen always, expecially it's ok when I do it in debug mode, proceeding line by line writing the code. what a pity, this solution was very very clever... thanks anyway for your support! mark "Peter T" wrote: I haven't tested myself but try something like this - Dim i as Long Dim LinesCount as Long Dim arrLines() as String Redim arrLines(1 to LinesCount) ' populate the string array with lines of code to be inserted in normal order arrLines(1) = "Public sub xxxxxx ()" '' etc arrLines(LinesCount) = "End Sub" for i = ubound(arrLines) to 1 step -1 ..InsertLines 1, arrLines(i) next ' hold your breath! Regards, Peter T "perf" wrote in message ... oh thanks!!!! this can help me a lot! so, do you suggest to do this? .InsertLines 1, "end sub" .InsertLines 1, "code line n" .InsertLines 1, "code line n-1" ... .InsertLines 1, "private sub ()" very clever. I'll try soon to do it and I let you know: ) mark "Peter T" wrote: That's a very interesting idea and makes perfect sense! It's also entirely consistent with what I suggested in my first post here, about the project attempting to recompile while other code was running. Just one thing though, I would only expect the crash problem to occur when code is writing changes to "self", if also the activeworkbook/sheet also may make things even more prone for worse for some reason. Thanks for posting. Peter T "Rene" wrote in message ... Hi, New to the forum, so forgive me if I duplicate. I also had similar problems, where excel crashes when starting to build up text in a sheet. I found out the reason for it not working is that Excel screens the data you put in, and if not complete, it provides the error you would get when you were to manual enter in. So, starting with "Public sub xxxxxx ()" will cause an error, (missing end sub). (try "Public sub xxxxxx ():end sub" and she's okay, but that doesn't help us if we need code in the sub. What I do now is start building the text from the back, and always do an "InsertLines 1, text" That seems to do the trick. . . . |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
.insertlines automation error excel crash
Not exactly... better explanation:
I load an xla that manage excel file in some directories. the xla opens a toolbar with some blocks to draw in these file and shows a menu where I can create a new sheet (so, a new file), delete one of them ect ect. all these file are listed in an access database, and everytime the xla shows in the menu the files present it reads in the access database. all these files have code in the sheet1 module, so when I create a new one, the xla creates the code in the new sheet1 module. but sometimes I have to modify the code in the sheet, so I open the menu via xla and I delete the code and I rewrite it. this last modification is what causes the automation error. so, everything is managed by the xla, so it's not true that there is no link between xla and excel file. but it's not true that the code added in the sheet1 recall the xla or something like this. I hope the structure is a little clearer for you now. : ) it's not very easy to explain, i know. thanks for your patience mark "Peter T" wrote: I don't understand what you are saying here I can say that a connection between the xla and the file is there, because the xla opens a dialog menu that searches in a database and allows to open from the menu the selected file. Are you saying code in the file that will be modified calls the xla which in turn modifies the code. If so that is the reason for the problem (as I have been trying to say all along), and there is probably a better solution. Regards, Peter T "perf" wrote in message ... Dear Peter, Maybe I just solved the problem with a trivial solution. I noticed that deleting the possible code present in sheet1, then save the file, close it, reopen it and at that stage write the new code from down to up seems to work well. at least so far. I do not why, but the combination of saving, closing and writing in a reverse way it's good. answering to your question, posting all code would require too much time, because the xla module are many. I can say that a connection between the xla and the file is there, because the xla opens a dialog menu that searches in a database and allows to open from the menu the selected file. and then it modifies the code... it sounds a little complicated, I know... but now it works, this is the most important thing! thank to you all mark : ) "Peter T" wrote: Explain clearly, is there any connection between the code workbook (the one that's writing the changes) and the workbook that's having its project modified, that includes the "modified workbook" calling code in some other workbook. If not, is there any problem to modify a brand new workbook. Is there something else going on that we are not aware of. If nothing above is of relevance post the ENTIRE code. Regards, Peter T "perf" wrote in message ... Hi peter! I held my breath.. but it was not enough! I can write all the code lines but the first. everytime I reach the LinesCount to write the line "sub ZZZ()" I obtain an automation error, like before. I this doesn't happen always, expecially it's ok when I do it in debug mode, proceeding line by line writing the code. what a pity, this solution was very very clever... thanks anyway for your support! mark "Peter T" wrote: I haven't tested myself but try something like this - Dim i as Long Dim LinesCount as Long Dim arrLines() as String Redim arrLines(1 to LinesCount) ' populate the string array with lines of code to be inserted in normal order arrLines(1) = "Public sub xxxxxx ()" '' etc arrLines(LinesCount) = "End Sub" for i = ubound(arrLines) to 1 step -1 ..InsertLines 1, arrLines(i) next ' hold your breath! Regards, Peter T "perf" wrote in message ... oh thanks!!!! this can help me a lot! so, do you suggest to do this? .InsertLines 1, "end sub" .InsertLines 1, "code line n" .InsertLines 1, "code line n-1" ... .InsertLines 1, "private sub ()" very clever. I'll try soon to do it and I let you know: ) mark "Peter T" wrote: That's a very interesting idea and makes perfect sense! It's also entirely consistent with what I suggested in my first post here, about the project attempting to recompile while other code was running. Just one thing though, I would only expect the crash problem to occur when code is writing changes to "self", if also the activeworkbook/sheet also may make things even more prone for worse for some reason. Thanks for posting. Peter T "Rene" wrote in message ... Hi, New to the forum, so forgive me if I duplicate. I also had similar problems, where excel crashes when starting to build up text in a sheet. I found out the reason for it not working is that Excel screens the data you put in, and if not complete, it provides the error you would get when you were to manual enter in. So, starting with "Public sub xxxxxx ()" will cause an error, (missing end sub). (try "Public sub xxxxxx ():end sub" and she's okay, but that doesn't help us if we need code in the sub. What I do now is start building the text from the back, and always do an "InsertLines 1, text" That seems to do the trick. . . . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel COM Automation crash | Excel Programming | |||
Office SP2 update causes Excel automation crash! | Excel Programming | |||
InsertLines and AddFromString crash excel | Excel Programming | |||
Excel crash error code | Excel Programming | |||
VBA Compiler Error Cause Excel to Crash | Excel Programming |