How could I have saved memory?
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 |
How could I have saved memory?
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 ~ |
How could I have saved memory?
"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 |
All times are GMT +1. The time now is 10:43 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com