Word Macros Fails Every Other Time it Runs

[RayLux]

I ahve a large WORD VBA macro that copies information from a word document into an EXCEL spreadsheet. If I run the macro on a file it runs fine. If I re-run the macro on the same or a different file it fails at the step
xlWB.Application.Selection.TextToColumns Destination:=Range("A2"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(13, 1), Array(22, 1), Array(32, 1), Array(42, 1), _
Array(52, 1), Array(62, 1), Array(72, 1), Array(82, 1), Array(92, 1), Array(102, 1), Array( _
112, 1)), TrailingMinusNumbers:=True

Run it again and it works. If I totally quit WORD, it works on the first time I run it after re-opening, but fails on the second. Is there something being reset when I quit WORD? If so can I reset it in the macro?

[fumei]

We need to know:

1. how you are creating and using your instance of Word.

2. how you are quitting Word, and how you are dealing with that instance.

Please post some code to indicate what you are doing. In fact, it seems that you are running your code from Word, and therefore are creating an instance, not of Word, but of Excel. Again, if so, please indicate how you are creating and dealing with this instance.

[RayLux]

I amd running it from WORD with an EXCEL Instance. Here is the section of code where I open the EXCEL and copy the DATA over. The alternating behavior is very puzzling to me.


' Move data to EXCEL spreadsheet
Excel:
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook, xlWBSum As Excel.Workbook
'
' Windows(1).Activate
Word.Windows(strOneDoc).Activate
Selection.WholeStory
Selection.Copy
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
xlApp.ScreenUpdating = False
Set xlWB = xlApp.Workbooks.Open(strWBName)
xlWB.Activate
xlWB.Sheets("activities").Activate
xlWB.ActiveSheet.Range("A2:N250").Select
xlWB.Application.Selection.Clear
xlWB.ActiveSheet.Range("A2").Select
xlWB.ActiveSheet.Paste
' Application.DisplayAlerts = False
xlWB.Application.Selection.TextToColumns Destination:=Range("A2"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(13, 1), Array(22, 1), Array(32, 1), Array(42, 1), _
Array(52, 1), Array(62, 1), Array(72, 1), Array(82, 1), Array(92, 1), Array(102, 1), Array( _
112, 1)), TrailingMinusNumbers:=True
Application.DisplayAlerts = True
' Do Power Entries
Windows(strTwoDoc).Activate
Selection.WholeStory
Selection.Copy
' Start Spreadsheet work
xlWB.Activate
xlWB.Sheets("dose_H_30min").Activate
xlWB.ActiveSheet.Range("B263:N265").Select
Excel.Application.Selection.Clear
Range("B263").Select
xlWB.ActiveSheet.Paste
' Application.DisplayAlerts = False
Excel.Application.Selection.TextToColumns Destination:=Range("B263"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
' Application.DisplayAlerts = False
' Copy Rem/g Values
xlWB.Sheets("dose_H_30min").Activate
xlWB.ActiveSheet.Range("C255:L255").Select
Excel.Application.Selection.Copy
' Open Summary Worksheet
Set xlWBSum = xlApp.Workbooks.Open(strWBSumName)
xlWBSum.Activate
xlWBSum.Sheets(strShName).Activate
xlWBSum.ActiveSheet.Range("D" & (4 + (GroupNum - 1) * GrSize + RowNum)).Select
Excel.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'
xlWB.Sheets("dose_H_30min").Activate
xlWB.ActiveSheet.Range("C268:L268").Select
Excel.Application.Selection.Copy
xlWBSum.Sheets(strShName).Activate
xlWBSum.ActiveSheet.Range("D" & (40 + (GroupNum - 1) * GrSize + RowNum)).Select
Excel.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
' Clean up and quit
' xlWB.Close Truen
xlWB.Close True
Set xlWB = Nothing
xlWBSum.Close True
Set xlWBSum = Nothing
Excel.Application.Quit
Set xlApp = Nothing
'
Windows(strOneDoc).Activate
Windows(strOneDoc).Close False
Windows(strTwoDoc).Activate
Windows(strTwoDoc).Close False
Windows(strMainName).Activate
' Word.Application.Quit

EDIT: Added VBA tags Tommy

[fumei]

1. please use the VBA code tags when posting code.

2. your code will be much easier to read (even for you), and will execute faster/better if you use With statements, rather than separate instructions. EXAMPLE:
Set xlWB = xlApp.Workbooks.Open(strWBName)
With xlWB
.Activate
.Sheets("activities").Activate
.ActiveSheet.Range("A2:N250").Select
.Application.Selection.Clear
.ActiveSheet.Range("A2").Select
.ActiveSheet.Paste
.Application.Selection.TextToColumns _
Destination:=Range("A2"), _
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(13, 1), _
Array(22, 1), Array(32, 1), Array(42, 1), _
Array(52, 1), Array(62, 1), Array(72, 1), _
Array(82, 1), Array(92, 1), Array(102, 1), _
Array(112, 1)), TrailingMinusNumbers:=True
End With


3. Avoid using selecting and activating, in BOTH Word and Excel. I am not sure what you are doing with:
' Windows(1).Activate
Word.Windows(strOneDoc).Activate
Selection.WholeStory
Selection.Copy
however, I do know that you do not need to do this.

Whatever strOneDoc is...make it a Document object.
Dim WhateverDoc As Document
' if it IS the active document
Set WhateverDoc = ActiveDocument

' OR

Set WhateverDoc = Documents(strOneDoc)
Now you can easily get the whole document - as that is what you seem to be doing - by using that reference. In other words, you do NOT need to Activate to process just about any action you want on a document.

So instead of:
Windows(strOneDoc).Activate
Windows(strOneDoc).Close False
you can use:
WhateverDoc.Close False

I am still walking through your code trying to figure out what is actually going on.