0

well my problem is that I have this code, so when I run it star to work and after a few minutes crash sometimes excel just close and sometimes restar the program.

this code is made for open a file inside of a list when the file is open, search inside of the file and find textbox with certain word and replace with another word, the problem is that just a few minutes after star to work excel restar or close and I don't know why. this is the code:

sub search 
Dim ws As Worksheet
Dim tBox As Object
Dim stBox As String
     Application.ScreenUpdating = False
    Sheet1.Activate
    For i = 1 To 4298
    ExcelPath = Cells(i, 1).Value
    Workbooks.Open Filename:=ExcelPath
    ActiveWindow.Visible = False
    Set ws = ActiveSheet
    For Each tBox In ws.Shapes
    On Error Resume Next
    stBox = " "
    newv = " "
    tBox.TextFrame.Characters.Text = Replace(stBox, newv, newv)
    On Error GoTo 0
    Next
    ActiveWorkbook.CheckCompatibility = False
    ActiveWorkbook.Close True
    A = (i / 4298) * 100
    Application.StatusBar = Mid(A, 1, 4)
    Next i

end sub
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Julian Alfonso Ochoa Cervera
  • 1
  • I'd start by not using 'Active' for any command. See [this very useful post](https://stackoverflow.com/a/10717999/2521004) for more information. – Automate This Jan 29 '19 at 02:34
  • I see you're opening a file each time in your For loop but there is no corresponding closing of the those workbooks. I'd be suspicious you have a lot of hidden excel windows in the background causing it to crash. – Mike Jan 29 '19 at 06:52
  • I have, I think think that you see the "for" that I use to search in textbox after that "For" there is other one and before that one is the code that close the file. – Julian Alfonso Ochoa Cervera Jan 30 '19 at 15:43

0 Answers0