Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.word.vba.general
|
|||
|
|||
Word crashed after repeated XL macro use
I have an Excel macro that call a new instance of Word and creates a new
document, does some things in the doc, then closes the doc without saving changes and sets the doc and Word app objects to nothing using doc.Close SaveChanges:=wdDoNotSaveChanges appWD.Quit Set doc = Nothing Set appWD = Nothing I used the macro this morning repeatedly - perhaps 30 times in succession. (Each time had to be an individual call - there was no way to loop this.) After doing all that, I did not check to see if there were any open instances of Word left (there were none during testing, so I didn't think I should have to) or other issues. Then I tried to open a Word document with an AutoOpen macro, and Word crashed. Subsequent attempts produced further crashes. I wound up eventually having to rebuild Normal to get things moving again. Question: Could just repeatedly accessing the Word app have caused this? Is there a better way to avoid these issues in the future? Ed |
#2
Posted to microsoft.public.excel.programming,microsoft.public.word.vba.general
|
|||
|
|||
Word crashed after repeated XL macro use
Ed,
There are a couple of things that could be confusing to the applications... How would Word or Excel read: appWD.doc.Close...? Is appWD the document or is doc the document? Maybe doc is not the best choice for a Word object variable. Since wdDoNotSaveChanges is not an Excel constant and you are running the program from Excel, you should qualify the constant... appWD.wdDoNotSaveChanges -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "Ed" wrote in message ... I have an Excel macro that call a new instance of Word and creates a new document, does some things in the doc, then closes the doc without saving changes and sets the doc and Word app objects to nothing using doc.Close SaveChanges:=wdDoNotSaveChanges appWD.Quit Set doc = Nothing Set appWD = Nothing I used the macro this morning repeatedly - perhaps 30 times in succession. (Each time had to be an individual call - there was no way to loop this.) After doing all that, I did not check to see if there were any open instances of Word left (there were none during testing, so I didn't think I should have to) or other issues. Then I tried to open a Word document with an AutoOpen macro, and Word crashed. Subsequent attempts produced further crashes. I wound up eventually having to rebuild Normal to get things moving again. Question: Could just repeatedly accessing the Word app have caused this? Is there a better way to avoid these issues in the future? Ed |
#3
Posted to microsoft.public.excel.programming,microsoft.public.word.vba.general
|
|||
|
|||
Word crashed after repeated XL macro use
Thanks for the response, Jim, and sorry for the confusion. The objects are
declared with: Dim appWD As New Word.Application Dim doc As Word.Document and set with: Set appWD = CreateObject("Word.Application") appWD.Visible = False Set doc = appWD.Documents.Add My assumption was that Excel VBA would thereafter recognize "doc" as having Word properties and methods, so doc.Close SaveChanges:=wdDoNotSaveChanges should be recognized. Is this an inaccurate assumption? Ed "Jim Cone" wrote in message ... Ed, There are a couple of things that could be confusing to the applications... How would Word or Excel read: appWD.doc.Close...? Is appWD the document or is doc the document? Maybe doc is not the best choice for a Word object variable. Since wdDoNotSaveChanges is not an Excel constant and you are running the program from Excel, you should qualify the constant... appWD.wdDoNotSaveChanges -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "Ed" wrote in message ... I have an Excel macro that call a new instance of Word and creates a new document, does some things in the doc, then closes the doc without saving changes and sets the doc and Word app objects to nothing using doc.Close SaveChanges:=wdDoNotSaveChanges appWD.Quit Set doc = Nothing Set appWD = Nothing I used the macro this morning repeatedly - perhaps 30 times in succession. (Each time had to be an individual call - there was no way to loop this.) After doing all that, I did not check to see if there were any open instances of Word left (there were none during testing, so I didn't think I should have to) or other issues. Then I tried to open a Word document with an AutoOpen macro, and Word crashed. Subsequent attempts produced further crashes. I wound up eventually having to rebuild Normal to get things moving again. Question: Could just repeatedly accessing the Word app have caused this? Is there a better way to avoid these issues in the future? Ed |
#4
Posted to microsoft.public.excel.programming,microsoft.public.word.vba.general
|
|||
|
|||
Word crashed after repeated XL macro use
Ed,
"so doc.Close SaveChanges:=wdDoNotSaveChanges should be recognized. Is this an inaccurate assumption?" Yes, however you may have lucked out. Since the constant's value in Word is 0 (zero), that may the value being assigned when Excel can't read it. However, Excel should be throwing an error. Unless... there is an "on error resume next" someplace. -- Jim Cone San Francisco, USA http://www.officeletter.com/blink/specialsort.html "Ed" wrote in message ... Thanks for the response, Jim, and sorry for the confusion. The objects are declared with: Dim appWD As New Word.Application Dim doc As Word.Document and set with: Set appWD = CreateObject("Word.Application") appWD.Visible = False Set doc = appWD.Documents.Add My assumption was that Excel VBA would thereafter recognize "doc" as having Word properties and methods, so doc.Close SaveChanges:=wdDoNotSaveChanges should be recognized. Is this an inaccurate assumption? Ed "Jim Cone" wrote in message ... Ed, There are a couple of things that could be confusing to the applications... How would Word or Excel read: appWD.doc.Close...? Is appWD the document or is doc the document? Maybe doc is not the best choice for a Word object variable. Since wdDoNotSaveChanges is not an Excel constant and you are running the program from Excel, you should qualify the constant... appWD.wdDoNotSaveChanges -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "Ed" wrote in message ... I have an Excel macro that call a new instance of Word and creates a new document, does some things in the doc, then closes the doc without saving changes and sets the doc and Word app objects to nothing using doc.Close SaveChanges:=wdDoNotSaveChanges appWD.Quit Set doc = Nothing Set appWD = Nothing I used the macro this morning repeatedly - perhaps 30 times in succession. (Each time had to be an individual call - there was no way to loop this.) After doing all that, I did not check to see if there were any open instances of Word left (there were none during testing, so I didn't think I should have to) or other issues. Then I tried to open a Word document with an AutoOpen macro, and Word crashed. Subsequent attempts produced further crashes. I wound up eventually having to rebuild Normal to get things moving again. Question: Could just repeatedly accessing the Word app have caused this? Is there a better way to avoid these issues in the future? Ed |
#5
Posted to microsoft.public.excel.programming,microsoft.public.word.vba.general
|
|||
|
|||
Word crashed after repeated XL macro use
Unless... there is an "on error resume next" someplace.
Wow, Jim! You are _good_!! As a matter of fact, the whole end of the macro is CleanUp: On Error Resume Next doc.Close SaveChanges:=wdDoNotSaveChanges appWD.Quit Set doc = Nothing Set appWD = Nothing On Error GoTo 0 End Sub The intention was that if an error is thrown early into the macro, like after setting the Word app object but not the document object, I could drop here to close out any object still remaining without causing more errors because I'm trying to manipulate an object that doesn't exist. Perhaps better would be to explicitly label the doc variable as belonging to the Word application when I close? CleanUp: On Error Resume Next appWD.doc.Close SaveChanges:=wdDoNotSaveChanges appWD.Quit Set doc = Nothing Set appWD = Nothing On Error GoTo 0 End Sub "Jim Cone" wrote in message ... Ed, "so doc.Close SaveChanges:=wdDoNotSaveChanges should be recognized. Is this an inaccurate assumption?" Yes, however you may have lucked out. Since the constant's value in Word is 0 (zero), that may the value being assigned when Excel can't read it. However, Excel should be throwing an error. Unless... there is an "on error resume next" someplace. -- Jim Cone San Francisco, USA http://www.officeletter.com/blink/specialsort.html "Ed" wrote in message ... Thanks for the response, Jim, and sorry for the confusion. The objects are declared with: Dim appWD As New Word.Application Dim doc As Word.Document and set with: Set appWD = CreateObject("Word.Application") appWD.Visible = False Set doc = appWD.Documents.Add My assumption was that Excel VBA would thereafter recognize "doc" as having Word properties and methods, so doc.Close SaveChanges:=wdDoNotSaveChanges should be recognized. Is this an inaccurate assumption? Ed "Jim Cone" wrote in message ... Ed, There are a couple of things that could be confusing to the applications... How would Word or Excel read: appWD.doc.Close...? Is appWD the document or is doc the document? Maybe doc is not the best choice for a Word object variable. Since wdDoNotSaveChanges is not an Excel constant and you are running the program from Excel, you should qualify the constant... appWD.wdDoNotSaveChanges -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "Ed" wrote in message ... I have an Excel macro that call a new instance of Word and creates a new document, does some things in the doc, then closes the doc without saving changes and sets the doc and Word app objects to nothing using doc.Close SaveChanges:=wdDoNotSaveChanges appWD.Quit Set doc = Nothing Set appWD = Nothing I used the macro this morning repeatedly - perhaps 30 times in succession. (Each time had to be an individual call - there was no way to loop this.) After doing all that, I did not check to see if there were any open instances of Word left (there were none during testing, so I didn't think I should have to) or other issues. Then I tried to open a Word document with an AutoOpen macro, and Word crashed. Subsequent attempts produced further crashes. I wound up eventually having to rebuild Normal to get things moving again. Question: Could just repeatedly accessing the Word app have caused this? Is there a better way to avoid these issues in the future? Ed |
#6
Posted to microsoft.public.excel.programming,microsoft.public.word.vba.general
|
|||
|
|||
Word crashed after repeated XL macro use
Ed,
"appWD.doc.Close SaveChanges:=wdDoNotSaveChanges" No, don't do that, you are right back to problem I pointed out in my first message. The simplest way is to just use the constant's value... doc.Close SaveChanges:=0 or the more self-explanatory way... doc.Close SaveChanges:=AppWD.wdDoNotSaveChanges Of course, all of this may not mean much if your app still crashes. Regards, Jim Cone "Ed" wrote in message ... Unless... there is an "on error resume next" someplace. Wow, Jim! You are _good_!! As a matter of fact, the whole end of the macro is CleanUp: On Error Resume Next doc.Close SaveChanges:=wdDoNotSaveChanges appWD.Quit Set doc = Nothing Set appWD = Nothing On Error GoTo 0 End Sub The intention was that if an error is thrown early into the macro, like after setting the Word app object but not the document object, I could drop here to close out any object still remaining without causing more errors because I'm trying to manipulate an object that doesn't exist. Perhaps better would be to explicitly label the doc variable as belonging to the Word application when I close? CleanUp: On Error Resume Next appWD.doc.Close SaveChanges:=wdDoNotSaveChanges appWD.Quit Set doc = Nothing Set appWD = Nothing On Error GoTo 0 End Sub "Jim Cone" wrote in message ... Ed, "so doc.Close SaveChanges:=wdDoNotSaveChanges should be recognized. Is this an inaccurate assumption?" Yes, however you may have lucked out. Since the constant's value in Word is 0 (zero), that may the value being assigned when Excel can't read it. However, Excel should be throwing an error. Unless... there is an "on error resume next" someplace. -- Jim Cone San Francisco, USA http://www.officeletter.com/blink/specialsort.html "Ed" wrote in message ... Thanks for the response, Jim, and sorry for the confusion. The objects are declared with: Dim appWD As New Word.Application Dim doc As Word.Document and set with: Set appWD = CreateObject("Word.Application") appWD.Visible = False Set doc = appWD.Documents.Add My assumption was that Excel VBA would thereafter recognize "doc" as having Word properties and methods, so doc.Close SaveChanges:=wdDoNotSaveChanges should be recognized. Is this an inaccurate assumption? Ed "Jim Cone" wrote in message ... Ed, There are a couple of things that could be confusing to the applications... How would Word or Excel read: appWD.doc.Close...? Is appWD the document or is doc the document? Maybe doc is not the best choice for a Word object variable. Since wdDoNotSaveChanges is not an Excel constant and you are running the program from Excel, you should qualify the constant... appWD.wdDoNotSaveChanges -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "Ed" wrote in message ... I have an Excel macro that call a new instance of Word and creates a new document, does some things in the doc, then closes the doc without saving changes and sets the doc and Word app objects to nothing using doc.Close SaveChanges:=wdDoNotSaveChanges appWD.Quit Set doc = Nothing Set appWD = Nothing I used the macro this morning repeatedly - perhaps 30 times in succession. (Each time had to be an individual call - there was no way to loop this.) After doing all that, I did not check to see if there were any open instances of Word left (there were none during testing, so I didn't think I should have to) or other issues. Then I tried to open a Word document with an AutoOpen macro, and Word crashed. Subsequent attempts produced further crashes. I wound up eventually having to rebuild Normal to get things moving again. Question: Could just repeatedly accessing the Word app have caused this? Is there a better way to avoid these issues in the future? Ed |
#7
Posted to microsoft.public.excel.programming,microsoft.public.word.vba.general
|
|||
|
|||
Word crashed after repeated XL macro use
Not sure that this is entirely the cause of the problem, but your object
declaration and instantation are screwy. You're using early binding for the declaration itself, but using late binding for the instantiation; and using 'as new' in the declaration is a recipe for disaster. (Some coding shops have an absolute ban on the use of 'as new'.) As it is, this statement Set appWD = CreateObject("Word.Application") will instantiate TWO copies of Word: one created automatically for the first use of appWD, and one from the CreateObject statement. Try --- Dim appWD as Word.Application on error resume next set appWD = Word.Application 'Get existing instance if any on error goto ErrorHandler if appWD is nothing then 'No existing instance, so create a new one set appWD = new Word.Application end if Two other issues that might be relevant -- 1) Word may refuse to close if there were unhandled errors in the course of your code. 2) There may be other code, from normal.dot or an add-in, that is interfering. After you've run your macro, check the Task Manager to see if there are any remaining instances of Word. "Ed" wrote in message ... Thanks for the response, Jim, and sorry for the confusion. The objects are declared with: Dim appWD As New Word.Application Dim doc As Word.Document and set with: Set appWD = CreateObject("Word.Application") appWD.Visible = False Set doc = appWD.Documents.Add My assumption was that Excel VBA would thereafter recognize "doc" as having Word properties and methods, so doc.Close SaveChanges:=wdDoNotSaveChanges should be recognized. Is this an inaccurate assumption? Ed "Jim Cone" wrote in message ... Ed, There are a couple of things that could be confusing to the applications... How would Word or Excel read: appWD.doc.Close...? Is appWD the document or is doc the document? Maybe doc is not the best choice for a Word object variable. Since wdDoNotSaveChanges is not an Excel constant and you are running the program from Excel, you should qualify the constant... appWD.wdDoNotSaveChanges -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "Ed" wrote in message ... I have an Excel macro that call a new instance of Word and creates a new document, does some things in the doc, then closes the doc without saving changes and sets the doc and Word app objects to nothing using doc.Close SaveChanges:=wdDoNotSaveChanges appWD.Quit Set doc = Nothing Set appWD = Nothing I used the macro this morning repeatedly - perhaps 30 times in succession. (Each time had to be an individual call - there was no way to loop this.) After doing all that, I did not check to see if there were any open instances of Word left (there were none during testing, so I didn't think I should have to) or other issues. Then I tried to open a Word document with an AutoOpen macro, and Word crashed. Subsequent attempts produced further crashes. I wound up eventually having to rebuild Normal to get things moving again. Question: Could just repeatedly accessing the Word app have caused this? Is there a better way to avoid these issues in the future? Ed |
#8
Posted to microsoft.public.excel.programming,microsoft.public.word.vba.general
|
|||
|
|||
Word crashed after repeated XL macro use
Nice catch.
Jim Cone San Francisco, USA "Jezebel" wrote in message ... Not sure that this is entirely the cause of the problem, but your object declaration and instantation are screwy. You're using early binding for the declaration itself, but using late binding for the instantiation; and using 'as new' in the declaration is a recipe for disaster. (Some coding shops have an absolute ban on the use of 'as new'.) As it is, this statement Set appWD = CreateObject("Word.Application") will instantiate TWO copies of Word: one created automatically for the first use of appWD, and one from the CreateObject statement. Try --- Dim appWD as Word.Application on error resume next set appWD = Word.Application 'Get existing instance if any on error goto ErrorHandler if appWD is nothing then 'No existing instance, so create a new one set appWD = new Word.Application end if Two other issues that might be relevant -- 1) Word may refuse to close if there were unhandled errors in the course of your code. 2) There may be other code, from normal.dot or an add-in, that is interfering. After you've run your macro, check the Task Manager to see if there are any remaining instances of Word. |
#9
Posted to microsoft.public.excel.programming,microsoft.public.word.vba.general
|
|||
|
|||
Word crashed after repeated XL macro use
Wow! Thanks, Jezebel. I owe you big for that one! I guess I need to go
back and brush up on some things - and see what other bad habits I've let get in here. Ed "Jezebel" wrote in message ... Not sure that this is entirely the cause of the problem, but your object declaration and instantation are screwy. You're using early binding for the declaration itself, but using late binding for the instantiation; and using 'as new' in the declaration is a recipe for disaster. (Some coding shops have an absolute ban on the use of 'as new'.) As it is, this statement Set appWD = CreateObject("Word.Application") will instantiate TWO copies of Word: one created automatically for the first use of appWD, and one from the CreateObject statement. Try --- Dim appWD as Word.Application on error resume next set appWD = Word.Application 'Get existing instance if any on error goto ErrorHandler if appWD is nothing then 'No existing instance, so create a new one set appWD = new Word.Application end if Two other issues that might be relevant -- 1) Word may refuse to close if there were unhandled errors in the course of your code. 2) There may be other code, from normal.dot or an add-in, that is interfering. After you've run your macro, check the Task Manager to see if there are any remaining instances of Word. "Ed" wrote in message ... Thanks for the response, Jim, and sorry for the confusion. The objects are declared with: Dim appWD As New Word.Application Dim doc As Word.Document and set with: Set appWD = CreateObject("Word.Application") appWD.Visible = False Set doc = appWD.Documents.Add My assumption was that Excel VBA would thereafter recognize "doc" as having Word properties and methods, so doc.Close SaveChanges:=wdDoNotSaveChanges should be recognized. Is this an inaccurate assumption? Ed "Jim Cone" wrote in message ... Ed, There are a couple of things that could be confusing to the applications... How would Word or Excel read: appWD.doc.Close...? Is appWD the document or is doc the document? Maybe doc is not the best choice for a Word object variable. Since wdDoNotSaveChanges is not an Excel constant and you are running the program from Excel, you should qualify the constant... appWD.wdDoNotSaveChanges -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "Ed" wrote in message ... I have an Excel macro that call a new instance of Word and creates a new document, does some things in the doc, then closes the doc without saving changes and sets the doc and Word app objects to nothing using doc.Close SaveChanges:=wdDoNotSaveChanges appWD.Quit Set doc = Nothing Set appWD = Nothing I used the macro this morning repeatedly - perhaps 30 times in succession. (Each time had to be an individual call - there was no way to loop this.) After doing all that, I did not check to see if there were any open instances of Word left (there were none during testing, so I didn't think I should have to) or other issues. Then I tried to open a Word document with an AutoOpen macro, and Word crashed. Subsequent attempts produced further crashes. I wound up eventually having to rebuild Normal to get things moving again. Question: Could just repeatedly accessing the Word app have caused this? Is there a better way to avoid these issues in the future? Ed |
#10
Posted to microsoft.public.excel.programming,microsoft.public.word.vba.general
|
|||
|
|||
Word crashed after repeated XL macro use
Jezebel and Jim:
Just ran the Excel macro again, about 25 times through. No leftovers this time! Then I launched Word and macros run fine there. Thank you both for your help with this. Ed "Ed" wrote in message ... Wow! Thanks, Jezebel. I owe you big for that one! I guess I need to go back and brush up on some things - and see what other bad habits I've let get in here. Ed "Jezebel" wrote in message ... Not sure that this is entirely the cause of the problem, but your object declaration and instantation are screwy. You're using early binding for the declaration itself, but using late binding for the instantiation; and using 'as new' in the declaration is a recipe for disaster. (Some coding shops have an absolute ban on the use of 'as new'.) As it is, this statement Set appWD = CreateObject("Word.Application") will instantiate TWO copies of Word: one created automatically for the first use of appWD, and one from the CreateObject statement. Try --- Dim appWD as Word.Application on error resume next set appWD = Word.Application 'Get existing instance if any on error goto ErrorHandler if appWD is nothing then 'No existing instance, so create a new one set appWD = new Word.Application end if Two other issues that might be relevant -- 1) Word may refuse to close if there were unhandled errors in the course of your code. 2) There may be other code, from normal.dot or an add-in, that is interfering. After you've run your macro, check the Task Manager to see if there are any remaining instances of Word. "Ed" wrote in message ... Thanks for the response, Jim, and sorry for the confusion. The objects are declared with: Dim appWD As New Word.Application Dim doc As Word.Document and set with: Set appWD = CreateObject("Word.Application") appWD.Visible = False Set doc = appWD.Documents.Add My assumption was that Excel VBA would thereafter recognize "doc" as having Word properties and methods, so doc.Close SaveChanges:=wdDoNotSaveChanges should be recognized. Is this an inaccurate assumption? Ed "Jim Cone" wrote in message ... Ed, There are a couple of things that could be confusing to the applications... How would Word or Excel read: appWD.doc.Close...? Is appWD the document or is doc the document? Maybe doc is not the best choice for a Word object variable. Since wdDoNotSaveChanges is not an Excel constant and you are running the program from Excel, you should qualify the constant... appWD.wdDoNotSaveChanges -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "Ed" wrote in message ... I have an Excel macro that call a new instance of Word and creates a new document, does some things in the doc, then closes the doc without saving changes and sets the doc and Word app objects to nothing using doc.Close SaveChanges:=wdDoNotSaveChanges appWD.Quit Set doc = Nothing Set appWD = Nothing I used the macro this morning repeatedly - perhaps 30 times in succession. (Each time had to be an individual call - there was no way to loop this.) After doing all that, I did not check to see if there were any open instances of Word left (there were none during testing, so I didn't think I should have to) or other issues. Then I tried to open a Word document with an AutoOpen macro, and Word crashed. Subsequent attempts produced further crashes. I wound up eventually having to rebuild Normal to get things moving again. Question: Could just repeatedly accessing the Word app have caused this? Is there a better way to avoid these issues in the future? Ed |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to remove repeated email ids by macro? | Excel Discussion (Misc queries) | |||
Merging adjacent repeated columns with a macro | Excel Discussion (Misc queries) | |||
Printing text in a repeated cell/row that is longer than repeated | Excel Discussion (Misc queries) | |||
How do I change one repeated word in the spreadsheet to another? | Excel Worksheet Functions | |||
how do I count the number of times a word is repeated in a range? | Excel Discussion (Misc queries) |