I threw together a macro in Word 2007 yesterday, but it kept
interrupting. There was no error, but I'd get the annoying message
box that says "Code execution has been interrupted." and the CONTINUE,
END, and DEBUG buttons. I've learned this is usually an indication
that I'm stretching the capacity of my memory - all I have to do is
hit CONTINUE and it runs along just fine - until it interuupts again!!

I had tables in a Word doc with reprot numbers in one column, and
several more blank columns. The data to fill in those blanks was
mostly in an Excel spreadsheet. One blank, though, could only be
filled in by finding the report document and grabbing one line of
text.

So the macro was designed to set an object to the table I had selected
and iterate down the report numbers, open the spreadsheet and find the
needed values for that report, then open the report, grab the one line
of text, and close the report. Lather, rinse, repeat until the end of
the table.

Can someone see where I could have saved memory overhead and made this
run a bit smoother?

Ed


Sub EnterMyInfo()

Dim doc As Document
Dim tbl As Table
Dim str As String
Dim cll As Word.Cell

Dim tir As Document
Dim this As Range

Dim oXL As Excel.Application
Dim wkb As Excel.Workbook
Dim wks As Excel.Worksheet
Dim xlcll As Excel.Range

Dim x As Long, y As Long
Dim a As Long, b As Long
Dim vlu

Set doc = ActiveDocument
Set tbl = Selection.Tables(1)

Set oXL = New Excel.Application
Set wkb = oXL.Workbooks.Open("C:\MyFile.xls")
oXL.Visible = True

Set wks = wkb.Worksheets("Sheet1")
a = wks.Range("A20000").End(xlUp).Row

x = tbl.Rows.Count

For y = 1 To x
Application.StatusBar = "Row " & y & " of " & x
Set cll = tbl.Cell(y, 1)
If Left(cll.Range.Text, 5) = "L5-BB" Then
str = Left(cll.Range.Text, 10)

For b = 2 To a
If wks.Range("B" & b).Value = str Then Exit For
Next b

'Stop

'**********
'vlu was deliberately left undefined
'so I could adjust it depending on the table.
'**********

vlu = Format(wks.Cells(b, 3).Value, "mm/dd/yyyy")
tbl.Cell(y, 2).Range.Text = vlu
vlu = Format(wks.Cells(b, 5).Value, "####0.0")
tbl.Cell(y, 3).Range.Text = vlu
'tbl.Cell(y, 4).Range.Text = wks.Cells(b, 7).Value

Set tir = Word.Application.Documents.Open(FileName:="\\Serve r1\ &
str & ".doc")
tir.PageSetup.LeftMargin = InchesToPoints(0.75)
tir.PageSetup.RightMargin = InchesToPoints(0.75)

Set this = tir.Content
With this.Find
.Text = "|90. "
.Execute

this.Collapse wdCollapseEnd
this.MoveEndUntil "|", wdForward

tbl.Cell(y, 4).Range.Text = Trim(this.Text)
End With

'Stop

tir.Close wdDoNotSaveChanges
Set tir = Nothing

End If
Next y

EndMeNow:
On Error Resume Next
wkb.Close
oXL.Quit
Set oXL = Nothing
On Error GoTo 0

MsgBox "I'm done!"

End Sub

On Jul 23, 10:07*am, Ed from AZ wrote:
I threw together a macro in Word 2007 yesterday, but it kept
interrupting. *There was no error, but I'd get the annoying message
box that says "Code execution has been interrupted." and the CONTINUE,
END, and DEBUG buttons. *I've learned this is usually an indication
that I'm stretching the capacity of my memory - all I have to do is
hit CONTINUE and it runs along just fine - until it interuupts again!!

I had tables in a Word doc with reprot numbers in one column, and
several more blank columns. *The data to fill in those blanks was
mostly in an Excel spreadsheet. *One blank, though, could only be
filled in by finding the report document and grabbing one line of
text.

