VBA script runs once but if I try to run it again, it does not work

jakecahill · #1 07-10-2017, 06:45 AM

Hello,

I wrote a VBA script to check a table in a word document for terms that are stored in an Excel spreadsheet.

The first time I run it, everything works fine.

Every other time results in an Object is invalid error and Excel won't even let me open the spreadsheet manually. Instead, I have to restart my PC to be able to open it again.

This is the script:

Code:

Public Sub CheckTableCells()

Dim oCell As Cell
Dim oRow As Row
Dim MyRange As Range
Dim Rng As Variant
Dim findText As String
Dim x1 As Excel.Application
Dim wkbk As Excel.Workbook
Dim wksh As Excel.Worksheet

Set x1 = New Excel.Application

x1.Workbooks.Close
Set wkbk = x1.Workbooks.Open(FileName:="F:\Blatchford termbase_MASTER.xlsx", ReadOnly:=True)
Set wksh = wkbk.Sheets(1)

If x1.Ready = True Then
LastRow = wksh.Columns(1).SpecialCells(xlLastCell).Row
Set Rng = wksh.Range(Cells(1, 1), Cells(LastRow, 1))
 Debug.Print Rng(1).Text
    For x = 1 To Rng.Count
        
        If Not Rng(x) Is Nothing Then
          
            findText = Rng(x).Text
            For Each oRow In Selection.Tables(1).Rows
    For Each oCell In oRow.Cells
        Set MyRange = oCell.Range
        
        Do While True
        MyRange.Find.Highlight = True
        MyRange.Find.MatchWildcards = False
        MyRange.Find.MatchWholeWord = True
        MyRange.Find.Forward = True
        MyRange.Find.IgnorePunct = True
        MyRange.Find.Execute (findText)
        
            If MyRange.Find.Found Then
           Debug.Print oCell.Range.Text
            With MyRange.Font
            .ColorIndex = wdGreen
            .Bold = True
            End With
            
            
            Else
            Exit Do
            End If
        Loop
    Next oCell
Next oRow
            
        Else
            MsgBox "Nothing found"
            Exit For
        End If
        
    Next x
x1.Workbooks.Close
Else
End
End If

End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
VBA runs I then save as to keep master free from editds but does not work	dingar	Word VBA	3	04-27-2017 03:18 PM
Video Slide Runs Slow	tommyc325	PowerPoint	0	04-21-2017 06:09 AM
Adapt a script used in Word to highlight words, to work in Outlook?	flatop	Outlook	5	07-15-2014 01:07 PM
Limiting the times that it runs	hchbiker	PowerPoint	0	02-09-2011 08:36 AM
Rule runs once, but not later	stlsailor	Outlook	0	07-28-2009 08:18 PM