So the macro was designed to set an object to the table I had selected
and iterate down the report numbers, open the spreadsheet and find the
needed values for that report, then open the report, grab the one line
of text, and close the report. *Lather, rinse, repeat until the end of
the table.

Can someone see where I could have saved memory overhead and made this
run a bit smoother?

Ed

Sub EnterMyInfo()

Dim doc As Document
Dim tbl As Table
Dim str As String
Dim cll As Word.Cell

Dim tir As Document
Dim this As Range

Dim oXL As Excel.Application
Dim wkb As Excel.Workbook
Dim wks As Excel.Worksheet
Dim xlcll As Excel.Range

Dim x As Long, y As Long
Dim a As Long, b As Long
Dim vlu

Set doc = ActiveDocument
Set tbl = Selection.Tables(1)

Set oXL = New Excel.Application
Set wkb = oXL.Workbooks.Open("C:\MyFile.xls")
oXL.Visible = True

Set wks = wkb.Worksheets("Sheet1")
a = wks.Range("A20000").End(xlUp).Row

x = tbl.Rows.Count

For y = 1 To x
* Application.StatusBar = "Row " & y & " of " & x
* Set cll = tbl.Cell(y, 1)
* If Left(cll.Range.Text, 5) = "L5-BB" Then
* * str = Left(cll.Range.Text, 10)

* * For b = 2 To a
* * * If wks.Range("B" & b).Value = str Then Exit For
* * Next b

* * 'Stop

* * '**********
* * 'vlu was deliberately left undefined
* * 'so I could adjust it depending on the table.
* * '**********

* * vlu = Format(wks.Cells(b, 3).Value, "mm/dd/yyyy")
* * tbl.Cell(y, 2).Range.Text = vlu
* * vlu = Format(wks.Cells(b, 5).Value, "####0.0")
* * tbl.Cell(y, 3).Range.Text = vlu
* * 'tbl.Cell(y, 4).Range.Text = wks.Cells(b, 7).Value

* * Set tir = Word.Application.Documents.Open(FileName:="\\Serve r1\ &
str & ".doc")
* * tir.PageSetup.LeftMargin = InchesToPoints(0.75)
* * tir.PageSetup.RightMargin = InchesToPoints(0.75)

* * Set this = tir.Content
* * With this.Find
* * * .Text = "|90. "
* * * .Execute

* * * this.Collapse wdCollapseEnd
* * * this.MoveEndUntil "|", wdForward

* * * tbl.Cell(y, 4).Range.Text = Trim(this.Text)
* * End With

* * 'Stop

* * tir.Close wdDoNotSaveChanges
* * Set tir = Nothing

* End If
Next y

EndMeNow:
On Error Resume Next
* wkb.Close
* oXL.Quit
* Set oXL = Nothing
On Error GoTo 0

MsgBox "I'm done!"

End Sub

Reboot your computer and try again... This happens to be more often
then I would like . Something is causing your code to automatically
break... It's nothing in the code it's self but rather something
stupid your computer did. You will find that after you reboot it will
work fine.

~ Cheers ~

"Str" is a VBA function. Don't use it as a variable.
Set all of your Excel object variables to nothing.



"Ed from AZ"
wrote in message
I threw together a macro in Word 2007 yesterday, but it kept
interrupting. There was no error, but I'd get the annoying message
box that says "Code execution has been interrupted." and the CONTINUE,
END, and DEBUG buttons. I've learned this is usually an indication
that I'm stretching the capacity of my memory - all I have to do is
hit CONTINUE and it runs along just fine - until it interuupts again!!

I had tables in a Word doc with reprot numbers in one column, and
several more blank columns. The data to fill in those blanks was
mostly in an Excel spreadsheet. One blank, though, could only be
filled in by finding the report document and grabbing one line of
text.

So the macro was designed to set an object to the table I had selected
and iterate down the report numbers, open the spreadsheet and find the
needed values for that report, then open the report, grab the one line
of text, and close the report. Lather, rinse, repeat until the end of
the table.

Can someone see where I could have saved memory overhead and made this
run a bit smoother?

Ed